Formula to help with index/match

windiestboat

New Member
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!

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Eric W

MrExcel MVP
Maybe:

Book2
ABC
1
2Data
3BuildingUnitAmount
41A34
51B653
61C234
71D876
82E142
92A89
103B473
113A204
123B145
134A23
14
15Example Starting product
16Bldg1
17UnitA
18Amount34
19
20Bldg1
21UnitB
22Amount653
23
24Bldg1
25UnitC
26Amount234
27
28Bldg1
29UnitD
30Amount876
31
Sheet8
Cell Formulas
RangeFormula
B17,B29,B25,B21B17=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,B22B18=IF(B17="","",SUMIFS(\$C\$4:\$C\$13,\$A\$4:\$A\$13,B16,\$B\$4:\$B\$13,B17))
B20,B28,B24B20=\$B\$16

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

windiestboat

New Member
Thanks Eric. Ill give that a try later today

Replies
0
Views
83
Replies
2
Views
105
Replies
5
Views
266
Replies
5
Views
72
Replies
2
Views
56

1,130,205
Messages
5,640,837
Members
417,173
Latest member
Tuanphun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back