IF formula true THEN move to next row

[zepelin]

I have a table with 131 columns and 4000 rows. I need in a different sheet to make a tabel with 3 raws:

original tabel has row A as an name(internal number composed of leters and/or digits) and the rest of rows are warehouse names; under warehouse names are numbers .

internal number warehouse1 warehouse2 warehouse3
abc 1 0 1
def 0 2 0

Using the example above I need to make a new table(in a new sheet) that will collect the data like this:

Internal number Location Sales
abc ware1 1
abc ware3 1
def ware2 2

I tried a lot of formulas but none did help me prepare what i want. Anyone got an ideea? I attached an bigger example so anyone who tries to help me can do that in that table.

[mike7952]

here' one way

Please Login or Register  to view this content.

[Kevin UK]

Hi zepelin

Will the INDEX & MATCH achieve those results! In E6 & copy down, adjust the ranges to suit your needs.

Please Login or Register  to view this content.

Kevin

[zepelin]

your formula is what i need.
how can i bring the new data to a new sheet? so that my first table remains untouched

[zepelin]

this is the table

[mike7952]

This should do it

Please Login or Register  to view this content.

[zepelin]

Great work.

Thank you