The Identification of Uniques

sanantonio

Board Regular
Joined
Oct 26, 2021
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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:

1639578263056.png


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
Joined
Jan 13, 2021
Messages
958
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0
Solution

sanantonio

Board Regular
Joined
Oct 26, 2021
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top