Transpose Columns with Conditions

Hi All,

I have been asked to transform data we receive as a list (Output in attached) into something more representative of that shown in the result sheet also on the attached.

In short this is basically taking the numbers beginning with 81 and making those the column headers and the then the corresponding numbers below being listed in this new column. The amount of numbers involved can vary massively from 1 upwards to there is no pattern here. The only consistent factor will be that the 81 numbers will be there albeit again they could be in a different order or sequence each time the data is presented.

Ideally I would like to take the raw data (output) each day it is produced and in turn end up with the result without having to make any additional amendments / manipulations to however the result can be calculated,

Many thanks,
Gaz

Hi
try this macro

Please Login or Register  to view this content.

Originally Posted by Gazsim

Hi All,

I have been asked to transform data we receive as a list (Output in attached) into something more representative of that shown in the result sheet also on the attached.

In short this is basically taking the numbers beginning with 81 and making those the column headers and the then the corresponding numbers below being listed in this new column. The amount of numbers involved can vary massively from 1 upwards to there is no pattern here. The only consistent factor will be that the 81 numbers will be there albeit again they could be in a different order or sequence each time the data is presented.

Ideally I would like to take the raw data (output) each day it is produced and in turn end up with the result without having to make any additional amendments / manipulations to however the result can be calculated,

Many thanks,
Gaz

Hi,

This can be done with the help of Macro. In case you do not want to go with VBA option, array formula may come handy.
I have implemented array formula in the sheet attached herewith. Kindly go through it.
100 rows have been considered for the array formula. In case you have more data, simply modify the range.

---

Shashank

Hi Nicky,

Many thanks - that does indeed work although I have noted that it does not overwrite existing data on the results if there are less lines of data under that particular 81 ref if you follow when you change / update it?

For example if I on my first set of data had three references under 81-14-022-04 and the next day this only became one, the previous additional two references would remain when they need to be deleted / cleared,

Hope this makes sense?

Thanks,
Gaz

Hi Gaz
if you want to clear the contents of the results sheet before copying the values then add this code

Please Login or Register  to view this content.

above the line starting "do while ... "

Hi All,

Many thanks for all your replies so far - I am currently looking at each individually to see which will best fit our requirements.

However, one final question. Once I have the data in result I then need to cross reference this data with further data to confirm which references we have a location (or match) on. I have created a separate 'Data' sheet on the attached file which I hope gives an indication of what is required but the list of data will contain somewhere in the region of 500,000+ lines ultimately to cross check against, the majority of which would have no match.

Many thanks,
Gaz