Search code examples
excelexcel-formuladuplicates

Counting duplicate values in one column based on the value from another in Excel?

I have a very large dataset which changes information and size on a daily basis (data is pulled from a very old, not very user friendly WMS).

I need to add a column to count how many unique item numbers are on each order number in a similar format as the snippet below if possible.

Problem is that I am unable to remove duplicates or add filters because each line has unique data that is used elsewhere. Any help with this would be greatly appreciated!

Table 1

I have tried a few different formulas but my knowledge is not the best and searching the web has not helped in this instance...

Order no. Item no. Item Count
568320 20436 1
568320 20436
568733 62509 1
568733 62509
568669 62418 2
568669 61016
568738 62249 2
568738 62546
568724 61024 3
568724 59955
568724 20436
568720 62418 3
568720 56599
568720 61033
568999 62726 2
568999 56609
568891 62486 2
568891 62454
Solution

  • Here is one alternative method to accomplish the desired output, and since you are using Structured References hence formulas using the references as well:

    enter image description here

    =IF(SUM(--(INDEX([Order no.],1):[@[Order no.]]=[@[Order no.]]))=1,
SUM(N(UNIQUE(FILTER([Item no.],[Order no.]=[@[Order no.]]))<>"")),"")

    Or, Using Dynamic Array Formula to spill if preferred then:

    =LET(
     a, OrderTbl,
     b, TAKE(a,,1),
     MAP(b, LAMBDA(r, IF(SUM(N(r:OrderTbl[@[Order no.]]=r))=1,
     SUM(1-ISERR(UNIQUE(FILTER(TAKE(a,,-1),b=r)))),""))))