Need to make a dynamic table

[metaman15]

Hi everyone, thanks for taking a look.

I have users who need to input values into a sheet such as below:

Table A

Date	Donor #	Festival Party	Rennovation Donations	Food Donations	International Relief Donations	Total
3/1/2023	1001011	Christmas		$101.00	$51.00	$152.00
3/2/2023	1001012	Easter	$201.00	$151.00		$352.00
3/3/2023	1001013	Easter	$301.00	$201.00	$101.00	$603.00

And need a dynamic table for the output such as below:

Table B

Date	Donor	Amount	Purpose	Class
3/1/2023	1001011	$101.00	Christmas	Food Donations
3/1/2023	1001011	$51.00	Christmas	International Relief Donations
3/2/2023	1001012	$201.00	Easter	Rennovation Donations
3/2/2023	1001012	$150.00	Easter	Food Donations
3/3/2023	1001013	$301.00	Easter	Rennovation Donations
3/3/2023	1001013	$201.00	Easter	Food Donations
3/3/2023	1001013	$101.00	Easter	International Relief Donations

The output table (Table B) needs to update every time a new row is added to Table A.

I tried many different ideas, (v and hlookup, if statements, index), etc. I need Table B to cycle through columns D-F in Table A and read if there are any values, and if so, create a row in Table B that would populate the amount (Column C, Table B), and the rest of the information.

But I cannot find a way to properly do this, can anyone help? Thank you.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Date	Donor #	Festival Party	Rennovation Donations	Food Donations	International Relief Donations	Total
2	01/03/2023	1001011	Christmas		101	51	152
3	02/03/2023	1001012	Easter	201	151		352
4	03/03/2023	1001013	Easter	301	201	101	603
5
6
7
8
9
10	01/03/2023	1001011	101	Christmas	Food Donations
11	01/03/2023	1001011	51	Christmas	International Relief Donations
12	02/03/2023	1001012	201	Easter	Rennovation Donations
13	02/03/2023	1001012	151	Easter	Food Donations
14	03/03/2023	1001013	301	Easter	Rennovation Donations
15	03/03/2023	1001013	201	Easter	Food Donations
16	03/03/2023	1001013	101	Easter	International Relief Donations
17
Summary

Cell Formulas
Range	Formula
A10:E16	A10	=LET(Data,D2:F4,HSTACK(TOCOL(IF(Data<>"",A2:A4,1/0),2),TOCOL(IF(Data<>"",B2:B4,1/0),2),TOCOL(Data,1),TOCOL(IF(Data<>"",C2:C4,1/0),2),TOCOL(IF(Data<>"",D1:F1,1/0),2)))
Dynamic array formulas.

 

[metaman15]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Date	Donor #	Festival Party	Rennovation Donations	Food Donations	International Relief Donations	Total
2	01/03/2023	1001011	Christmas		101	51	152
3	02/03/2023	1001012	Easter	201	151		352
4	03/03/2023	1001013	Easter	301	201	101	603
5
6
7
8
9
10	01/03/2023	1001011	101	Christmas	Food Donations
11	01/03/2023	1001011	51	Christmas	International Relief Donations
12	02/03/2023	1001012	201	Easter	Rennovation Donations
13	02/03/2023	1001012	151	Easter	Food Donations
14	03/03/2023	1001013	301	Easter	Rennovation Donations
15	03/03/2023	1001013	201	Easter	Food Donations
16	03/03/2023	1001013	101	Easter	International Relief Donations
17
Summary

Cell Formulas
Range	Formula
A10:E16	A10	=LET(Data,D2:F4,HSTACK(TOCOL(IF(Data<>"",A2:A4,1/0),2),TOCOL(IF(Data<>"",B2:B4,1/0),2),TOCOL(Data,1),TOCOL(IF(Data<>"",C2:C4,1/0),2),TOCOL(IF(Data<>"",D1:F1,1/0),2)))
Dynamic array formulas.

Thanks for the quick response! This works for the most part, the only problem is that if I add a row of information to Table A, it doesn't automatically update Table B with the new information. Is there any way to do that?

 

[metaman15]

Thanks for the quick response! This works for the most part, the only problem is that if I add a row of information to Table A, it doesn't automatically update Table B with the new information. Is there any way to do that?

I was able to resolve this by changing the D4:F4 to F100 instead, thanks!

 

[Fluff]

You can just increase the range like

=LET(Data,D2:F9,HSTACK(TOCOL(IF(Data<>"",A2:A9,1/0),2),TOCOL(IF(Data<>"",B2:B9,1/0),2),TOCOL(Data,1),TOCOL(IF(Data<>"",C2:C9,1/0),2),TOCOL(IF(Data<>"",D1:F1,1/0),2)))

Just don't go to far.

 

[Mario_R]

Hello. A Power Query based solution can also result here:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Removed_Columns = Table.RemoveColumns(Source,{"Total"}),
    Unpivoted_Other_Columns = Table.UnpivotOtherColumns(Removed_Columns, {"Date", "Donor", "Purpose"}, "Class", "Amount"),
    Reordered_Columns = Table.ReorderColumns(Unpivoted_Other_Columns,{"Date", "Donor", "Class", "Amount", "Purpose"}),
    Changed_Type = Table.TransformColumnTypes(Reordered_Columns,{{"Date", type date}})
in
    Changed_Type

Date

Donor

Purpose

AA

BB

CC

Total

Date

Donor

Class

Amount

Purpose

24/2/2023​

1001011

Christmas

944,10​

273,15​

1.602,40​

24/2/2023​

1001011​

BB

944,1​

Christmas

27/3/2023​

1001012

Easter

479,67​

195,69​

1.302,85​

24/2/2023​

1001011​

CC

273,15​

Christmas

4/4/2023​

1001013

Other

638,73​

1.495,69​

2.334,69​

27/3/2023​

1001012​

AA

479,67​

Easter

3/2/2023​

1001014

Christmas

487,11​

574,62​

1.790,05​

27/3/2023​

1001012​

CC

195,69​

Easter

5/3/2023​

1001015

Easter

128,05​

696,17​

1.253,91​

4/4/2023​

1001013​

AA

638,73​

Other

2/3/2023​

1001016

Other

1.124,40​

925,61​

2.547,77​

4/4/2023​

1001013​

BB

1495,69​

Other

31/3/2023​

1001017

Christmas

1.222,76​

257,68​

1.937,65​

3/2/2023​

1001014​

AA

487,11​

Christmas

 

[Fluff]

I was able to resolve this by changing the D4:F4 to F100 instead, thanks!

You're welcome & thanks for the feedback.