Combine 2 unique row entries from a table into 1 consolidated row entry

OliverScheurich sorry that im just now getting back to you.  Thank you for your help on this.

So this looks almost exactly like what im trying to do, except I do have a couple questions or possible issues I wanted to ask about.

1. Is there any way to get the two values (or multiple values when it comes to some accounts for my actual data) to just sum together instead of stack side by side?  Also, keep in mind, not EVERY account has a warehouse or has multiple locations.  Some just have one.

2. Also, within your scenario, the accounts and linked warehouses have the same address and account ID.  This is not the case in my problem.  Im fine with the address and ID consolidating to the main accounts (I don't need both to stay intact).  How do we deal with that?

3.  What I’ve also just realized, is there might be accounts that only order thru the warehouse one month, and I’d still want that to reflect as the store, even if there’s nothing to consolidate.  So maybe this whole thing is less about consolidating the two cells and just setting up formatting that I can customize to change “Account A Warehouse” into “Account A Store”

4.  Finally, I see you’re using XLOOKUP, does this mean in your example that I need to manually type in all the account names into Column G to get this all to populate?

To give you some more context, here’s some more info as well into what im trying to do.  I apologize, I’m responding from my phone and not near a computer, so I can’t really share an excel file example, but ill do my best to express it on here.

Here’s an example of what the data sets might look like below in a minimalistic form. Let’s say this is all the data in the table currently. I’ll continue to add to the table every subsequent month. For this example let’s say I started the table in May 2024, and then added more data for June 2024.

Here’s how the main data input table would look with data from my distributor:

Account Name | Acct ID | Units Sold | Month

Big Boy Liquor | A569 | 75 | May 2024

Wine Stop Market | C123 | 52 | May 2024

Big Boy Warehouse | B718 | 48 | May 2024

Jr Food & Deli | A331 | 82 | May 2024

John’s Liquor Mart | C423 | 38 | May 2024

Market Street Deli | B610 | 65 | June 2024

Big Boy Warehouse | B718 | 49 | June 2024

Wine Stop Market | C123 | 71 | June 2024

Wine Stop Warehouse | D121 | 41 | June 2024

Here’s how I’d want the final result to look:

Big Boy Liquor | A569 | 123 | May 2024

Wine Stop Market | C123 | 52 | May 2024

Jr Food & Deli | A331 | 82 | May 2024

John’s Liquor Mart | C423 | 38 | May 2024

Market Street Deli | B610 | 65 | June 2024

Big Boy Liquor | A569 | 71 | June 2024

Wine Stop Market | C123 | 112 | June 2024

You’ll notice two big things… “Big Boy Liquor” and “Big Boy Warehouse” combined for May and the sales totaled together.  Same thing in June for “Wine Stop”.  Also, in June, “Big Boy Warehouse” automatically changed to “Big Boy Liquor”.  It also changed the Acct ID.  Like I said before tho, the address and ID don’t really matter, because I don’t need to pull that data into any useful pivot tables. 

Ultimately, I want to create a pivot table from this table with the units sold data, and put it on a dashboard with a slicer that can be easily manipulated by myself and my company.

So, is there a function that recognizes these two different account names and then will combine them on the pivot table?

Or as I’ve discovered above, just will merely automatically replace my desired text with something else? (I don’t mind the same account having multiple entries each month, because a pivot table should automatically combine alike accounts together anyway)
Do I need to create another table as an in-between to make this consolidation possible?

Do I have to run some type of VBA code to translate this for me automatically?

Also note, I’d need to keep each month separated for tracking different sales data month-to-month or within different time periods historically within the pivot tables and dashboard. 

All this sales data are reports from a sales software website that my company uses. All the sales info comes from our distributor after units have been sold. The names of accounts are generated from that website. That website just has little to no functionality, so I wanted to create a more expressive Excel file to organize the data.

So essentially, each month, I can run that month’s report and export it to a CSV file and then copy and paste it into the Excel file I’m building. Sure, I could sit there manually and just combine all of these accounts that have multiple locations together, but there are LOTS of those examples. I was hoping to find a solution that would just automatically interpret these linked accounts, so once I set up the file, literally all I have to do is copy & paste the data every month.

I hope this might give a little more clarity into what I’m trying to achieve.