Formula to fill up or down when IF condition met

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello! Could really use some help with a formula. What I have is a workbook for warehouse product and what location they're assigned to in my warehouse.

My workbook lists every location and operates essentially as binary with a YES or NO as to whether the space is occupied, and if YES, it also the product assigned to that particular location.

My problem is that when locations are empty I need to account for how many pallets can fit in that location. Some product can be stacked higher than others, but if there's no product in a location, there's no data to tell me the maximum stack height - which is where I need help.

If a location is empty I need to assume the product in that location will be the same as the previous location, thus having the same stack height, so I need the Pallet Height to fill down until the next occupied location. However, if the aisle changes (the blue highlight), and the start of the new aisle is empty, I need the next occupied location to fill backwards. Typically a new aisle is where new product starts, so I don't want assumed product heights from the previous aisle (in this case Aisle Y2) flowing over into a new aisle (Z1). Z1 needs to backfill the product height from the first occupied location.

Hope that makes sense! I've been playing with this all day and it's driving me crazy. ANY help would immensely appreciated.

Thank you!!
 

Attachments

  • Screenshot.PNG
    Screenshot.PNG
    24 KB · Views: 12

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

Was just lookin at some older and not answered threads.
Don't know if you're still lokking for an answer but if so, take a look at this and se if this helps.

Book2
AIJ
1LocAdj Plt HghtFormula
2Y2_3811
3Y2_391
4Y2_401
5Y2_411
6Y2_421
7Y2_431
8Y2_441
9Y2_451
10Y2_461
11Y2_471
12Y2_481
13Y2_491
14Y2_501
15Y2_511
16Y2_521
17Z1_14
18Z1_24
19Z1_34
20Z1_44
21Z1_54
22Z1_64
23Z1_74
24Z1_844
25Z1_94
Sheet1
Cell Formulas
RangeFormula
J2:J25J2=INDEX($I$2:$I$25,MATCH(TRUE(),IF(LEFT($A$2:$A$25,2)=LEFT($A2,2),$I$2:$I$25,0)>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I totally forgot about this! I do actually have several places I can put it to work, however, my sheet is 53,000+ lines. Any chance there's a quicker non-array option?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,121
Messages
5,570,309
Members
412,318
Latest member
angoeyuan
Top