I have two lists of inventory codes in 2 different sheets:
Sheet 1:
A
1 XX123
2 YY123
3 ZZ123
4 ZY123
Sheet 4:
A
1 XX123
2 YY123
3 ZZ123
4 ZY123
5 XY123
Sheet 4 contains latest inventory list.
What I basically need is a formula that will compare inventory codes on sheet 4 to codes on sheet 1.
Then display in 1 cell the number of codes that is unique to sheet 4, i.e. number of new inventory items in sheet 4 that dont appear in sheet 1.
###
It may also be noteworthy to mention that the reason I need this is more from a fail safe perspective. I tried initially to find a formula that will compare each data range and build a 3rd list of unique product codes. Unfortunately, the suggestions on the net are array formula which when tested prove to be finicky. Plus my sheets are full of other array formula for analysis purposes so I'm not really enthusiastic on another array formula for 33,000 items, already takes about a min to save!
So the best solution I found was to sheet 4 and the live system file into access and export a 3rd back into excel with unique items. Hence I need a fail safe for sheet 1.
To cut a long story short, if anyone can provide me with a way to build a new list in excel, of unique items that appear on sheet 4 but not on sheet 1 it will serve as an alternative to the formula need before the hashes.
Also, I'm not opposed to macros but I've never worked with them before so if there is a macro that will build the list somewhere without messing up my analysis in sheet 1 and taking into account fresh data in both sheets that would be sweet. But would have to explain every step.
Sorry about the novel but I wanted to be as specific as possible. Any time you guys spend on the above will be greatly appreciated!
Count unique entries from columns in 2 sheets.



Re: Count unique entries from columns in 2 sheets.
You may be able to achieve what you want with a few Dynamic Named Ranges (DNRs) and an AdvancedFilter with a formula Criteria:
Add a new Worksheet and call it 'Unique'
Define the following 'Names':
Old, Refers To: =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$50000,COUNTA(Sheet1!$A$2:$A$50000))
New, Refers To: =Sheet4!$A$1:INDEX(Sheet4!$A$1:$A$50000,COUNTA(Sheet4!$A$1:$A$50000))
Criteria, Refers To: =Unique!$A$1:$A$2
UniqueList, Refers To: =Unique!$C$1
Then Select the Unique Worksheet:
A1: Unique
A2: =COUNTIF(Old,Sheet4!$A2)=0
Then Data > Filter > Advanced Filter and fill out the dialog as follows:
Copy to another location: Selected
List Range: Old
Criteria Range: Criteria
Copy to: UniqueList
Unique Records only: Unchecked
Then OK
Should give you a unique list on your new Worksheet. 
Re: Count unique entries from columns in 2 sheets.
Hi Rob
Thanks for your reply! For some reason data filter is only populating the first item in sheet 1. Do the defined ranges for sheet 1 and 4 need to be the same size in length?
Also this is great advice, mind giving a brief description of how this works so I can apply the same concepts in future?
Thanks a million... 
Re: Count unique entries from columns in 2 sheets.
I have uploaded the file I'm using to test the formula, if anyone can figure this out or what I've done wrong please let me know. Its just a shortened version to make it uploadable.

Re: Count unique entries from columns in 2 sheets.
Hi,
Do the changes.
change the dynamic range formulas
Inventory_new =Inventory!$A$1:INDEX(Inventory!$A$1:$A$20017,COUNTA(Inventory!$A$1:$A$20017))
Price_List_Range =Matrix!$A$1:INDEX(Matrix!$A$1:$A$19118,COUNTA(Matrix!$A$1:$A$19118))
Put a Column heading in A1 on both the sheets
Clear A1 on sheet Unique
change formula in A2 on Unique
=COUNTIF(Price_List_Range,Inventory!$A2)=0
Now do the steps for Advanced Filter Rob mentioned above.
HTH


Re: Count unique entries from columns in 2 sheets.
Hi Kris
Tried as you advised. Is it something on my side? Still only populating 1st cell, see attachment. 
Re: Count unique entries from columns in 2 sheets.
Hi,
replace
Copy to: UniqueList
with
Copy to: $D$1
and try

Re: Count unique entries from columns in 2 sheets.
Unfortunately still doing exactly the same thing. Have you managed to test this and get it working on your side?

Re: Count unique entries from columns in 2 sheets.
Can anyone tell how to get this to work?

Re: Count unique entries from columns in 2 sheets.
Leaf,
I think you are not entering the List Range correctly, the use of Old/New/Inventory_new/Price_List_Range have become somewhat muddled as you started off refering to them only as Sheet1 and Sheet 4 so I just took a 'best guess' at what they should be called for the DNRs.
If you take your most recent file and without doing anything else just use: Data > Filter > Advanced filter.
Copy to another location: Selected
List Range: Inventory_new
Change nothing else
Hit OK.
You should get your list. 

Re: Count unique entries from columns in 2 sheets.
Hi Rob
That's done the trick! Thanks
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!