Grouping Columns based on Row Criteria and Creating a new Sheet for the results

[NicoleHou]

Revised Information, still looking for solution.

I am trying to Group Columns based on Row Criteria and Creating a new Sheet for the results.  For example, I am trying to group the columns based on the top row, then create a sheet with the "subtotals" for each group?

Data

			x	y	xx	z	x	z	y	zz	zz
			123P4	345P6	123P4	123P4	445P9	123P4	355P5	123P4	345P6
			P4	P6	P4	P4	P9	P4	P5	P4	P6
			Colby	Moore	Colby	Colby	Ralph	Colby	Jim	Colby	Moore
			Year To Date	Year To Date	Year To Date	Year To Date	Year To Date	Year To Date	Year To Date	Year To Date	Year To Date
			Aug-22	Aug-22	Aug-22	Aug-22	Aug-22	Aug-22	Aug-22	Aug-22	Aug-22
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC	C123P4 - XY Corp.	C123P4 - XY Corp.	C445P9 - BB LP	C123P4 - XY Corp.	C355P5 - ABC Investment LLC	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC
44123	TOTAL	Acct 1	12345.66	22345.66	12345.66	12345.66	32345.66	12345.66	10	12345.66	22345.66
44124	TOTAL	Acct 2	2345.68	32345.68	2345.68	2345.68	332345.68	2345.68	250	2345.68	4400
44125	TOTAL	Acct 3	564.88	1564.88	564.88	564.88	31564.88	564.88	700	564.88	1564.88
44125	TOTAL	Acct 4	12345.66	22345.66	12345.66	12345.66	32345.66	12345.66	10	12345.66	22345.66
44126	TOTAL	Acct 5	2345.68	32345.68	2345.68	2345.68	332345.68	2345.68	250	2345.68	32345.68
44127	TOTAL	Acct 6	564.88	1564.88	564.88	564.88	31564.88	564.88	700	564.88	1564.88
44128	TOTAL	Acct 7	564.88	1564.88	564.88	564.88	31564.88	564.88	700	564.88	1564.88
44129	TOTAL	Acct 8	12345.66	22345.66	200	12345.66	32345.66	12345.66	10	12345.66	22345.66
44130	TOTAL	Acct 9	2345.68	32345.68	2345.68	2345.68	332345.68	2345.68	250	2345.68	32345.68
44131	TOTAL	Acct 10	564.88	1564.88	400	564.88	31564.88	564.88	700	564.88	1564.88
44132	TOTAL	Acct 11	564.88	1564.88	564.88	564.88	31564.88	564.88	700	564.88	1564.88
44133	TOTAL	Acct 12	12345.66	22345.66	12345.66	600	32345.66	12345.66	10	12345.66	22345.66
44134	TOTAL	Acct 13	2345.68	32345.68	2345.68	2345.68	332345.68	2345.68	250	2345.68	32345.68
44135	TOTAL	Acct 14	564.88	1564.88	564.88	800	31564.88	100	700	564.88	1564.88
44136	TOTAL	Acct 15	564.88	1564.88	564.88	564.88	31564.88	564.88	700	564.88	1564.88
44137	TOTAL	Acct 16	12345.66	22345.66	12345.66	12345.66	32345.66	700	10	12345.66	22345.66
44138	TOTAL	Acct 17	2345.68	32345.68	2345.68	2345.68	332345.68	1000	250	3300	32345.68
44139	TOTAL	Acct 18	564.88	1564.88	564.88	564.88	31564.88	564.88	700	564.88	1564.88

Desired Output

			Group x	Group y	z	xx	zz
			Year To Date	Year To Date				Total
			Aug-22	Aug-22
Account		Account Description
44123	TOTAL	Acct 1	44691.32	22355.66	24691.32	12345.66	34691.32	138,775.28
44124	TOTAL	Acct 2	334691.36	32595.68	4691.36	2345.68	6745.68	381,069.76
44125	TOTAL	Acct 3	32129.76	2264.88	1129.76	564.88	2129.76	38,219.04
44125	TOTAL	Acct 4	44691.32	22355.66	24691.32	12345.66	34691.32	138,775.28
44126	TOTAL	Acct 5	334691.36	32595.68	4691.36	2345.68	34691.36	409,015.44
44127	TOTAL	Acct 6	32129.76	2264.88	1129.76	564.88	2129.76	38,219.04
44128	TOTAL	Acct 7	32129.76	2264.88	1129.76	564.88	2129.76	38,219.04
44129	TOTAL	Acct 8	44691.32	22355.66	24691.32	200	34691.32	126,629.62
44130	TOTAL	Acct 9	334691.36	32595.68	4691.36	2345.68	34691.36	409,015.44
44131	TOTAL	Acct 10	32129.76	2264.88	1129.76	400	2129.76	38,054.16
44132	TOTAL	Acct 11	32129.76	2264.88	1129.76	564.88	2129.76	38,219.04
44133	TOTAL	Acct 12	44691.32	22355.66	12945.66	12345.66	34691.32	127,029.62
44134	TOTAL	Acct 13	334691.36	32595.68	4691.36	2345.68	34691.36	409,015.44
44135	TOTAL	Acct 14	32129.76	2264.88	900	564.88	2129.76	37,989.28
44136	TOTAL	Acct 15	32129.76	2264.88	1129.76	564.88	2129.76	38,219.04
44137	TOTAL	Acct 16	44691.32	22355.66	13045.66	12345.66	34691.32	127,129.62
44138	TOTAL	Acct 17	334691.36	32595.68	3345.68	2345.68	35645.68	408,624.08
44139	TOTAL	Acct 18	32129.76	2264.88	1129.76	564.88	2129.76	38,219.04
	Total		2,153,951.48	292,875.74	130,984.72	65,665.20	336,960.12	2,980,437.26

I tried transpose and then summarize plus multi field formula  but I can't seem to get it.  Thank YOU!

[binuacs]

@NicoleHou One way of doing this

[NicoleHou]

I edited my post to include a better example

I will try another example, that worked perfectly for the two groups, but I have 9 "groups" and hundreds of accounts and columns....so I was hoping to group those that had the same group name and keep all accounts....but I would have to do the formula hundreds of formulas and know which one was what group.  Any tips to make this easier?

Thank You very Much! 

[binuacs · Accepted solution]

@NicoleHou attaching the updated workflow

[NicoleHou]

@binuacs Thank you!