Transpose grouped items into single column

Topic

New Reply

I am working with individual axles within axle groups on transport trucks. Each axle group has up to four individual axles, and each axle is identified individually with an ID number. Each axle has an offset that is represented in a “grouped” layout. I want to change the grouped layout into a single column as shown in the simplified, attached spreadsheet. I did the transformation manually, but need to duplicate the same function with formula. I’ve tried every combination of MATCH() and VLOOKUP() that I can think of, but no success yet. Can anybody help me solve this? Thanks.

Reply | Quote

Replies

I don’t see an attachment yet…

Reply | Quote

Ooops – overlooked the “Upload File” button.

Reply | Quote

Enter this “interesting” formula in B17 and fill down:

=INDEX($G$9:$J$13,MATCH(A17,$B$9:$B$13,1),MATCH(A17,OFFSET($B$8:$E$8,MATCH(A17,$B$9:$B$13,1),0),0))

Reply | Quote

Thanks, Hans. You’ve come thru as usual!
Now I have to dissect that formula into its components so I can deal with the other parts of the worksheet that I did not include in the simplified example.

Reply | Quote

It might be easier to use intermediate formulas – here is what I created before merging the formulas into one megaformula.

Reply | Quote