0

I have the following Excel spreadsheet:

         A                    B 
1   Original List          Unique List
2   Product A               Product A
3   Product A               Product B
4   Product B               Product C
5   =""
6   Product A
7   Product C
8   Product B
9   =""
10  Product C

In Column A I have list which contains several products multiple times. My goal is now to create a list of all unique items in Column B.

To achive this I used the formula from this post in Cells B2:B10:

B2:B10 =IFERROR(INDEX($A$2:$A$10,MATCH(SUM(COUNTIF(B$1:B1,$A$2:$A$10)),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),0)),"")

and I get the following result:

         A                    B 
1   Original List        Unique List
2   Product A            
3   Product A            Product A
4   Product B            Product B
5   =""                  Product C
6   Product A
7   Product C
8   Product B
9   =""
10  Product C

This result comes pretty close to the list I want. The only issue is that the formula cannot handle the formula ="" which is in some cells in Column A. Instead of starting the list in Cell B2 it starts the list in Cell B3.

How do I have to mody the formula so it also works in case there are cells with ="" in the original list?

Michi
  • 4,663
  • 6
  • 33
  • 83

3 Answers3

0

Could you use excel's built in 'Remove Duplicates' function?:

Range("$A$2:$A$10").RemoveDuplicates Columns:=1, Header:=xlNo
Joe
  • 616
  • 2
  • 12
  • 27
0

We can use a "helper" column. In C2 enter:

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",1+MAX($C$1:C1)))

and copy down. Column C assigns a simple sequential value to each item in column A from which data will be extracted. Then in B2 enter:

=IFERROR(INDEX($A$2:$A$10,MATCH(ROWS($1:1),$C$2:$C$10,0)),"")

and copy down:

enter image description here

Note:

This method uses normal formulas rather than array formulas.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

You could use the Advanced Filter.

Use a Formula criteria such as =LEN(A5)>0 where A5 is the first cell with data. And then check the Unique Records Only in the Advanced Filter dialog. You can either filter in place, or to a new location

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60