Count unique entries from columns in 2 sheets.

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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!

    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: Un-checked
    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.


    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.


    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.


    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login