Counting Unique combinations

Can someone, anyone please help. I have a spreadsheet that has 2 columns. The first is a vendor code, and the second is a part #. There are many vendors with multiple part #'s per vendor and duplicates. In other words, (for example sake), a vendor has 20 part numbers associated with a particular vendor #,and there are many duplicates within the list.

I am trying to do a vlookup and count how many items are associated with the vendor. But I have to exclude duplicates and only count the part once? This is very frustrating. Is this even possible? ANY help would be appreciated. Below is an example.....

SHEET 1
Column A (supplier)......................Column B (part#)
xyz corp.......................................12345
xyz corp........................................78910
xyz corp.......................................12345
ABC Corp......................................99999
ABC Corp......................................55555
ABC Corp......................................99999

SHEET 2
Column A (supplier)................Column B(# of parts)
xyz corp.....................................2
ABC Corp...................................2


So based on this, I want to COUNT how many times the part # shows up for xyz, and ABC and then I need it input into another sheet. So the logic verbally would be......lookup xzy in the first sheet, count how many exclusive times the part # shows up for that supplier, and return that count in the new sheet.

I hope that make sense.

Can be done easily in a few steps, without complex formulas...

You can use Advanced Filter in Data menu, to get a unique list of vendors and parts..

Select columns A and B and go to Data|Filter|Advanced Filter.

check Unique Parts Only and select where you want result to go.

Click ok, you should have unique part ids and corresponding vendors.

You can create another unique list based only on the Vendor Column to get a unique list of Vendors.

Then you can use Countif to count how many times the unique vendor appears in the first list you created of unique vendor/parts.


=Countif(X:X,Z1) where Z1 contains the Vendor in the unique list of vendors and X:X is the list of vendors in the unique vendor/part list.

Hi,
to obtain a list of the SUPPLIER of column A in Sheet1, you could try this array formula in cell B2 of sheet2Array formula has to be confirmed with control+shift+enter.
You must drag down the formula until "error" pops up.

=INDEX(Sheet1!$A$2:$A$3000,SMALL(IF(ISNUMBER(MATCH(ROW(Sheet1!$A$2:$A$3000)-1,MATCH(Sheet1!$A$2:$A$3000,Sheet1!$A$2:$A$3000,0),0)),MATCH(Sheet1!$A$2:$A$3000,Sheet1!$A$2:$A$3000,0),""),ROW(Sheet1!A1)))

You can also nest the formula inside a IF formula, to avoid errors

=IF(ISERROR(formula);"";formula)

Using condional formatting to blank errors is another solution.
--------------------------------------------

To obtain the unique number of the PART (column B sheet1) per SUPPLIER (column A sheet1) you could try another array formula (thanks to Mike Gel Girvin)

=SUM(IF(FREQUENCY(IF(Sheet1!A2:A3000=A2,IF(Sheet1!A2:A3000<>"",MATCH("~"&Sheet1!B2:B3000,Sheet1!B2:B3000&"",0))),ROW(Sheet1!B2:B3000)-ROW(Sheet1!B2)+1),1,0))

Both the formula are very challenging for processor.
Hoping did not make any mistake in translation from Excel.

As CANAPONE states, the array formulas can be a bit taxing on the processor.

Maybe with the help of a helper column, you can use a pivot table...

Say your data is in column A:B, then in C1 enter a title, like CountParts and in C2 enter formula like:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

and copy down.

This formula too can be limiting if you drag down to far.. so try to limit.

NOTE: If part numbers are unique to the suppliers (i.e. Part numbers are not shared, then use this less taxing formula: =IF(COUNTIF($B$2:$B2,B2)>1,0,1) instead of the Sumproduct() formula.

Then use a Pivot table to summarize that column

Go to Data|Pivot Table, clikc Next.

Ensure you have correct range, click Next

Click Layout and drag the Supplier button to the Row Area, drag the CountParts button to the Data Area... It should say Sum of CountParts ... if not double-click that button in the Data Area and choose sum.

Then determine where you want Pivot table to go,

Click Finish.

Hi, (I'm working on your file),
you could copy in c2 of sheet2 the NBVC's suggested formula

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

and you drag the formula till row 3158

In sheet1 you could use in B2 a SUMIF
=SUMIF(Sheet2!$A$2:$C$3158,A2,Sheet2!$C$2:$C$3158)

In the file you could also see and test as the Array formula (highlighted in yellow: sheet1) do their job correctly, but not smoothly.


http://myfreefilehosting.com/f/05b92da21a_0.12MB


Hope it's a step forward

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.