textsplit() + mode.mult() = My happiness (maybe? finally?)

[bksbksbks]

In the data below, the grouping data is generated in a CSV, then formulated to output the grouping ID (A) and the jobs within that grouping (B). I'm trying to find the most common job within that grouping, which will sometimes have a single result in the CSV (like Grouping1) or multiple results (like Grouping2).

Book1
	A	B	C	D
1	Data		Desired Result
2	id	output	Grouping1	Grouping2
3	Grouping1	CIO|DIRECTOR OF IS|NETWORK MGR	CIO|DIRECTOR OF IS|NETWORK MGR	Pump Deli Clerk|PUMP MGR
4	Grouping2	Pump Deli Clerk|PUMP MGR|PUMP DIRECTOR
5	Grouping2	PUMP MGR|PUMP FOOD COURT AST MGR|PUMP FOOD COURT ASSOCIATE|PUMP SALES ASSOCIATE|PUMP MAINTENANCE CLERK|STORE ASSOC UNDER 19|PUMP 1ST ASST MGR|Pump Deli Clerk
Sheet1

In Grouping1 there is only result, so the three jobs within that result are each the most common. In Grouping2 there are two results, so counting all of the jobs between the results finds that Pump Deli Clerk and PUMP MGR both occur twice, making them the most common results.

I have attempted several variations of mode.mult(), index(), match(), and everything else I can think of, but nothing wants to play well with textsplit() to pull out the job titles. How can I find the most common job within each grouping?

As always, thanks to all of the dedicated volunteers who spend their time helping out everyone on their Excel learning journeys!

 

[Cubist]

Try:

Book1
	A	B	C	D
1	Data		Desired Result
2	id	output	Grouping1	Grouping2
3	Grouping1	CIO|DIRECTOR OF IS|NETWORK MGR	CIO|DIRECTOR OF IS|NETWORK MGR	Pump Deli Clerk|PUMP MGR
4	Grouping2	Pump Deli Clerk|PUMP MGR|PUMP DIRECTOR
5	Grouping2	PUMP MGR|PUMP FOOD COURT AST MGR|PUMP FOOD COURT ASSOCIATE|PUMP SALES ASSOCIATE|PUMP MAINTENANCE CLERK|STORE ASSOC UNDER 19|PUMP 1ST ASST MGR|Pump Deli Clerk
Sheet3

Cell Formulas
Range	Formula
C3:D3	C3	=LET(t,TEXTSPLIT(TEXTJOIN("|",,FILTER($B$3:$B$5,$A$3:$A$5=C2)),,"|"),g,GROUPBY(t,t,COUNTA,,0),TEXTJOIN("|",,FILTER(INDEX(g,,1),INDEX(g,,2)=MAX(INDEX(g,,2)))))

 

[bksbksbks]

Try:

Book1
	A	B	C	D
1	Data		Desired Result
2	id	output	Grouping1	Grouping2
3	Grouping1	CIO|DIRECTOR OF IS|NETWORK MGR	CIO|DIRECTOR OF IS|NETWORK MGR	Pump Deli Clerk|PUMP MGR
4	Grouping2	Pump Deli Clerk|PUMP MGR|PUMP DIRECTOR
5	Grouping2	PUMP MGR|PUMP FOOD COURT AST MGR|PUMP FOOD COURT ASSOCIATE|PUMP SALES ASSOCIATE|PUMP MAINTENANCE CLERK|STORE ASSOC UNDER 19|PUMP 1ST ASST MGR|Pump Deli Clerk
Sheet3

Cell Formulas
Range	Formula
C3:D3	C3	=LET(t,TEXTSPLIT(TEXTJOIN("|",,FILTER($B$3:$B$5,$A$3:$A$5=C2)),,"|"),g,GROUPBY(t,t,COUNTA,,0),TEXTJOIN("|",,FILTER(INDEX(g,,1),INDEX(g,,2)=MAX(INDEX(g,,2)))))

I have tried this both on the actual file and a dummy sheet and it only returns a #NAME? error. Office365 does not seem to want to recognize groupby().

 

[saboh12617]

Hello,

I think it can be simplified but this big formula should work

=LET(tblI,A3:B5,
grped,BYROW(UNIQUE(TAKE(tblI,,1)),LAMBDA(grp,TEXTJOIN("|",TRUE,FILTER(TAKE(tblI,,-1),TAKE(tblI,,1)=grp,"")))),
sep,DROP(REDUCE("",grped,LAMBDA(acc,v,VSTACK(acc,TEXTSPLIT(v,"|")))),1),
nums,MAKEARRAY(ROWS(sep),COLUMNS(sep),LAMBDA(r,c,
IFERROR(SUM(--(IFERROR(CHOOSEROWS(sep,r),0)=INDEX(sep,r,c))),0))),
maxes,BYROW(nums,MAX),
vals,MAKEARRAY(ROWS(sep),COLUMNS(sep),LAMBDA(r,c,IF(INDEX(nums,r,c)=CHOOSEROWS(maxes,r),INDEX(sep,r,c),""))),
res,BYROW(vals,LAMBDA(r,TEXTJOIN("|",TRUE,UNIQUE(r,TRUE)))),
TRANSPOSE(HSTACK(UNIQUE(A3:A5),res)))

 

[bksbksbks]

Hello,

I think it can be simplified but this big formula should work

=LET(tblI,A3:B5,
grped,BYROW(UNIQUE(TAKE(tblI,,1)),LAMBDA(grp,TEXTJOIN("|",TRUE,FILTER(TAKE(tblI,,-1),TAKE(tblI,,1)=grp,"")))),
sep,DROP(REDUCE("",grped,LAMBDA(acc,v,VSTACK(acc,TEXTSPLIT(v,"|")))),1),
nums,MAKEARRAY(ROWS(sep),COLUMNS(sep),LAMBDA(r,c,
IFERROR(SUM(--(IFERROR(CHOOSEROWS(sep,r),0)=INDEX(sep,r,c))),0))),
maxes,BYROW(nums,MAX),
vals,MAKEARRAY(ROWS(sep),COLUMNS(sep),LAMBDA(r,c,IF(INDEX(nums,r,c)=CHOOSEROWS(maxes,r),INDEX(sep,r,c),""))),
res,BYROW(vals,LAMBDA(r,TEXTJOIN("|",TRUE,UNIQUE(r,TRUE)))),
TRANSPOSE(HSTACK(UNIQUE(A3:A5),res)))

That IS a big formula! Unfortunately, like Cubist's response, I receive a #NAME? error.

 

[Cubist]

I have tried this both on the actual file and a dummy sheet and it only returns a #NAME? error. Office365 does not seem to want to recognize groupby().

You probably don't have the latest. Alternative:

Book1
	A	B	C	D
1	Data		Desired Result
2	id	output	Grouping1	Grouping2
3	Grouping1	CIO|DIRECTOR OF IS|NETWORK MGR	CIO|DIRECTOR OF IS|NETWORK MGR	Pump Deli Clerk|PUMP MGR
4	Grouping2	Pump Deli Clerk|PUMP MGR|PUMP DIRECTOR
5	Grouping2	PUMP MGR|PUMP FOOD COURT AST MGR|PUMP FOOD COURT ASSOCIATE|PUMP SALES ASSOCIATE|PUMP MAINTENANCE CLERK|STORE ASSOC UNDER 19|PUMP 1ST ASST MGR|Pump Deli Clerk
Sheet3

Cell Formulas
Range	Formula
C3:D3	C3	=LET(t,TEXTSPLIT(TEXTJOIN("|",,FILTER($B$3:$B$5,$A$3:$A$5=C2)),,"|"),ct,MAP(t,LAMBDA(m,SUM(--(m=t)))),TEXTJOIN("|",,UNIQUE(FILTER(t,ct=MAX(ct)))))

 

[Fluff]

I receive a #NAME? error.

Are you trying this in 365 or 2019?

 

[bksbksbks]

You probably don't have the latest. Alternative:

Book1
	A	B	C	D
1	Data		Desired Result
2	id	output	Grouping1	Grouping2
3	Grouping1	CIO|DIRECTOR OF IS|NETWORK MGR	CIO|DIRECTOR OF IS|NETWORK MGR	Pump Deli Clerk|PUMP MGR
4	Grouping2	Pump Deli Clerk|PUMP MGR|PUMP DIRECTOR
5	Grouping2	PUMP MGR|PUMP FOOD COURT AST MGR|PUMP FOOD COURT ASSOCIATE|PUMP SALES ASSOCIATE|PUMP MAINTENANCE CLERK|STORE ASSOC UNDER 19|PUMP 1ST ASST MGR|Pump Deli Clerk
Sheet3

Cell Formulas
Range	Formula
C3:D3	C3	=LET(t,TEXTSPLIT(TEXTJOIN("|",,FILTER($B$3:$B$5,$A$3:$A$5=C2)),,"|"),ct,MAP(t,LAMBDA(m,SUM(--(m=t)))),TEXTJOIN("|",,UNIQUE(FILTER(t,ct=MAX(ct)))))

That does it! Thank you!

Are you trying this in 365 or 2019?

365

 

[Cubist]

That does it! Thank you!

You're welcome.
My 365 Enterprise license does not have GROUPBY or PIVIOTBY. I'm guessing you're using the same.