Multiple Rows to One Row with Multiple Columns

[Alysha]

I'm trying to manipulate a very large file into a new format so I can work with it in Visio.

For context, it's a succession planning file, where each current incumbent has their own row, but with each successor they add a row. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Current Incumbent[/TD]
[TD]Successor[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Bob Smith[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Robert Jones[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Bobby Brown[/TD]
[/TR]
</tbody>[/TABLE]

However, what I'd like is to have one row for Jane and then multiple columns for the Successors:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Current Incumbent[/TD]
[TD]Successor 1[/TD]
[TD]Successor 2[/TD]
[TD]Successor 3[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Bob Smith[/TD]
[TD]Robert Jones[/TD]
[TD]Bobby Brown[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone have any suggestions on how to do this aside from manually copying and pasting? The file has thousands of rows, so any way to automate this would be a HUGE time saver!

TIA!

 

[hiker95]

Alysha,

Welcome to the MrExcel forum.

Here s a macro solution for you to consider, that will adjust to the number of raw data rows, and, to the number of columns needed for the results.

With your raw data in columns A, and, B, then the results will be written beginning in column D.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	Current Incumbent	Successor
2	Jane Doe	Bob Smith
3	Jane Doe	Robert Jones
4	Jane Doe	Bobby Brown
5
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	Current Incumbent	Successor		Current Incumbent	Successor 1	Successor 2	Successor 3
2	Jane Doe	Bob Smith		Jane Doe	Bob Smith	Robert Jones	Bobby Brown
3	Jane Doe	Robert Jones
4	Jane Doe	Bobby Brown
5
Sheet1

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReroganizeData()
' hiker95, 07/26/2017, ME1016132
' Thank you MickG
Dim Dic As Object, Ray As Variant, k As Variant, G As Variant
Dim Rw As Long, Ac As Long, c As Long, a As Long, oMax As Long
Ray = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
For Rw = 1 To UBound(Ray, 1)
  For Ac = 2 To UBound(Ray, 2)
    If Not Dic.Exists(Ray(Rw, 1)) Then
      Set Dic(Ray(Rw, 1)) = CreateObject("Scripting.Dictionary")
    End If
    Dic(Ray(Rw, 1))(Ray(Rw, Ac)) = Empty
  Next Ac
Next Rw
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 1) * UBound(Ray, 2))
For Each k In Dic.Keys
  c = c + 1: a = 1
  nray(c, 1) = k
  For Each G In Dic(k)
    a = a + 1
    nray(c, a) = G
    oMax = Application.Max(oMax, a)
  Next G
Next k
Range("D1").Value = Range("A1").Value
With Range("D2").Resize(c, oMax)
  .Value = nray
  Columns(4).Resize(, oMax).AutoFit
End With
With Range("E1").Resize(, oMax - 1)
  .Formula = "=""Successor "" & Column() - 4"
  .Value = .Value
End With
Columns.AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReroganizeData macro.

 

[hiker95]

I'm trying to manipulate a very large file into a new format so I can work with it in Visio.

Alysha,

1. What is your raw data worksheet name?

2. If you want the results to be written to a different/new worksheet, then, let me know what the worksheet name should be, and, I will adjust the macro accordingly?

 

[Mike Szczesny]

Can you use something like this? Place your formula in the cell below Successor1. You need to enter Cntrl+Shift+Enter. Then copy accross. I enclosed an =iferror(formul," ") to remove any error messages after you get all your results. The formula is [TABLE="width: 89"]
<tbody>[TR]
[TD="width: 89"] =INDEX($B$2:$B$4,SMALL(IF($A$2:$A$4=$A$7,ROW($B$2:$B$4)-ROW($B$2)+1),COLUMNS($B$7:B7)))

[TABLE="width: 339"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Incumbent[/TD]
[TD]Successor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Rob Smith[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Robert Jones[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Bobby Brown[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incumbent[/TD]
[TD]Successor1[/TD]
[TD]Successor2[/TD]
[TD]Succesor3[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Rob Smith[/TD]
[TD]Robert Jones[/TD]
[TD]Bobby Brown[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]