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: 17

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top