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

[Jmd07fsu]

So I’m hoping there’s a solution to this, but I’m not expert level at excel and can’t figure it out.

To give some background, I’m inputting sales data each month into a table that has thousands of different accounts. In the sales data that I’m pulling, some of the accounts have multiple locations (like a store and a warehouse) and the sales data will be separated into two separate unique rows with unique names, addresses, and account IDs.

For this example, let’s say I have two different rows that are reflected as “Account A Store” and “Account A Warehouse” (again both with unique addresses and unique account IDs with their own respective sales data).

Is there anyway to combine these two separate rows into one consolidated “Account A” row. Whether that be moving it into another Table or Pivot Table? Or can this be done with any formatting or VBA?

This example needs to be repeated, as there are numerous accounts like this, and I’m entering new sales data every month.

Thanks and I appreciate any help!

[#2]

@Jmd07fsu 

Yes, but it depends on how you like working with query.  You can make all the sheets tables and import data as a query and then rename the columns.

But what it sounds like is you really want is an automated pull each month.

This will help.  It’s high level stuff, but go slow and pause often.  There are a few clicks he does that he doesn’t explain and it can cause you a little trouble.  

https://youtu.be/ktgdDPNXiMg?si=Wx2DML9APbpUzgs5

or worse (better)

https://youtu.be/0ZOY8is-bgY?si=sLXJyFkjiSLUYvtX

One thing to keep in mind is that the way you undo something in Query is to delete the step in the query history.

Oh… if you cannot find PowerQuery, tab Data… GetData… slide down to Launch Power Query Editor

else… watch some of guy in a cube videos.

Good luck!  Keep us posted. -mm

[#3]

@Jmd07fsu 

=LET(accounts,

UNIQUE(TEXTBEFORE(A2:A17," ",2)),

HSTACK(

accounts,

CHOOSECOLS(B2:E17,XMATCH("*"&accounts&"*",A2:A17,2)),

XLOOKUP("*"&accounts&"*",A2:A17,E2:E17,,2,-1)

)

)

With Excel for the web or Office 365 you can use this formula.

[#4]

@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. 

[#5]

@Jmd07fsu 

https://techcommunity.microsoft.com/t5/excel/aligning-1st-and-last-name-column-in-excel-with-similar...

fuzzy match？

find minium edit distance?

[#6]

Whichever option you choose you're going to need to train Excel how to recognize "Store A" is the same as "Store A -revised", for example. There are ways to do partial matches but then accuracy of results becomes a concern. The best solution may be to normalize the data and eliminate the discrepancies in the way a given name is listed.

[#7]

@Jmd07fsu 

In the sample file the below formula returns a database that can be used for a Pivot table. You only have to change A2:D28 according to the actual size of your data. Then the formula updates the spilled result. In cell H15 "New New Warehouse" is returned because there is no "New New Inn" or "New New Deli" or else with "New New ". The formula is in cell H2.

=LET(rng,A2:D28,HSTACK(MAP(INDEX(rng,,1),BYROW(INDEX(rng,,1),LAMBDA(xx,XLOOKUP(TRUE,ISNUMBER(SEARCH(UNIQUE(FILTER(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),LEN(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""))>0)),xx)),UNIQUE(FILTER(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),LEN(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""))>0)),""))),BYROW(TEXTBEFORE(INDEX(rng,,1)," ",2,,,"")=TRANSPOSE(TRIM(UNIQUE(FILTER(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),LEN(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""))>0)))),LAMBDA(yy,OR(yy))),BYROW(SEQUENCE(ROWS(INDEX(rng,,1))),LAMBDA(y,IF(LEN(INDEX(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),y))>0,SUM(N(ISNUMBER(SEARCH(INDEX(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),y),INDEX(rng,,1))))),""))),LAMBDA(t,u,v,w,IFS(w=1,t,v,XLOOKUP("*"&u&"*",BYROW(UNIQUE(FILTER(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),LEN(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""))>0)),LAMBDA(rr,UNIQUE(FILTER(INDEX(rng,,1),ISNUMBER(SEARCH(rr,INDEX(rng,,1)))*(NOT(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))))),"")))),BYROW(UNIQUE(FILTER(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""),LEN(IF(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))),TEXTBEFORE(INDEX(rng,,1),"Warehouse"),""))>0)),LAMBDA(rr,UNIQUE(FILTER(INDEX(rng,,1),ISNUMBER(SEARCH(rr,INDEX(rng,,1)))*(NOT(ISNUMBER(SEARCH("warehouse",INDEX(rng,,1))))),"")))),"",2),1,t))),DROP(rng,,1)))

Alternatively you can create an in-between result and an end result as spilled by formulas in cells H8 and R8. However i'd calculate the sum for each Account name in every Month with a Pivot table with the spilled result from the formula in cell H2.

[#8]

First of all, this is absolutely incredible. And I can’t even begin to understand the intense amount of formulas you used in this. However, from trying to understand it, it does seem like the text is being triggered by the exact text “Warehouse”. Is that correct?

Unfortunately, for my actual data, the terminology and wording of the entries can be plentiful and different.

I was reading up on some other things. Is there a way I can use conditional formatting within a table to recognize specific text and change it to the desired text? If I went this route, does that screw up the consolidation of sales in the pivot table because the conditional formatting is just “hiding” the actual entered text?

[#9]

@Jmd07fsu 

However, from trying to understand it, it does seem like the text is being triggered by the exact text “Warehouse”. Is that correct?

TEXTBEFORE(INDEX(rng,,1),"Warehouse"). This formula looks for an exact match of "Warehouse" (with a capital W). SEARCH("warehouse",INDEX(rng,,1)). This formula looks for "warehouse" and "Warehouse".  

Is there a way I can use conditional formatting within a table to recognize specific text and change it to the desired text?

You can use conditional formatting to recognize specific text (by highlighted the cell containing the text) however it's not possible to change it to the desired text. This should be possible with formulas.

If I went this route, does that screw up the consolidation of sales in the pivot table because the conditional formatting is just “hiding” the actual entered text?

I think this shouldn't be possible because we can't change desired text by conditional formatting. However i'm not 100% sure i correctly understand the question.

Could you give a few examples of the plentiful and different terminology and wording of the entries along with the expected result?

[#10]

@OliverScheurich 

So I manage over 1000 accounts.  The sales report I receive come from our distributor, and the account names are generated by them.

Like I mentioned, some of the accounts have multiple locations, and when pulling a pivot table for the entire 1000+ account universe, I want those linked accounts to reflex as one.

These linked accounts could all differ in how they’re named.  Some examples of linked accounts:

• Big Boy Liquor & Big Boy Warehouse
• Top Spot, Top Spot #2, Top Spot Wine
• Gary’s Deli, Gary’s Liquor, Gary’s Warehouse
• Main St W&S, Main St Wine, Main St Liquor, Main St W&Sp
• Keg & Cork, Keg & Cork Liquor, Keg & Cork Liq, Keg & Cork #2, Keg & Cork G

Sometimes theres just one letter difference, and sometimes it’s a different word.  It’s pretty confusing tbh.  It might just be easier for me to manually change them after input.  It’s not like every store has sales every single month, so it wouldn’t be that time consuming.  I was just hoping for a way for it to automatically link certain accounts together for the pivot table, so once I set up to file, I would simply just have to copy & paste the distributors report every month. 

[#11]

@Jmd07fsu 

Perhaps you can use the suggestion either by @Patrick2788  or by @peiyezhu .

I must admit that my previous suggestions aren't useful for your data. The only idea i have is to manually set up a reference table as shown in the screenshot. Then you can reference this table either with XLOOKUP or INDEX/MATCH and return a unique account name for e.g. Main St W&S, Main St Wine, Main St Liquor and Main St W&Sp. I'm unhappy with this suggestion because it takes a lot of time to set up the reference table. On the other hand i believe this will save you time and work in the long run. You don't have to manually change the account names listed in the reference table anymore.