Dynamic data rollup by Row - non specific criteria

[Andy395]

I am trying to condense a large data set by creating a two tab 'machine'. One tab takes the raw data (50,000 rows +), and the second tab produces a rollup of the data while maintaining the same array width. (i.e. column positions need to respected).

I have tried using both FILTER and/or UNIQUE and it gets me very close, but something isn't quite right.

I have a range A2:T10000
Columns A,B,C,D,E,F,G,H,J,L,M,P,R,S,T comprise a unique ROW ID.
Columns I,K,N,O,Q are data that will need to be summed once the data set is rolled up.
Column P is that data that needs to be ignored to allow the data set to be rolled up.

So by ignoring the ID values in column P the overall Row ID becomes more generic allowing the data set to be collapsed into a more general grouping.

I am not able to use FILTER (array, {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,1,1,1,1}) as this will remove the column. The column needs to remain as there will be one condition where the Column P ID will be retained (based on the date in column A), but this I can address separately with a VSTACK (maybe).

Does anyone know how to manipulate FILTER/UNIQUE so that I can paste a single 2D array equation in the A2 position of the rollup sheet, which will maintain the original data set column positions, ignore data in column P and sum the data in Columns I,K,N,O,Q after UNIQUE has created a reduced list?

 

[hagia_sofia]

It is probably doable with a single but rather complicated fomula; therefore, could you please post some sample data (e.g. 10 rows)?

 

[JamesCanale]

I simplified the input a good amount, and it got pretty ugly when I did. But this takes the unique records for multiple columns and sums the values in those records. I think it's what you want in a smaller form.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Age	SN	Rank	Value	Hair	Value2			Name	Age	SN	Rank	Value	Hair	Value2
2	James	51	123	Captain	5	Black	500			James	51	123	Captain	64	Black	2523
3	Fred	45	456	General	43	Grey	604			Fred	45	456	General	62	Grey	9260
4	Harry	60	789	Captain	5	Brown	156			Harry	60	789	Captain	81	Brown	5644
5	James	51	123	Captain	5	Black	346
6	Fred	45	456	General	6	Grey	7567
7	Harry	60	789	Captain	7	Brown	345
8	James	51	123	Captain	45	Black	234
9	Fred	45	456	General	4	Grey	65
10	Harry	60	789	Captain	3	Brown	234
11	James	51	123	Captain	2	Black	567
12	Fred	45	456	General	5	Grey	346
13	Harry	60	789	Captain	64	Brown	4453
14	James	51	123	Captain	7	Black	876
15	Fred	45	456	General	4	Grey	678
16	Harry	60	789	Captain	2	Brown	456
Sheet11

Cell Formulas
Range	Formula
J2:P4	J2	=LET(a,A2:G16,b,CHOOSECOLS(a,1,2,3,4,6),c,UNIQUE(b),d,MMULT(--(TOROW(CHOOSECOLS(a,1))=CHOOSECOLS(b,1))*(TOROW(CHOOSECOLS(a,2))=CHOOSECOLS(b,2))*(TOROW(CHOOSECOLS(a,3))=CHOOSECOLS(b,3))*(TOROW(CHOOSECOLS(a,4))=CHOOSECOLS(b,4))*(TOROW(CHOOSECOLS(a,6))=CHOOSECOLS(b,5)),CHOOSECOLS(a,5)),e,MMULT(--(TOROW(CHOOSECOLS(a,1))=CHOOSECOLS(b,1))*(TOROW(CHOOSECOLS(a,2))=CHOOSECOLS(b,2))*(TOROW(CHOOSECOLS(a,3))=CHOOSECOLS(b,3))*(TOROW(CHOOSECOLS(a,4))=CHOOSECOLS(b,4))*(TOROW(CHOOSECOLS(a,6))=CHOOSECOLS(b,5)),CHOOSECOLS(a,7)),f,TAKE(d,ROWS(c)),g,TAKE(e,ROWS(c)),h,HSTACK(c,f,g),i,CHOOSECOLS(h,1,2,3,4,6,5,7),i)
Dynamic array formulas.

 

[Fluff]

So by ignoring the ID values in column P the overall Row ID becomes more generic allowing the data set to be collapsed into a more general grouping.

But if you want to keep col P in the output, how do you determine which values to keep?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Andy395]

I simplified the input a good amount, and it got pretty ugly when I did. But this takes the unique records for multiple columns and sums the values in those records. I think it's what you want in a smaller form.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Age	SN	Rank	Value	Hair	Value2			Name	Age	SN	Rank	Value	Hair	Value2
2	James	51	123	Captain	5	Black	500			James	51	123	Captain	64	Black	2523
3	Fred	45	456	General	43	Grey	604			Fred	45	456	General	62	Grey	9260
4	Harry	60	789	Captain	5	Brown	156			Harry	60	789	Captain	81	Brown	5644
5	James	51	123	Captain	5	Black	346
6	Fred	45	456	General	6	Grey	7567
7	Harry	60	789	Captain	7	Brown	345
8	James	51	123	Captain	45	Black	234
9	Fred	45	456	General	4	Grey	65
10	Harry	60	789	Captain	3	Brown	234
11	James	51	123	Captain	2	Black	567
12	Fred	45	456	General	5	Grey	346
13	Harry	60	789	Captain	64	Brown	4453
14	James	51	123	Captain	7	Black	876
15	Fred	45	456	General	4	Grey	678
16	Harry	60	789	Captain	2	Brown	456
Sheet11

Cell Formulas
Range	Formula
J2:P4	J2	=LET(a,A2:G16,b,CHOOSECOLS(a,1,2,3,4,6),c,UNIQUE(b),d,MMULT(--(TOROW(CHOOSECOLS(a,1))=CHOOSECOLS(b,1))*(TOROW(CHHi OOSECOLS(a,2))=CHOOSECOLS(b,2))*(TOROW(CHOOSECOLS(a,3))=CHOOSECOLS(b,3))*(TOROW(CHOOSECOLS(a,4))=CHOOSECOLS(b,4))*(TOROW(CHOOSECOLS(a,6))=CHOOSECOLS(b,5)),CHOOSECOLS(a,5)),e,MMULT(--(TOROW(CHOOSECOLS(a,1))=CHOOSECOLS(b,1))*(TOROW(CHOOSECOLS(a,2))=CHOOSECOLS(b,2))*(TOROW(CHOOSECOLS(a,3))=CHOOSECOLS(b,3))*(TOROW(CHOOSECOLS(a,4))=CHOOSECOLS(b,4))*(TOROW(CHOOSECOLS(a,6))=CHOOSECOLS(b,5)),CHOOSECOLS(a,7)),f,TAKE(d,ROWS(c)),g,TAKE(e,ROWS(c)),h,HSTACK(c,f,g),i,CHOOSECOLS(h,1,2,3,4,6,5,7),i)
Dynamic array formulas.

Hi James, this is close, but needs to drop a term. If we look at your data set, what i'm trying to do is ignore the name and SN so that the values get rollup up by rank. In my data set column O would be the SN, and there would not be additional correlation to that individual (i.e. name and age). I'll try and put together a mini sheet

 

[Andy395]

But if you want to keep col P in the output, how do you determine which values to keep?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello, the criteria is the dates in column A. IF A:A >= EDATE(current month, -3) then take data set as is (no condensing). IF A:A < EDATE(current month, -3) then ignore data in column O and perform resulting SUMs based on new unique ROW ID.

I'll try out this xlbb thing.

 

[Andy395]

I simplified the input a good amount, and it got pretty ugly when I did. But this takes the unique records for multiple columns and sums the values in those records. I think it's what you want in a smaller form.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Age	SN	Rank	Value	Hair	Value2			Name	Age	SN	Rank	Value	Hair	Value2
2	James	51	123	Captain	5	Black	500			James	51	123	Captain	64	Black	2523
3	Fred	45	456	General	43	Grey	604			Fred	45	456	General	62	Grey	9260
4	Harry	60	789	Captain	5	Brown	156			Harry	60	789	Captain	81	Brown	5644
5	James	51	123	Captain	5	Black	346
6	Fred	45	456	General	6	Grey	7567
7	Harry	60	789	Captain	7	Brown	345
8	James	51	123	Captain	45	Black	234
9	Fred	45	456	General	4	Grey	65
10	Harry	60	789	Captain	3	Brown	234
11	James	51	123	Captain	2	Black	567
12	Fred	45	456	General	5	Grey	346
13	Harry	60	789	Captain	64	Brown	4453
14	James	51	123	Captain	7	Black	876
15	Fred	45	456	General	4	Grey	678
16	Harry	60	789	Captain	2	Brown	456
Sheet11

Cell Formulas
Range	Formula
J2:P4	J2	=LET(a,A2:G16,b,CHOOSECOLS(a,1,2,3,4,6),c,UNIQUE(b),d,MMULT(--(TOROW(CHOOSECOLS(a,1))=CHOOSECOLS(b,1))*(TOROW(CHOOSECOLS(a,2))=CHOOSECOLS(b,2))*(TOROW(CHOOSECOLS(a,3))=CHOOSECOLS(b,3))*(TOROW(CHOOSECOLS(a,4))=CHOOSECOLS(b,4))*(TOROW(CHOOSECOLS(a,6))=CHOOSECOLS(b,5)),CHOOSECOLS(a,5)),e,MMULT(--(TOROW(CHOOSECOLS(a,1))=CHOOSECOLS(b,1))*(TOROW(CHOOSECOLS(a,2))=CHOOSECOLS(b,2))*(TOROW(CHOOSECOLS(a,3))=CHOOSECOLS(b,3))*(TOROW(CHOOSECOLS(a,4))=CHOOSECOLS(b,4))*(TOROW(CHOOSECOLS(a,6))=CHOOSECOLS(b,5)),CHOOSECOLS(a,7)),f,TAKE(d,ROWS(c)),g,TAKE(e,ROWS(c)),h,HSTACK(c,f,g),i,CHOOSECOLS(h,1,2,3,4,6,5,7),i)
Dynamic array formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date	Project	Department	Department Name	Cost Type	Component	Year Fiscal	Fiscal Period	Cost Amount	Original currency	Fx	Labor Skill	Labor Code	Qty	No. of Hours	Employee	OH	Actual budget?	Parent	% Complete
2	Mar-24	254687	411	Sales	1	BCA1	2024	3	323.4	USD	1	BC	S1	0	3.5	76636	0	Actual	4.1	0
3	Mar-24	254687	311	Marketing	1	BCA1	2024	3	323.4	USD	1	BC	S1	0	3.5	76636	0	Actual	3.1	0
4	Mar-24	254687	220	Finance	1	BCA1	2024	3	739.2	USD	1	BC	S1	0	8	76780	0	Actual	2.2	0
5	Jun-24	254687	910	Front office	1	DSA1	2024	6	300.3	USD	1	DS	S1	0	3.25	77292	0	Actual	9.1	0
6	Jun-24	254687	443	accounting	1	DSA1	2024	6	693	USD	1	DS	S1	0	7.5	79360	0	Actual	4.4	0
7	Jul-23	254687	542	engineering	1	DSA2	2023	7	471	USD	1	DS	S2	0	7.5	79334	0	Actual	5.4	0
8	Jul-23	254687	542	engineering	1	DSA2	2023	7	471	USD	1	DS	S2	0	7.5	79334	0	Actual	5.4	0
9	Jun-24	254687	1040	stores	1	CDA1	2024	6	92.4	USD	1	CD	S1	0	1	78371	0	Actual	10.4	0
10	Jan-24	254687	1040	stores	1	CDA3	2024	1	25.36	USD	1	CD	S3	0	0.5	79270	0	Actual	10.4	0
11	Jan-23	254687	321	shipping	1	ASA1	2023	1	1039.37	USD	1	AS	S1	0	11.5	77782	0	Actual	3.2	0
12	Jan-23	254687	321	shipping	1	ASA1	2023	1	10077.37	USD	1	AS	S1	0	111.5	79584	0	Actual	3.2	0
minisheet

 

[Andy395]

Hello, the criteria is the dates in column A. IF A:A >= EDATE(current month, -3) then take data set as is (no condensing). IF A:A < EDATE(current month, -3) then ignore data in column O and perform resulting SUMs based on new unique ROW ID.

I'll try out this xlbb thing.

I meant column P as per the original post (not O)

 

[Andy395]

I meant column P as per the original post (not O)

@Fluff too complicated to solve?