Transpose Blocks of Data

Hello and good morning.

I have a spreadsheet which is basically a long list of records in column A. It is pretty much blocks of data. The blocks are separated by a single blank row. Each block contains either 7 or 8 lines. It varies throughout.

What I want to do is somehow do a transpose for each block of data. For example the last line in each block contains the word "Product Code" so it would be logical to tell Excel to transpose everything between the blank row, and the row which contains the word "Product Code".


Someone suggested I use a macro to transpose every Nth row but that won't work since the blocks are not uniform in the amount of rows they have, thus throwing a wrench in the gears.

Can someone help me out with this? Thank you.

Hi and welcome to the forum
The problem yoou described can easily be solved with a simple vba code.
Before writing the code, I need to know where to you want to paste the copied blocks.

Cheers,

Hi Mayda89. Sorry to waste your time. I am still in the process of organizing my data. I should have waited before asking. I think I may have found a way to figure it out. I'll be back. Thanks.

Ok I'm back. So basically what I need is this. I have thousands of blocks that look like this:

Price: $6.50
Package (6) $39.00
Scale: S-M-L-XL
Ratio: 1-2-2-1
Code: Z119-1139S-PEACH-1-2-2-1

However some blocks are missing some data. They may be only 2 or 3 or 4 rows long and look like this:

Price: $6.50
Package (6) $39.00
Code: Z119-1139S-PEACH-1-2-2-1


I need a script that will take each block and transpose it into a row into the appropriate column. Like so:

PRICE | PACKAGE | SCALE | RATIO | CODE


I think one way to do this would be to have the script recognize the text value in the cell. Such as, the script will see that some cell has the word "Price" in it, and therefore would put it in the PRICE column. And another cell has the word "Scale" in it, and it would place it in the SCALE column, and it would continue transposing as such until it reaches the end.

No problem

But I still haven't got the answer to my question?

Well I would like them pasted across the adjacent columns. Really the pasting I'm not too worried about because I can always rearrange the columns. So I guess if I have this in Column A:

Price: $6.50
Package (6) $39.00
Scale: S-M-L-XL
Ratio: 1-2-2-1
Code: Z119-1139S-PEACH-1-2-2-1

Then we can paste it across column B, C, D, E, F.

Once all the blocks are transposed across those columns I can simply delete column A and have all the data nicely fitted across the row and have a proper spreadsheet.

I think it would have been easier for you to have downloaded the sample, so that no only we can see the format of the data, but also use it to test a code.

Oh I'm sorry. Yeah you're right. Here is the spreadsheet I am talking about.

Let's see if this is what you wanted

I used "Price as an identifier.
Try it. It is a bit slow as it to loop through 1500rows

Please Login or Register  to view this content.