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

Question

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!

michaelmench · Answer

Jmd07fsu&nbsp;Yes, but it depends on how you like working with query. &nbsp;You can make all the sheets tables and import data as a query and then rename the columns.&nbsp;But what it sounds like is you really want is an automated pull each month.&nbsp;This will help. &nbsp;It’s high level stuff, but go slow and pause often. &nbsp;There are a few clicks he does that he doesn’t explain and it can cause you a little trouble. &nbsp;https://youtu.be/ktgdDPNXiMg?si=Wx2DML9APbpUzgs5or worse (better)https://youtu.be/0ZOY8is-bgY?si=sLXJyFkjiSLUYvtX&nbsp;One thing to keep in mind is that the way you undo something in Query is to delete the step in the query history.&nbsp;Oh… if you cannot find PowerQuery, tab Data… GetData… slide down to Launch Power Query Editor&nbsp;else… watch some of guy in a cube videos.&nbsp;Good luck! &nbsp;Keep us posted. -mm

oliverscheurich · Answer

Jmd07fsu&nbsp;=LET(accounts,UNIQUE(TEXTBEFORE(A2:A17," ",2)),HSTACK(accounts,CHOOSECOLS(B2:E17,XMATCH("*"&amp;accounts&amp;"*",A2:A17,2)),XLOOKUP("*"&amp;accounts&amp;"*",A2:A17,E2:E17,,2,-1)))&nbsp;With Excel for the web or Office 365 you can use this formula.

jmd07fsu · Answer

OliverScheurich&nbsp;sorry that im just now getting back to you. &nbsp;Thank you for your help on this.&nbsp;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.&nbsp;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? &nbsp;Also, keep in mind, not EVERY account has a warehouse or has multiple locations. &nbsp;Some just have one.&nbsp;2. Also, within your scenario, the accounts and linked warehouses have the same address and account ID. &nbsp;This is not the case in my problem. &nbsp;Im fine with the address and ID consolidating to the main accounts (I don't need both to stay intact). &nbsp;How do we deal with that?&nbsp;3. &nbsp;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. &nbsp;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. &nbsp;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?&nbsp;To give you some more context, here’s some more info as well into what im trying to do. &nbsp;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.&nbsp;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.&nbsp;Here’s how the main data input table would look with data from my distributor:&nbsp;Account Name | Acct ID | Units Sold | Month&nbsp;Big Boy Liquor | A569 | 75 | May 2024Wine Stop Market | C123 | 52 | May 2024Big Boy Warehouse | B718 | 48 | May 2024Jr Food &amp; Deli | A331 | 82 | May 2024John’s Liquor Mart | C423 | 38 | May 2024Market Street Deli | B610 | 65 | June 2024Big Boy Warehouse | B718 | 49 | June 2024Wine Stop Market | C123 | 71 | June 2024Wine Stop Warehouse | D121 | 41 | June 2024&nbsp;Here’s how I’d want the final result to look:&nbsp;Big Boy Liquor | A569 | 123 | May 2024Wine Stop Market | C123 | 52 | May 2024Jr Food &amp; Deli | A331 | 82 | May 2024John’s Liquor Mart | C423 | 38 | May 2024Market Street Deli | B610 | 65 | June 2024Big Boy Liquor | A569 | 71 | June 2024Wine Stop Market | C123 | 112 | June 2024&nbsp;You’ll notice two big things… “Big Boy Liquor” and “Big Boy Warehouse” combined for May and the sales totaled together. &nbsp;Same thing in June for “Wine Stop”. &nbsp;Also, in June, “Big Boy Warehouse” automatically changed to “Big Boy Liquor”. &nbsp;It also changed the Acct ID. &nbsp;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.&nbsp;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.&nbsp;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?&nbsp;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.&nbsp;&nbsp;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.&nbsp;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 &amp; paste the data every month.&nbsp;I hope this might give a little more clarity into what I’m trying to achieve.&nbsp;

peiyezhu · Answer

Jmd07fsu&nbsp;&nbsp;https://techcommunity.microsoft.com/t5/excel/aligning-1st-and-last-name-column-in-excel-with-similar-jpg-file/m-p/3889608fuzzy match？find minium edit distance?

patrick2788 · Answer

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.

Forum Discussion

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

Share

Resources