HELP-Rearranging data into columns

S4squ4tch · Oct 7, 2015

Good Morning All,

So I have a lot of data in rows that I need to rearrange and organize into columns. Transpose doesn't quite fulfill my needs and was hoping to find a way to write a formula that will capture what I am trying to do. I will include an example of what I am looking for below.

So here is what I have right now:

ID#11111	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11111	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11112	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11112	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11113	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11113	Product Name	Manufacturer Name	Supplier Name	Distributor Name

What I am trying to do

Product Name	ID#11111
Manufacturer Name
Supplier Name
Distributor Name
Product Name	ID#11112
Manufacturer Name
Supplier Name
Distributor Name
Product Name	ID#11113
Manufacturer Name
Supplier Name
Distributor Name

Any and all help and suggestions would be GREATLY appreciated!

Thanks, all!

S4squ4tch · Oct 7, 2015

Bump...anyone?

Rick Rothstein · Oct 7, 2015

You did not say where to put that rearranged table, so I put it to the right of your existing table starting in cell H1...

Code:

Sub RearrangeData()
  Dim X As Long
  For X = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Cells(4 * X - 3, "I") = Cells(X, "A")
    Cells(4 * X - 3, "H").Resize(4) = Application.Transpose(Cells(X, "B").Resize(, 4))
  Next
  Columns("H:I").AutoFit
End Sub

S4squ4tch · Oct 7, 2015

It would be on a separate sheet.

Osvaldo Palmeiro · Oct 7, 2015

Maybe:

Code:

Sub RearrangeData()
 Dim pn As Long, k As Long
  With Sheets("Sheet1")
   For pn = 1 To .Cells(Rows.Count, "B").End(xlUp).Row Step 2
    Sheets("Sheet2").Cells(k + 1, 1) = .Cells(pn, 2)
    Sheets("Sheet2").Cells(k + 1, 2) = .Cells(pn, 1)
    Sheets("Sheet2").Cells(k + 2, 1).Resize(3) = Application.Transpose(.Cells(pn, 3).Resize(, 3))
    k = k + 4
   Next pn
  End With
End Sub

Rick Rothstein · Oct 7, 2015

S4squ4tch said:
It would be on a separate sheet.

Okay, here is my code modified to output the rearranged data to a separate sheet (change the red text if I guessed wrong as to the name of that separate sheet)...

Code:

Sub RearrangeData()
  Dim X As Long, WS As Worksheet
  Set WS = Sheets("[B][COLOR="#FF0000"]Sheet6[/COLOR][/B]")
  For X = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    WS.Cells(4 * X - 3, "B") = Cells(X, "A")
    WS.Cells(4 * X - 3, "A").Resize(4) = Application.Transpose(Cells(X, "B").Resize(, 4))
  Next
  WS.Columns("A:B").AutoFit
End Sub

S4squ4tch · Oct 7, 2015

Thanks all, I need to play around with the vba so I can use my actual data as opposed to the example and see if I can get this to work.

Rick Rothstein · Oct 7, 2015

S4squ4tch said:
Thanks all, I need to play around with the vba so I can use my actual data as opposed to the example and see if I can get this to work.

Why don't you tell us how your data is really arranged and where it is located and where you want the output to go and then Osvaldo and I can modify our codes to do what you need directly.

S4squ4tch · Oct 7, 2015

Hi Rick,

I truly appreciate all of your and Ovaldo's help. I love learning new ways to do things in excel. Hopefully this helps clear some things up.

WHAT I HAVE NOW:

ID

ID Name

Employee Name

Employee ID

XXX

Manager ID

Manager Name

Manager ID

Manager Name

Manager ID

Manager Name

Manager ID

Manager Name

Manager ID

Manager Name

Manager ID

Manager Name

Manager ID

Manager Name

Manager ID

Manager Name

001

Toys

Joe Smith

100

XXX

901

Chuck

902

Bob

903

Adam

904

Steve

905

Luke

906

Bill

907

Charlotte

908

Sue

So I have a table with 23 columns. I only need to take the values from columns 8-23 (H-W) and place them in column 3 and 4 (C and D).

WHAT I NEED (WITHOUT MANUALLY COPY AND PASTE): I need to take the Manager ID'S in each row, along with the corresponding Manager Name, and place under the employee name and ID.

ID	ID Name	Employee Name	Employee ID	XXX	XXX	XXX
001	Toys	Joe Smith	100	XXX	XXX	XXX
		Chuck	901
		Bob	902
		Adam	903
		Steve	904
		Luke	905
		Bill	906
		Charlotte	907
		Sue	908

I need to do this for hundreds of rows of information.

Thanks again for all of your help!

Rick Rothstein · Oct 7, 2015

S4squ4tch said:
Hi Rick,

I truly appreciate all of your and Ovaldo's help. I love learning new ways to do things in excel. Hopefully this helps clear some things up.

WHAT I HAVE NOW:

ID ID Name Employee Name Employee ID XXX XXX XXX Manager ID Manager Name Manager ID Manager Name Manager ID Manager Name Manager ID Manager Name Manager ID Manager Name Manager ID Manager Name Manager ID Manager Name Manager ID Manager Name
001 Toys Joe Smith 100 XXX XXX XXX 901 Chuck 902 Bob 903 Adam 904 Steve 905 Luke 906 Bill 907 Charlotte 908 Sue

<tbody>
</tbody>
So I have a table with 23 columns. I only need to take the values from columns 8-23 (H-W) and place them in column 3 and 4 (C and D).

WHAT I NEED (WITHOUT MANUALLY COPY AND PASTE): I need to take the Manager ID'S in each row, along with the corresponding Manager Name, and place under the employee name and ID.

ID ID Name Employee Name Employee ID XXX XXX XXX
001 Toys Joe Smith 100 XXX XXX XXX
Chuck 901
Bob 902
Adam 903
Steve 904
Luke 905
Bill 906
Charlotte 907
Sue 908

<tbody>
</tbody>

I need to do this for hundreds of rows of information.

That data is completely different than what you showed us in Message #1... I am not sure how you thought you would get the answer you need by misrepresenting your data like that. For future questions you might ask, please do not simplify your data for us... that is almost always a bad thing to do... usually it results in you getting great answers to a problem you really don't have nor care about and which usually has you coming back to expand the question to what you should have posted for us in the first place. If I get time later, I'll come back to your question and, if no one has answered it at that point, see if I can develop completely new code for your completely different question.

HELP-Rearranging data into columns

S4squ4tch

New Member

Excel Facts

S4squ4tch

New Member

Rick Rothstein

MrExcel MVP

S4squ4tch

New Member

Osvaldo Palmeiro

Well-known Member

Rick Rothstein

MrExcel MVP

S4squ4tch

New Member

Rick Rothstein

MrExcel MVP

S4squ4tch

New Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

HELP-Rearranging data into columns

New Member

Excel Facts

New Member

MrExcel MVP

New Member

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock