Transpose multiple cell groups of varying lengths

Hello!

I have a messy data problem I'm hoping for some help with. For a data analysis project, I've been scraping basic facebook user data resulting from a search (I'm aware of terms of service issues, and unfortunately the API won't cut it for what I'm doing). When the data gets dumped into excel it looks like this:

Picture1.png

And I'd like to get it transposed and sorted so it looks like this:

Picture2.png

I need each group of data to be transposed to rows, and because there are often several thousand groups of cells, I'd like to be able to do this without manually transposing each group. I've had success writing a macro that automatically copies and pastes>transpose each group, but I still have to highlight each group manually before pressing the hotkey for the macro, which means that it takes a couple hours, a red bull and hand cramps to transpose a longer list. Here's the really basic macro for transposing groups individually:

Please Login or Register  to view this content.

The real sticking point here is that the length of the groups of data/cells is variable. Sometimes there are four pieces of information in the group, sometimes three, sometimes two (excluding the "Add FriendMore Options" data which is, of course, removed). I've run this by a few excel wizards and they're stumped. I'd like to be able to tell the macro to find the next blank cell in column A, then highlight until the following blank cell, then perform the previously mentioned transpose function, but some serious googling hasn't resulted in any answers on how I might do this.

I'm open to other solutions to this problem too - it doesn't have to be a macro. Although I'll go ahead and note that the obvious scrape software answers to this (i.e. iMacros, Data Miner, etc.) aren't helpful due to some browser memory issues involved in scrolling to the bottom of FB results. But any help or ideas would be greatly appreciated!

Thanks,
Mike

Hi Mike

i will look at this for you.

Unfortunately i cannot reply until you comply with rule 3.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Sorry Mike

Post One needs to be amended to include Code Tags.

The moderators insist on them.

I have your macro ready,

One way

Please Login or Register  to view this content.

Mikes Solution is a lot neater than mine.

Well done Mike.

Please Login or Register  to view this content.