Forum Discussion
Combine 2 unique row entries from a table into 1 consolidated row entry
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.
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?
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?
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.
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.