How to join multiple match and insert Total of each Group name using one line function

Question

Heres what my data look like in Data

Agent Name	Merchant Name	Committed Funding	Campaign Name Period
adyanti	Digicodes	7500000	11.11 (Oct W4)
adyanti	Digicodes	5000000	10.10 (Sep W4)
adyanti	ID Cloud Host	10000000	11.11 (Oct W4)
adyanti	Karyakarsa	17500000	10.10 (Sep W4)
adyanti	Karyakarsa	14000000	BAU (Oct W2)
adyanti	Karyakarsa	14000000	BAU (Oct W3)
adyanti	Karyakarsa	14000000	SPD&SMS Oct (Oct W4)
adyanti	KoinWorks	60000000	Tactical (Oct W4)
adyanti	Vision+	900000	10.10 (Sep W4)
adyanti	Vision+	900000	SPD&SMS Oct (Oct W4)

What I expected is something like this in Expectation Result

Agent Name	Merchant Name	Live Period	Total Budget
adyanti	Digicodes	11.11 (Oct W4), 10.10 (Sep W4)	12500000
adyanti	ID Cloud Host	11.11 (Oct W4)	10000000
adyanti	Karyakarsa	10.10 (Sep W4), BAU (Oct W2), BAU (Oct W3), SPD&SMS Oct (Oct W4)	59500000
adyanti	KoinWorks	Tactical (Oct W4)	60000000
adyanti	Vision+	10.10 (Sep W4), SPD&SMS Oct (Oct W4)	1800000
Total			143800000
Total All Submission			2164552000

for now, my approach for this is by using Helper reference which Generates a unique Agent name and merchant name using this line function

=Unique(query(Data!$A:$B,"select * where B is not null"))

And join the text of the campaign name /Merchant name/Agent name by using the formula from here.(Credits to @JPV) :

=Query(unique({Data!$B$2:$B, map(Data!$B$2:$B,  lambda( item, textjoin(", ", 1, unique(filter(Data!$D$2:$D, Data!$B$2:$B=item)))))}),"Select Col2")

After that, I generate the Total Budget of each merchant and also add the Total Budget of all merchants at the bottom using this line function

={Arrayformula(IF(LEN(INDIRECT("B1:"&"B"&COUNTA($B$2:$B)))=0,,SUMIF(Data!$B$2:$B,$B$2:$B,Data!$C$2:$C)));SUM(INDIRECT("D2:"&"D"&COUNTA($D$2:$D)))}

Using those three line functions above, my data looks like this in Helper

Agent Name	Merchant Name	Campaign Name Period	Total Budget
adyanti	Digicodes	11.11 (Oct W4), 10.10 (Sep W4)	12500000
adyanti	ID Cloud Host	11.11 (Oct W4)	10000000
adyanti	Karyakarsa	10.10 (Sep W4), BAU (Oct W2), BAU (Oct W3), SPD&SMS Oct (Oct W4)	59500000
adyanti	KoinWorks	Tactical (Oct W4)	60000000
adyanti	Vision+	10.10 (Sep W4), SPD&SMS Oct (Oct W4)	1800000

After preparing the Helper reference I finally using the formula From here.(Credits to @Osm) (Modified by me)

=ArrayFormula({
Helper!A1:D1;
SPLIT(QUERY(FLATTEN(SPLIT(
MAP(QUERY(UNIQUE(Helper!A2:A), "Where Col1 <>'' "),
SUMIF(Helper!A2:A,"="&QUERY(UNIQUE(Helper!A2:A), "Where Col1 
<>'' "),Helper!D2:D),
LAMBDA(fw,ct, 
TEXTJOIN("♣", 1,
BYROW(FILTER(Helper!A2:D,Helper!A2:A=fw)&"♦", 
LAMBDA(c,CONCATENATE(c))))&"♠"&"Total♦"&"  ♦"&"  
♦"&REPT("♦"&ct,1))), "♣♠")), "where Col1 <> ''"),"♦");
"Total All Submission",
" ",
" ",
SUMIF(Helper!A2:A,"<>",Helper!D2:D)
})

to generate all the data from Helper and and adding total row after each agent name, so my data in Result now looks exactly the same as in the Expectation table above

Agent Name	Merchant Name	Campaign Name Period	Total Budget
adyanti	Digicodes	11.11 (Oct W4), 10.10 (Sep W4)	12500000
adyanti	ID Cloud Host	11.11 (Oct W4)	10000000
adyanti	Karyakarsa	10.10 (Sep W4), BAU (Oct W2), BAU (Oct W3), SPD&SMS Oct (Oct W4)	59500000
adyanti	KoinWorks	Tactical (Oct W4)	60000000
adyanti	Vision+	10.10 (Sep W4), SPD&SMS Oct (Oct W4)	1800000
Total			143800000
Total All Submission			2164552000

So the conclusion is, I need something to simplify the process, I'm expecting to have 1 formula line that can generate data directly from Data to be Exactly the same as in Expectation without having to use helper reference.

But for now, I'm going to use this approach until someone is willing to help or maybe until I figure it out myself.

Here's a link to the sample spreadsheet

Thank You!

Your question can be greatly improved if you add input table and expected output table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. — TheMaster, Sep 29 '22 at 09:07
It's great that you give credit, but link to the actual answer or the user is required as per site rules. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). — TheMaster, Sep 29 '22 at 09:08
Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [tag info page](https://stackoverflow.com/tags/google-sheets-formula/info) for official documentation, free resources and more details. — TheMaster, Sep 29 '22 at 09:10
What's the difference between your "expected screenshot" and "using the two methods above my data is look like this:"? — TheMaster, Sep 29 '22 at 11:36
Your input table is not representative. Consider adding 2 items from each group for 3 groups (total 2x3=6 rows). And the expected output as a table for that 6 input items. See [mre]: Starting from scratch is step 1. — TheMaster, Sep 29 '22 at 11:40
The difference between the two is there is a total in Col A, and blank cells in col B & C after every group name in the expected screenshot. — byrmn__, Sep 30 '22 at 03:05
i want to edit my post but I cannot because I keep getting error message says "Your post appears to contain code that is not properly formatted as code. Please indent all code by 4 spaces using the code toolbar button or the CTRL+K keyboard shortcut. For more editing help, click the [?] toolbar icon." but I couldn't spot the error, can you help me? i put the edited post in here: https://docs.google.com/document/d/1gz4Szc7MVElOq7fDre37aPGGUxETu5vkp7MXsDaiNeQ/edit?usp=sharing — byrmn__, Sep 30 '22 at 07:02
As suggested before, please consider adding a simplified table with few items in order to clarify your purpose a bit. — Iamblichus, Oct 04 '22 at 09:31

Osm · Answer 1 · 2022-09-29T12:30:06.693

1

Updated

Use this formula

=ArrayFormula({
 Data!F3:H3;
 SPLIT(QUERY(FLATTEN(SPLIT(
 MAP(QUERY(UNIQUE(Data!F4:F), "Where Col1 <>'' "),
     COUNTIF(Data!F4:F,"="&QUERY(UNIQUE(Data!F4:F), "Where Col1 <>'' ")), LAMBDA(fw,ct, 
 TEXTJOIN("♣", 1,
 BYROW(FILTER(Data!F4:H,Data!F4:F=fw)&"♦", 
 LAMBDA(c,CONCATENATE(c))))&"♠"&REPT("♦"&ct,1))), "♣♠")), "where Col1 <> ''"),"♦")})

edited Sep 29 '22 at 12:30

answered Sep 29 '22 at 10:23

Osm

2,699
2
4
26

Thank you for your response, i just edit my question and edit my sample sheet, I hope it can be easier for you to understand what I want/meant – byrmn__ Sep 29 '22 at 11:08
You want blank not count?right @byrmn__ – Osm Sep 29 '22 at 11:10
I want "Total" for A and I want blank for B and C – byrmn__ Sep 29 '22 at 11:15
Adjust the range as shown F1:H @byrmn__ – Osm Sep 29 '22 at 11:20
ive tried it, but it generates a unique Group Name, Merchant Name, and Campaign Name instead of a unique Group Name, Merchant Name but join the campaign didnt do the join text for the campaign name, as whats in my sample sheet. – byrmn__ Sep 29 '22 at 11:32
@byrmn__ the answer is updated paste it anyware you like just keep the tab name `Data` – Osm Sep 29 '22 at 12:19
Hi @osm is it possible to move the Count to campaign name col? its really hard for me to understand your formula, for now i only able replace the count with "Total" but i ynable to put the count in the in the third column, please help. and btw here's the edited formula that i did =ArrayFormula({ A1:E1; SPLIT(QUERY(FLATTEN(SPLIT( MAP(QUERY(UNIQUE(A2:A), "Where Col1 <>'' "),LAMBDA(BD, TEXTJOIN("♣", 1, BYROW(FILTER(A2:E,A2:A=BD)&"♦", LAMBDA(c,CONCATENATE(c))))&"♠"&REPT("♦"&"Total",1))), "♣♠")), "where Col1 <> ''"),"♦")}) – byrmn__ Sep 30 '22 at 21:54
and one other thing, i really want to reformatting this post to follow the rule, but somehow i keep getting error message, i don't know where did i do wrong and if you don't mind, please help me to check it, here's the link to the txt https://docs.google.com/document/d/1gz4Szc7MVElOq7fDre37aPGGUxETu5vkp7MXsDaiNeQ/edit Thank you! – byrmn__ Sep 30 '22 at 21:57

How to join multiple match and insert Total of each Group name using one line function

1 Answers1

Updated