Re: Want to extract unique values from columns
@Aladin Akyurek, You can't be wrong ever; I believe! Thanks a lot! this is exactly what I wanted.
Could you explain the formula for me?
Goal: Pick out the distinct items from the B range and display each one by one in the D range.
Formula: =IFERROR(INDEX($B$1:$B$9,SMALL(IF(FREQUENCY(IF(1-($B$1:$B$9=""),
IF(ISNA(MATCH($B$1:$B$9,$A$1:$A$9,0)),MATCH($B$1:$B$9,$B$1:$B$9,0))),
ROW($B$1:$B$9)-ROW($B$1)+1),ROW($B$1:$B$9)-ROW($B$1)+1),
ROWS($D$1:D1))),"")
Explanation:
IFERROR returns a blank (i.e., "") in case an error occurs in the INDEX bit.
INDEX returns a value from a position in the B range, corresponding to the integer value SMALL returns at each copy step, specified by ROWS. The ROWS bit deliver the number of rows in the range it is fed with.
The FREQUENCY bit...
The first IF bit
1-($B$1:$B$9="") eliminates empty/blank cells from consideration.
The second IF bit
ISNA(MATCH($B$1:$B$9,$A$1:$A$9,0))
eliminates B items which are not in the A range.
In the MATCH bit
MATCH($B$1:$B$9,$B$1:$B$9,0)
MATCH is matching a IF-filtered range against itself.
This evaluates to:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;9}
for the sample we have. FALSE values would correspond to the empty/blank cells and B items which do occur in the A range.
Non-FALSE results read:
B7 matched against B1:B9 yields 7. That is, B7 matches B1:B9 at position (row) 7.
B8 at position/row 8.
B9 at row 9.
FREQUENCY(NumericValues,Bins)
yields a count per bin, that is, how many of the numeric values map onto a bin.
Example:
=FREQUENCY({2;0;5},{2,5})
yields an array result of {2;1;0}, saying bin 2 has two occurrences: 0 and 2, bin 5 one occurrence which is the number 5.
In our formula, NumericValues are:
IF(1-($B$1:$B$9=""),IF(ISNA(MATCH($B$1:$B$9,$A$1:$A$9,0)),MATCH($B$1:$B$9,$B$1:$B$9,0)))
Bins: ROW($B$1:$B$9)-ROW($B$1)+1)
The latter expression creates:
{1;2;3;4;5;6;7;8;9}
an integer vector (a set of consecutive bins).
The FREQUENCY bit yields in our case:
{0;0;0;0;0;0;1;1;1;0}
saying that J occurs once, L occurs once, etc.
The IF that envelops the foregoing FREQUENCY bit maps these frequencies to a copy of the integer vector
IF(FREQUENCY(IF(...,IF(...,MATCH($B$1:$B$9,$B$1:$B$9,0))),ROW($B$1:$B$9)-ROW($B$1)+1),
ROW($B$1:$B$9)-ROW($B$1)+1)
giving us the positions of distinct items in the A range: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;9;FALSE}
SMALL that is already mentioned picks out the relevant numbers one by one and feeds INDEX with that, enabling INDEX to fetch the items at those positions from the B range.
Hope this helps.
