sanantonio
Board Regular
- Joined
- Oct 26, 2021
- Messages
- 124
- Office Version
- 365
- Platform
- 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:
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?
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?