How to batch transpose groups to columns when groups take up various rows

0

Hi! I'm currently using the macro "Transpose every X number of rows" to batch-transpose some data. 

However, the data I'm putting in Excel to then tranpose sometimes takes up 4 rows, sometimes 5, etc. As I'm currently inputting it in the Excel sheet, I have a blank row between every data "group." Is there any way to use a macro to transpose every GROUP of rows between blank lines (as opposed to by the number of rows)?

It would be okay if they got transposed to columns that didn't necessarily line up... I just would like to get the whole sheet laid out horizontally and then sort into the correct columns. 

Here's an example of what my data looks like:

Title
Sub-title
Year
Value

Title
Year
Value

Title
Sub-Title
Location
Year
Value

Any thoughts?

Answer
Discuss

Answers

0
Selected Answer

If you want to continue thinking in terms of transposing data I suggest you just insert missing rows in the original data so that all groups have the same number of rows. Remember that you probably have to do that from the bottom up because row numbers will change as you insert rows above the current row.

As an alternative you might think of transferring data. In that method you would pick the data you want from each group and copy/paste them to their correct column in a destination worksheet.

Either way, the key is to be able to identify the data. In your representative data that is simple. "Year" is year and "Value" is value. In your real data row titles might vary. So you would need to find a genereic way of identification. Perhaps the last row in a group is always "Value". You don't say which data you need. Perhaps you want some of the titles as well.

So, my answer is that the method you employ, transpose or transfer, is a question of how you manage to identfy the rows - and how many groups you have. As a rule of thumb, the transposed data probably need additional work even after they have been sorted into columns. A transfer is more likely to give you a final result immedaitely. Therefore you might be willing to invest more time in such a solution.

Discuss

Discussion

I like the idea of transferring (it's what I would prefer to do because, I agree with you, it sounds quicker!). The issue is that my data doesn't have identifiers, per se. There are differences in font (titles are in bold, sub-titles in all caps, and then some data that is labeled like, "Year: 2010"), but it's not like row 1 has "Title: UChicago," row 2, "Value: 4," etc. Also the amount of information available from one data group to the next is inconsistent (hence my issue with the number of rows in the first place)--which is why I really was looking for something simple in Excel that will indicate GROUP OVER and NEXT GROUP START (like a blank line). Can you think of anything that would do that?

I sort of like your idea of adding blank lines, though...

Update: I'm actually using the "insert line" strategy, and it's working really well! I think I'm going to soldier on with that for now. Thank you!
transposeq (rep: 2) Aug 11, '20 at 9:17 pm
Great! Thanks for the feedback. 
Variatus (rep: 4729) Aug 11, '20 at 10:05 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login