# The Identification of Uniques

#### sanantonio

##### Board Regular
Hi All,

The goal here is to provide a count per Vendor of 1: How many POs (Purchase Orders) we have with them and 2: How many Articles (Items) we stock from them.

The data looks like this:

As you can see PO numbers appear multiple times as it's repeated each time in instances where there is more than 1 article on the PO, which is 9/10 times. My initial attempt was to pivot, with the Vendor in
"Rows" and "Count of PO" in values but this of course counts each time a PO occurs, including the duplicates.

I'm wanting to avoid a macro in this instance as this data exists in a fully automated-macro free environment and I'm hoping to keep it that way.

So what I'm looking for in some advise on how to achieve the goal with a formula? Perhaps one that will return a 1 in a new column each time a PO number appears for the first time and a 0 when it's a repeat? Does such a thing exist? Or is there a different work around that I haven't thought of?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### JamesCanale

##### Well-known Member
MrExcelPlayground5.xlsx
CDEFGHIJ
1POPositionVendorArticleVendorPO countItems
211A1A27
312A2B14
413A3C12
514A4
621B5
722B6
823B7
924B8
1031C9
1132C10
1241A11
1342A12
1443A13
Sheet19
Cell Formulas
RangeFormula
H2:H4H2=UNIQUE(E2:E14)
I2:I4I2=ROWS(UNIQUE(FILTER(\$C\$2:\$C\$14,\$E\$2:\$E\$14=H2,"")))
J2:J4J2=ROWS(UNIQUE(FILTER(\$F\$2:\$F\$14,\$E\$2:\$E\$14=H2)))
Dynamic array formulas.

#### sanantonio

##### Board Regular
Absolutely perfect thank you so much!!

So this solution work inside a table? When I add it to a table I get #spill errors? Ideally I need it in a table as the number of Vendors may change? Or does the formula adjust for that?

Replies
5
Views
135
Replies
5
Views
475
Replies
7
Views
295
Replies
1
Views
437
Replies
3
Views
254

1,195,855
Messages
6,011,974
Members
441,661
Latest member
Pammie007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back