Unique Accounts for Warehouses

Hi there,

I have a data set where I have lines of data that show a vendor that was purchased from and the weight associated with that purchase, then what warehouse those goods were stored in. What I'm trying to figure out is how to write a command in excel to ask how many accounts (aka vendors) exist in each warehouse. My perfect world is that I could eventually put this into the Pivot Table I have which shows each account list under a given warehouse and the weight associated with each account. I originally thought I could use a countif, but then I realized I probably needed to do some sort of nested function. I'm pretty new to the nested function world and struggle to figure out what to nest in order to get what I want. Can anyone assist?

Unfortunately, I tried to upload an attachment and was unsuccessful. Here is an example snip it.

Vendor Name wt Storage Name
MERIDIAN PRODUCTS, INC. 250 VERNON ALCOA COLD STORAGE
MERIDIAN PRODUCTS, INC. 250 VERNON ALCOA COLD STORAGE
MERIDIAN PRODUCTS, INC. 80 VERNON ALCOA COLD STORAGE
MERIDIAN PRODUCTS, INC. 320 VERNON ALCOA COLD STORAGE
MERIDIAN PRODUCTS, INC. 20 VERNON ALCOA COLD STORAGE
MERIDIAN PRODUCTS, INC. 320 VERNON ALCOA COLD STORAGE
NW GOURMET FOOD PRODUCTS 1080 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 600 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 720 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 1200 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 840 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 960 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 480 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 720 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 600 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 840 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 720 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 900 WIN TRUCKING ITS COLD STR
NW GOURMET FOOD PRODUCTS 1200 WIN TRUCKING ITS COLD STR
SOJITZ CORP OF AMERICA 660 WORLD FRESH
SOJITZ CORP OF AMERICA 880 WORLD FRESH
POSEIDON SEAFOOD CORP 440 WORLD FRESH
POSEIDON SEAFOOD CORP 660 WORLD FRESH
POSEIDON SEAFOOD CORP 660 WORLD FRESH


Thanks,
AnnaRose

Hi Alan,

Unfortunately, I already have this pivot table, but what I would like is a count how many unique accounts there are for each of those storage warehouses. My other searches online are getting me to this function:

=SUM(IF(FREQUENCY(IF(Name=A2,MATCH(Customer,Customer,0)),ROW(Customer)-MIN(ROW(Customer))+1),1))

Where instead for me Name would be the Warehouse and Customer would instead be the account name, but unfortunately I have not been able to get this to work for me.

AnnaRose

Hi all,

FlameRetired, yes! I finally figured out the Array formula to work. Finally!

Alan, thanks for the VBA code! I will try it out. I'm just starting to dip my toes in that realm of Excel. Thank you!