Bring value from row on column

[Amber12]

Not sure how to explain but I will attach here how raw data looks like (the left columns A:B) and what I am trying to accomplish (to the right columns D:F). How can I populate the value for warehouse? I am trying to build a macro and the position of warehouse value will change from period to period. I can't think of how I can do it.

[mcmahobt]

For both formulas below, enter as ARRAY formulas with CTRL+SHIFT+ENTER and copy down to fill:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

For F2, more information is needed as to how to determine which Warehouse the record should display.

[Amber12]

This is where my problem is, with the warehouse. The report pretty much displays total per warehouse, item group and account. So the first set will be for the first warehouse listed, in this case 200. The next set of data is for the following warehouse, in this case 251 and so on. I tried to use Go Special but didn't quite work. I am not too good with array formulas...but if you can think of one that works, I would be happy to use it.

Thank you

[mcmahobt]

That makes more sense, but the issue would then fall under Warehous 251 only applying to one record in your dataset. How am I (or the formula) supposed to know that Warehous 251 applies to three records (columns D:F) vice just one (columns A:B)?

To try and elaborate, can you explain verbally (or through text) how I would know to apply a Warehous number to a record?

[Amber12]

There is no set number of records that the warehouse applies to. Each month the number of item groups in a warehouse vary. But for each item within a warehouse I need to assign the warehouse's number.
What I tried was create another column where I copied column B and filter out the transfer price variance and item group. Then I tried to use GoSpecial to fill the blanks with the text below. For some reason is not working well.
I used GoSpecail in the past to fill blanks with values from above and worked perfectly. This is the first time when I tried with items from below. That's about all I could think of.

[mcmahobt]

I'm still not sure if I follow, because by what you're describing, you're not sure how the Warehouse number is generated/applied either. If there is a systematic/formulaic method that dictates what Warehouse number is used, we could go off that. But without an understanding of how the Warehouse numbers are tied back to its respective record, and without the raw data reflecting any sort of pattern, I can't provide more information.