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



## Rgibson (Dec 29, 2022)

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 AItem AItem AAItem BItem BItem ABItem AAItem CItem CItem DItem AAItem EItem 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 (Dec 29, 2022)

Hi and welcome to MrExcel!

Check if this helps you:

Dante AmorABC1LIST 1 (Working List)LIST 2 (Database)New List2Item AItem AItem AA3Item BItem BItem AB4Item AAItem C 5Item CItem D 6Item ABItem E 7Item AA Hoja3Cell FormulasRangeFormulaC2:C7C2=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 (Dec 29, 2022)

DanteAmor said:


> Hi and welcome to MrExcel!
> 
> Check if this helps you:
> 
> ...


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 AITEM A0​ITEM BITEM B0​ITEM AAITEM C0​ITEM CITEM D0​ATEM ABITEM E0​ITEM AA0​


----------



## Rgibson (Dec 30, 2022)

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.


----------

