Help!: Compare 2 lists and extract only unique values from list 1 without duplicates.

[Rgibson]

Hi all, I am stuck trying to create an array formula that will create a dynamic list of names.
I am essentially creating a 3rd dynamic list with an array formula, which draws from List 1 (Working List), and compares to List 2 (Database). I get the items that do not show up in database to populate into List 3, but it extracts ALL of the items, even the duplicates. Can anyone help? Essentially what I am looking to do is like this:

LIST 1 (Working List)	LIST 2 (Database)	New List (Unique Items, not found in List 2, consolidated without duplicates)
Item A	Item A	Item AA
Item B	Item B	Item AB
Item AA	Item C
Item C	Item D
Item AA	Item E
Item AB

Right now, I use a formula to determine the amount of unique items in list 1. It is quite convoluted, so I'm not sure if there is a more simplified way to do this:
=SUM(SUM(COUNTA(Takeoff[Description]),COUNTBLANK(Takeoff[Description]))-SUMPRODUCT(1-ISNUMBER(MATCH(Takeoff[Description],$B$7:$B$7487,0))))

And, for list 3, my array formula is as follows:
{=IF(ROWS(B$7490:B7490)<=C$7488,INDEX(Takeoff[Description],SMALL(IF(ISNA(MATCH(Takeoff[Description],$B$7:$B$7487,0)),ROW(Takeoff[Description])-ROW(TAKEOFF!$E$7)+1),ROWS(B$7490:B7490))),"")} - (((using CSE)))

Any help would be greatly appreciated. I do not want to be using VBA to do this, btw. Strictly trying to do this with an array formula.

 

[DanteAmor]

Hi and welcome to MrExcel!

Check if this helps you:

Dante Amor
	A	B	C
1	LIST 1 (Working List)	LIST 2 (Database)	New List
2	Item A	Item A	Item AA
3	Item B	Item B	Item AB
4	Item AA	Item C
5	Item C	Item D
6	Item AB	Item E
7	Item AA
Hoja3

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIF($C$1:C1, IFERROR(INDEX($A$1:$A$7,SMALL(IF(NOT(ISNUMBER(MATCH($A$2:$A$7,$B$2:$B$6,0))), ROW($A$2:$A$7)), ROW()-1)),""))=0, IFERROR(INDEX($A$1:$A$7,SMALL(IF(NOT(ISNUMBER(MATCH($A$2:$A$7,$B$2:$B$6,0))), ROW($A$2:$A$7)), ROW()-1)),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rgibson]

Hi and welcome to MrExcel!

Check if this helps you:

Dante Amor
	A	B	C
1	LIST 1 (Working List)	LIST 2 (Database)	New List
2	Item A	Item A	Item AA
3	Item B	Item B	Item AB
4	Item AA	Item C
5	Item C	Item D
6	Item AB	Item E
7	Item AA
Hoja3

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIF($C$1:C1, IFERROR(INDEX($A$1:$A$7,SMALL(IF(NOT(ISNUMBER(MATCH($A$2:$A$7,$B$2:$B$6,0))), ROW($A$2:$A$7)), ROW()-1)),""))=0, IFERROR(INDEX($A$1:$A$7,SMALL(IF(NOT(ISNUMBER(MATCH($A$2:$A$7,$B$2:$B$6,0))), ROW($A$2:$A$7)), ROW()-1)),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi, thank you for the response. It did not work for me. When I adopted it to my spreadsheet, I get an alert telling me it is a circular reference, but it can't tell me where. So, I thought I would create a spreadsheet like the one above, in Columns A,B, and my answer in C exactly as above, and it returns "0" in each cell. I entered the formula, made some adjustments to include all of the cells with data in them, and used CSE. Still "0".

ITEM A	ITEM A	0​
ITEM B	ITEM B	0​
ITEM AA	ITEM C	0​
ITEM C	ITEM D	0​
ATEM AB	ITEM E	0​
ITEM AA		0​

 

[Rgibson]

I figured out the solution for my problem! In case anyone wonders how I fixed it, this ended up being my fix:

=IF(ROWS(B$7490:B7490)>$C$7488,"",INDEX(Takeoff[Description],SMALL(IF(FREQUENCY(IF((Takeoff[Group]="")*(Takeoff[Description]<>""),MATCH(Takeoff[Description],Takeoff[Description],0)),ROW(Takeoff[Description])-ROW(TAKEOFF!$E$7)+1),ROW(Takeoff[Description])-ROW(TAKEOFF!$E$7)+1),ROWS(B$7490:B7490))))

The highlighted and underlined being the breakthrough I had.