Dissecting and organizing data doesn't have to be challenging. You can quickly filter your data items with this method using a combination of formulas to determine which are unique items.
Syntax
=LOOKUP(2, 1/(COUNTIF(expanding unique list, original list)=0), original list)
Steps
- Begin by typing in =LOOKUP(
- Follow with 2, and 1/COUNTIF(
- Select or type in the range reference that is one cell above the list, and make sure that the argument entered is an absolute reference (i.e. $D$2)
- Type in a colon and the same reference as the relative reference (i.e. :D2)
- Select or type in the range reference that contains the original list (i.e. $B$3:$B$9)
- Type in )=0),
- Select or type in the range reference that contains the original list (i.e. $B$3:$B$9)
- Type ) and press Enter to complete the formula
- Copy down the formula all the way down =LOOKUP(2,1/(COUNTIF($D$2:D5,$B$3:$B$9)=0),$B$3:$B$9)
How
With this detailed formula we aim to find a unique value, which is not listed in any previous cells (i.e. $D$2:D5), by searching for a greater value (2) in an array of 1 and 0 values (1/(COUNTIF($D$2:D5,$B$3:$B$9)=0)).
Let's breakdown the formula,
=LOOKUP(2,1/(COUNTIF($D$2:D4,$B$3:$B$9)=0),$B$3:$B$9)
Step | Description | Return Value |
1 | COUNTIF returns an array that contains 1 for each existed value of $B$3:$B$9 in expanding $D$2:D4 and 0 for not existed | {0;1;0;0;1;1;1} |
2 | By using an equal sign, the 1/0 array is converted into a TRUE/FALSE array | {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE} |
3 | The number 1 is divided by the array, creates an array of 1s and #DIV/0 errors | {1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;#DIV/0!} |
4 |
The number 2 is used as a larger number and is searched in the 1/error array. The LOOKUP returns the value in original list by using last non-error matched values index. |
4th item in original list is pea |