Transpose/Iterate Group Label to a Row Label

Originally Posted by Leith Ross

Hello Rumble,

Welcome to the Forum!

This macro will transpose the labels based on your sample. Copy the macro into a standard VBA module in your workbook.

Please Login or Register  to view this content.

Adding the Macro (Excel 95 - 2003)

1. Copy the macro above 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. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
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.

SOLVED -- EXCELLENT - after adding the line "Dim I As Long" it worked perfect. This gives me an approach for a much more complex scenario (varied number of lines between Group name etc.). Thank you!

I have spent some time on reworking the code but have not been able to accommodate more than one column of data, what if my data has more than one column? Using the same example:

Column A is blank here...
.....A......B...........C................D............... E
1. ...... BLUE..............................................
2. .......Ball.........Kmart..............................
3. .......Pale........Target.............................
4. .......Book.......Bookstar.........................
5. ...........................................................
6. .......Subtotal3.....................................
7. ..........................................................
8. .......RED.............................................
9. .......Sign......BannerInc........................
10. ......Cup......Safeway..........................
11. ......Door.....Lowes.............................
12. ......Car.......CarMax...........................
13. ......Poster...SportsInc........................
14. ........................................................
15. ..... Subtotal5....................................

To now have my data have a 1-to-1 association and remove the grouping to perform pivots and other preferred ways to use the data, the final output would look like this essentially removing the grouping and label each row with the previous group value:

Column A would then become the Group Value
.....A..........B.............C.................D..................E
1. BLUE....Ball......Kmart
2. BLUE....Pale.....Target
3. BLUE....Book....Bookstar
4. RED.....Sign......BannerInc
5. RED.....Cup......Safeway
6. RED.....Door......Lowes
7. RED.....Car........CarMax
8. RED.....Poster....SportsInc

Thank you so much for the last quick response!