Formula to help with index/match

[windiestboat]

Hello All,

I have an example of a problem i am encountering.

Imagine the below data (a simplified example of the data im working with):

I am wondering if there is a function or macro that I can put together which would input the units for a selected bldg on a different line. so if i selectd bldg one and wanted to see the amounts for each unit it would pull a-d into a successive line and i could then use an index match function for the amount data. this would need to be variable though as say bld 4, 3, and 2 all have less units than bldg 1. I cannot use a pivot table here. Wondering if there is a macro that would take the count of the units for each bldg and insert on a new line based on duplicates? the number of line items is variable - so an example end product will show 1 to 4 unit/amt line items - have a macro that automatically adjusts to the number of units by copying rows 16-18 in the example product and inserting them below...

any help or ideas is much appreciated. Thank you guys!

 

[Eric W]

Maybe:

Book2
	A	B	C
1
2		Data
3	Building	Unit	Amount
4	1	A	34
5	1	B	653
6	1	C	234
7	1	D	876
8	2	E	142
9	2	A	89
10	3	B	473
11	3	A	204
12	3	B	145
13	4	A	23
14
15		Example Starting product
16	Bldg	1
17	Unit	A
18	Amount	34
19
20	Bldg	1
21	Unit	B
22	Amount	653
23
24	Bldg	1
25	Unit	C
26	Amount	234
27
28	Bldg	1
29	Unit	D
30	Amount	876
31
Sheet8

Cell Formulas
Range	Formula
B17,B29,B25,B21	B17	=IFERROR(INDEX($B$4:$B$13,AGGREGATE(15,6,(ROW($B$4:$B$13)-ROW($B$4)+1)/($A$4:$A$13=B16),(ROW(B17)-ROW(B$17))/4+1)),"")
B18,B30,B26,B22	B18	=IF(B17="","",SUMIFS($C$4:$C$13,$A$4:$A$13,B16,$B$4:$B$13,B17))
B20,B28,B24	B20	=$B$16

Just put the building number in B16, and the formulas will fill in the rest.

 

[windiestboat]

Thanks Eric. Ill give that a try later today