Transpose wont work - too much data

Hi There,

I asked this question a while ago but I don't think I explain myself very well! Here's the information I have:

COLUMN A COLUMN B

RunspaceId Data 1
Cost Data 2
ADCost Data 3
ExchangeCost Data 4
MaxMessageSize Data 5
Sites Data 6
AdminDisplayName Data 7
ExchangeVersion Data 8
Name Data 9
DistinguishedName Data 10
Identity Data 11
Guid Data 12
ObjectCategory Data 13
ObjectClass Data 14
WhenChanged Data 15
WhenCreated Data 16
WhenChangedUTC Data 17
WhenCreatedUTC Data 18
OrganizationId Data 19
OriginatingServer Data 20
IsValid Data 21

RunspaceId Data 1
Cost Data 2
ADCost Data 3
ExchangeCost Data 4
MaxMessageSize Data 5
Sites Data 6
AdminDisplayName Data 7
ExchangeVersion Data 8
Name Data 9
DistinguishedName Data 10
Identity Data 11
Guid Data 12
ObjectCategory Data 13
ObjectClass Data 14
WhenChanged Data 15
WhenCreated Data 16
WhenChangedUTC Data 17
WhenCreatedUTC Data 18
OrganizationId Data 19
OriginatingServer Data 20
IsValid Data 21

This repears upto 200 times and I have lots of these outputs with the value changing in column B but the format is essenially the same.

I'm trying to work out how I can take Column A, make the data in there the title of each colum and then populate the relivant colum with the correct data based on it's entry next to it (in the above example column b.

I'd like something like this in the end (I've removed some to the columns for ease of copy/paste)

RunspaceId Cost ADCost ExchangeCost MaxMessageSize
Data 1 Data 2 Data 3 Data 4 Data 5

I can edit each script with the relivant column entrys that it should look for. Transpose wont work due to the sheer amount of data - it all ends up on two lines

Any help would be appreciated - I'm looking at doing this manually at the moment and I have over 20,000 sections to work though!!

Best regards, Wilbbleman

Your profile indicates you are using 2003 this version only has 256 columns, therefore that is the maximum rows you can transpose.

You are asking for some 20x200 i.e. 8000 columns, for that you will need to use 2007 or above.

Perhaps I'm misunderstanding you are you asking for a VBa solution to return 20 columns of data?

Hope this helps

It's really easy to get this wrong, can you make it absolutely clear by posting a workbook with an excerpt of 5-10 of these "sections" on sheet1, then sheet2 showing the exact desired output from that sample set.

Thanks.

Marcol, I think the RunSpaceIDs would all go in the same column, etc. See, it's easy to get this wrong, sample wb will really help.

This will do it:

Please Login or Register  to view this content.

SpecialCells is limited to about 8500 "sections" at a time, so you may need to break that data up into 3 groups, run the transpose on each group, then put them together.

Actually, this version might not run into the limitations you might hit with SpecialCells:

Please Login or Register  to view this content.

Here's the second one in your workbook, working for me.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.