Need multiple columns converted into pairs, please help, thanks!

Ok, so I have data that looks like this (spaces being column breaks):

FirmA FirmB FirmC FirmD
FirmE FirmF
FirmG FirmH FirmI
FirmJ FirmK FirmL FirmM FirmN
etc.

And I need it to look like this:
FirmA FirmB
FirmA FirmC
FirmA FirmD
FirmE FirmF
FirmG FirmH
FirmG FirmI
FirmJ FirmK
FirmJ FirmL
FirmJ FirmM
FirmJ FirmN
etc.

Basically in a row where there's just a pair, it leaves it alone. In a row with more than a pair (3 or more columns), it makes a row for each pair with the first firm in the original row always the first column, and subsequent firms as the second.

I also need all this another way, too. Back to the example (simplified, don't want it as long):

FirmA FirmB FirmC FirmD
FirmE FirmF
FirmG FirmH FirmI
etc.

I also need a separate version (i.e. another macro) to make it look like this:

FirmA FirmB
FirmA FirmC
FirmA FirmD
FirmB FirmC
FirmB FirmD
FirmC FirmD
FirmE FirmF
FirmG FirmH
FirmG FirmI
FirmH FirmI
etc.

(Essentially just converting every row into all possible pairs).

I'm sure there's a fairly straightforward approach here but I'm terrible at programming Excel... any help? I'd obviously like it as a macro if possible. It'd be hugely appreciated. I'm using a dataset with something like 8 thousand rows (and need to do it 5 different times), so you can see why I'd like this automated.

Thanks so much!

--Craig

aphex242,

Welcome to the Excel Forum.

With FirmA in worksheets Sheet1, cell A1:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

aphex242,

You are very welcome. Glad I could help.

Thanks for the feedback.

I updated my original post with a second request as well. If you have more time, please take a look, I think it's potentially trickier.

Yes it was trickier....


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.

aphex242,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.