Aligning data from Text and Adjacent Cells into columns - Best way to achieve this?

[jacketpotato]

Hello Experts,
Please see the attached. I am trying to align the data in this in the best way possible, that will be able (hopefully) to automatically do this for ongoing data which is automatically generated into this spreadsheet. I've so far been trying to use IFERROR, MATCH, VLOOKUP...but having had no experience of those functions previously I think I'm veering off track, and going to end up using a long winded solution if I do keep trying to learn this myself.

So..
For example '0 secs' and 'p&l' always contain their values in the adjacent cells. However they are not always in the same columns, which is making analysis and particularly PivotTables very difficult to use without manual manipulation beforehand.
There is a lot I'd like to do, but if you could help me even pull out one of the quoted examples and align into a new column that would probably teach me how to do the rest of the changes myself.

So - for the example - I have freed up columns A and B. Could you please show me the best way for these new blank columns to contain '0 secs' (into A) and it's result (into B) - the result is every adjacent (first cell to the right) of '0 secs'? In the same way I have manually done in pic2.

For the purpose of any formulas, the data I'll be doing this is ongoing (currently just over 2000 rows, and has a limit of perhaps the first 40/50 columns, currently as far as AK but if the searched range could just be 'All data' or the entire Spreadsheet that would be fine.

Really appreciate any help.

 

[jacketpotato]

Please close thread

I have successfully managed using the INDEX MATCH and OFFSET rules.

 

[Peter_SSs]

Welcome to the MrExcel board!

I have successfully managed using the INDEX MATCH and OFFSET rules.

Given that OFFSET is a volatile function and it sounds like you might end up with a lot of these formulas, you may find your worksheet becoming sluggish.

I think that the following non-volatile formulas should do the job for you.

In A1 & down:

=IF(ISNUMBER(MATCH("0 secs",C1:ZZ1,0)),"0 secs","")

In B1 & down:

=IF(A1="","",INDEX(C1:ZZ1,MATCH(A1,C1:ZZ1,0)+1))

BTW, we do not close threads in this forum as you never know when another/better solution might come along. However, if a solution suits you, you can mark it as the solution - see my signature block below for more details.

 

[jacketpotato]

Thank you so much this has worked perfectly!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.