Get Unique Values from Data Range - Part 2 (Using Function)

In our last post Get Unique Values from Data Range - Part 1 (Using default option), we have learn 'Remove Duplicate' & 'Advanced Filter' to get Unique records from a data range. Today we will learn two different function to get Unique Records from a range. We have same example, we have used in Part 1.

1. Using Function (Normal Function)

Step 1: Add a helping column & put this formula and drag it down "=IF(COUNTIF($B$2:B2,B2)=1,A1+1,A1)". See below snap-shot:

Step 2: Put any of these formula in other column where you want to get the output and drag it down "=IFERROR(VLOOKUP(ROW(A1),$A:$B,2,0),"")" - will be used if you have used left column of data range for helping column or "=IFERROR(INDEX($B$2:$B$30,MATCH(ROW(B1),$A$2:$A$30,0)),"")" you can use if you put helping column right to your data range. Your output is ready, see below snapshot:

Let's look into the formula:

=IF(COUNTIF($B$2:B2,B2)=1,A1+1,A1)

This function will provide a serial no. of each unique names in the list.

=IFERROR(VLOOKUP(ROW(A1),$A:$B,2,0),"")

This function lookup the value in the data range based on the serial we have got from above function.

2. Using Function (Array Function)

 

 Step 1: Put this formula into cell D2 & Press Ctrl+Shift+Enter and drag it down "=IFERROR(INDEX($B$2:$B$30,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$30),0)),"")". Your outpit is ready, see below snap-shot:

Let's look into the formula:

=IFERROR(INDEX($B$2:$B$30,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$30),0)),"")

Above highlighted portion will count the value in Data Range B2:B30 based on the criteria D1:D1. As D1 is blank and do not found in Data range, hence returns array {0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}.

=IFERROR(INDEX($B$2:$B$30,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$30),0)),"")

Above highlighted portion will match 0 in the array which CountIf returns. As match function found 0 in 1st position, it returns 1.

=IFERROR(INDEX($B$2:$B$30,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$30),0)),"")

Above function gives the output as "Anu Gupta" as it is on 1st position.

As we drag the formula to down, criteria of count function will change. See here:

=IFERROR(INDEX($B$2:$B$30,MATCH(0,COUNTIF($D$1:D2,$B$2:$B$30),0)),"")

Now, the criteria is D1:D2 as it was D1:D1 in cell D2.

This function tells us the count of D1:D2 and returns 1 where it matches the criteria value else 0 see array {1;0;0;0;1;0;0;1;0;0;0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;0;0;0;0}.

Now the match function  matches 0 in this array and tells us the position 2.

Now Index function gives the output "Kuldeep Rawat" as it is on 2nd position in array.

And so on, function returns the output.

Bonus Function - To get Duplicate values

In the same example we will find the Duplicate value from the same Data range.

Step 1: Add a helping column & put this formula and drag it down "=IF(COUNTIF($B$2:B2,B2)=2,A1+1,A1)". See below snap-shot:

Step 2: Put any of these formula in other column where you want to get the output and drag it down "=IFERROR(VLOOKUP(ROW(A1),$A:$B,2,0),"")" - will be used if you have used left column of data range for helping column or "=IFERROR(INDEX($B$2:$B$30,MATCH(ROW(B1),$A$2:$A$30,0)),"")" you can use if you put helping column right to your data range. Your output is ready, see below snapshot:

-----------------------------------------------------------------------------------------------------------------------------------------------

Download the file : GetUnique-Part2.xlsx

Thanks for learning, will come soon with new post.

Please give your valuable feedback i comment box if it is helpful.