Transpose Data and extract text using a list of names

Hello everyone,

I need some help on transposing some data and extract a text string using a list of names. Specifically, in the sample file attached, the first sheet "Have" is the current data format. The first step is to transpose the data into sheet "Step 1". The important thing is that each new observation starts with the "Year" variable. Please note that in some cases there 6 variables per observation, while in others 7 (Special Remarks). The identifier for a new observation is "Year".

After this, I would like to separate the make of the car from its model. This is sheet "Step 2". In order to do this, I think it would be possible to use the sheet "List" which contains all makes?

Any ideas for a macro about this?

Many thanks in advance.

Try this for results on sheet "Step 2".

Please Login or Register  to view this content.

Regards Mick

Thanks Mick,

The code works fine for the sample test file, however when the makes contains more than 1 word (e.g. Aston Martin), it only inputs the first word (Aston) in the Make cell and keeps the second (Martin) in the model field. That's why I thought having a list of the Makes (sheet "List") would help. Yet, there are only a few cases with makes consisting of 2 words, so could do them manually.

Any ideas about transposing the data? this is the biggest task to achieve.

Many thanks again,

costasg

This should do

Please Login or Register  to view this content.

Many thanks jindon, it works fine!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.