Formula to help with index/match

windiestboat

New Member
Joined
Jul 2, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. MacOS
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):
excel.png


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!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,752
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,563
Messages
5,625,528
Members
416,116
Latest member
Joemamasuka

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top