I have a matrix of data in Excel and I want to write in a row the unique values in that data using only formulas. Most of the formulas I have found so far write the unique values into a column and takes the data from a single column. I attach an image of an example of what I need.
In the example the data is in B2:D4 and the unique results will be printed in F2:I2, thank you in advance for the help
Apply this array formula in F2 with CSE,
=IFERROR(INDEX($B$2:INDEX($B:$B, MATCH(1E+99, $B:$B)), MATCH(0, COUNTIF($E2:E2, $B$2:INDEX($B:$B, MATCH(1E+99, $B:$B))), 0)),
IFERROR(INDEX($C$2:INDEX($C:$C, MATCH(1E+99, $C:$C)), MATCH(0, COUNTIF($E2:E2, $C$2:INDEX($C:$C, MATCH(1E+99, $C:$C))), 0)),
IFERROR(INDEX($D$2:INDEX($D:$D, MATCH(1E+99, $D:$D)), MATCH(0, COUNTIF($E2:E2, $D$2:INDEX($D:$D, MATCH(1E+99, $D:$D))), 0)), TEXT(,))))
... and fill right.
The closest I can get is to give you a unique list in order of largest to smallest:
=AGGREGATE(14,6,$B$2:$D$4/(COUNTIF($E$2:E2,$B$2:$D$4)=0),1)
With data in B2 through D4, in F2 enter:
=MIN($B$2:$D$4)
and in G2 enter the Array Formula:
=MIN(IF($B$2:$D$4>F2,$B$2:$D$4))
and copy across:
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
Do you have to do it using only formulas? The Remove Duplicates function under the Data tab sounds perfect for this sort of problem. I am not sure if Remove Duplicates can produce horizontal data, but the transpose function can easily solve that.
