Reorganising Data

  • Thread starter Thread starter Dolphy
  • Start date Start date
D

Dolphy

Hi All,

Is there a way to have data transploted from a Vertical to
horizointal?

I've looked a Paste Spoecial command but I have over 20K of rows to
sort;

Sample Data:

04xxxxxxxx Smog
04xxxxxxxx alto
04xxxxxxxx wagon
04xxxxxxxx carriage
04xxxxxxxx beeer
04xxxxxxxx wine
04xxxxxxxx cola
04xxxxxxxx bourbon
04yyyyyyyy Smog
04yyyyyyyy alto
04yyyyyyyy wagon
04yyyyyyyy carriage
04yyyyyyyy beeer
04yyyyyyyy wine
04yyyyyyyy bourbon
04zzzzzzzz Smog
04zzzzzzzz alto
04zzzzzzzz wagon
04zzzzzzzz carriage
04zzzzzzzz beeer
04zzzzzzzz wine
04zzzzzzzz bourbon
04zzzzzzzz magic
04zzzzzzzz zulu
04zzzzzzzz tango

And I want to output the data as:

04xxxxxxxx Smog alto wagon carriage beeer wine cola bourbon
04yyyyyyyy Smog alto wagon carriage beeer wine bourbon
04zzzzzzzz Smog alto wagon carriage beeer wine bourbon magic zulu
tango

Is there a macro or formula that can be used?

Rgds,
Dolphy
 
Hi All,

Is there a way to have data transploted from a Vertical to
horizointal?

I've looked a Paste Spoecial command but I have over 20K of rows to
sort;

Sample Data:

04xxxxxxxx Smog
04xxxxxxxx alto
04xxxxxxxx wagon
04xxxxxxxx carriage
04xxxxxxxx beeer
04xxxxxxxx wine
04xxxxxxxx cola
04xxxxxxxx bourbon
04yyyyyyyy Smog
04yyyyyyyy alto
04yyyyyyyy wagon
04yyyyyyyy carriage
04yyyyyyyy beeer
04yyyyyyyy wine
04yyyyyyyy bourbon
04zzzzzzzz Smog
04zzzzzzzz alto
04zzzzzzzz wagon
04zzzzzzzz carriage
04zzzzzzzz beeer
04zzzzzzzz wine
04zzzzzzzz bourbon
04zzzzzzzz magic
04zzzzzzzz zulu
04zzzzzzzz tango

And I want to output the data as:

04xxxxxxxx Smog alto wagon carriage beeer wine cola bourbon
04yyyyyyyy Smog alto wagon carriage beeer wine bourbon
04zzzzzzzz Smog alto wagon carriage beeer wine bourbon magic zulu
tango

Is there a macro or formula that can be used?

Rgds,
Dolphy
Click to expand...

Quick and dirty. This is only going based off of your example data
where you want to consolidate column B value into one row if they
match on column A. Be sure to run this on a copy of your data to be
sure that you get the desired results.
Sub consil()
Dim r As Long, bRow As Long
bRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To bRow
If IsEmpty(Cells(r, 1)) Then Exit For
If Cells(r, 1).Text = Cells(r, 1).Offset(1, 0).Text Then
Cells(r, Range("IV" & r).End(xlToLeft).Offset(0,
1).Column) = _
Cells(r, 1).Offset(1, 1).Text
Cells(r, 1).Offset(1, 1).EntireRow.Delete
r = r - 1: bRow = bRow - 1
End If
Next
End Sub
 
Quick and dirty. This is only going based off of your example data
where you want to consolidate column B value into one row if they
match on column A. Be sure to run this on a copy of your data to be
sure that you get the desired results.
Sub consil()
Dim r As Long, bRow As Long
bRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To bRow
If IsEmpty(Cells(r, 1)) Then Exit For
If Cells(r, 1).Text = Cells(r, 1).Offset(1, 0).Text Then
Cells(r, Range("IV" & r).End(xlToLeft).Offset(0,
1).Column) = _
Cells(r, 1).Offset(1, 1).Text
Cells(r, 1).Offset(1, 1).EntireRow.Delete
r = r - 1: bRow = bRow - 1
End If
Next
End Sub
Click to expand...

Thanks for the quick reply.

When I run the script I get a syntax error.

Can you have a quick look at this?

Rgds,
Dolphy
 
Thanks for the quick reply.

When I run the script I get a syntax error.

Can you have a quick look at this?

Rgds,
Dolphy
Click to expand...

I screwed up and I left out two columns:
\
04xxxxxxxx apple HOME Smog
04xxxxxxxx apple HOME alto
04xxxxxxxx apple HOME wagon
04xxxxxxxx apple HOME carriage
04xxxxxxxx apple HOME beeer
04xxxxxxxx apple HOME wine
04xxxxxxxx apple HOME cola
04xxxxxxxx apple HOME bourbon
04yyyyyyyy orange WORK Smog
04yyyyyyyy orange WORK alto
04yyyyyyyy orange WORK wagon
04yyyyyyyy orange WORK carriage
04yyyyyyyy orange WORK beeer
04yyyyyyyy orange WORK wine
04yyyyyyyy orange WORK bourbon
04zzzzzzzz banana OFFICE Smog
04zzzzzzzz banana OFFICE alto
04zzzzzzzz banana OFFICE wagon
04zzzzzzzz banana OFFICE carriage
04zzzzzzzz banana OFFICE beeer
04zzzzzzzz banana OFFICE wine
04zzzzzzzz banana OFFICE bourbon
04zzzzzzzz banana OFFICE magic
04zzzzzzzz banana OFFICE zulu
04zzzzzzzz banana OFFICE tango



Output should look like:

04xxxxxxxx apple HOME Smog alto wagon carriage beeer wine cola
bourbon
04yyyyyyyy orange WORK Smog alto wagon carriage beeer wine
bourbon
04zzzzzzzz banana OFFICE Smog alto wagon carriage beeer
wine bourbon magic zulu tango

Thanks!

Dolphy
 
Thanks for the quick reply.

When I run the script I get a syntax error.

Can you have a quick look at this?

Rgds,
Dolphy
Click to expand...

When I run it, it turns this:
Col1 Col2
04xxxxxxxx Smog
04xxxxxxxx alto
04xxxxxxxx wagon
04xxxxxxxx carriage
04xxxxxxxx beeer
04xxxxxxxx wine
04xxxxxxxx cola
04xxxxxxxx bourbon
04yyyyyyyy Smog
04yyyyyyyy alto
04yyyyyyyy wagon
04yyyyyyyy carriage
04yyyyyyyy beeer
04yyyyyyyy wine
04yyyyyyyy bourbon
04zzzzzzzz Smog
04zzzzzzzz alto
04zzzzzzzz wagon
04zzzzzzzz carriage
04zzzzzzzz beeer
04zzzzzzzz wine
04zzzzzzzz bourbon
04zzzzzzzz magic
04zzzzzzzz zulu
04zzzzzzzz tango

Into this:
Col1 Col2
04xxxxxxxx Smog alto wagon carriage beeer wine cola bourbon
04yyyyyyyy Smog alto wagon carriage beeer wine bourbon
04zzzzzzzz Smog alto wagon carriage beeer wine bourbon magic
zulu tango

Without any syntax errors to speak of. How is your data arranged?
The code is written assuming that col A contains the value to base off
of, column B contains the values to consolidate, and the data start in
row 2 with headers in row 1.
 
When I run it, it turns this:
Col1 Col2
04xxxxxxxx Smog
04xxxxxxxx alto
04xxxxxxxx wagon
04xxxxxxxx carriage
04xxxxxxxx beeer
04xxxxxxxx wine
04xxxxxxxx cola
04xxxxxxxx bourbon
04yyyyyyyy Smog
04yyyyyyyy alto
04yyyyyyyy wagon
04yyyyyyyy carriage
04yyyyyyyy beeer
04yyyyyyyy wine
04yyyyyyyy bourbon
04zzzzzzzz Smog
04zzzzzzzz alto
04zzzzzzzz wagon
04zzzzzzzz carriage
04zzzzzzzz beeer
04zzzzzzzz wine
04zzzzzzzz bourbon
04zzzzzzzz magic
04zzzzzzzz zulu
04zzzzzzzz tango

Into this:
Col1 Col2
04xxxxxxxx Smog alto wagon carriage beeer wine cola bourbon
04yyyyyyyy Smog alto wagon carriage beeer wine bourbon
04zzzzzzzz Smog alto wagon carriage beeer wine bourbon magic
zulu tango

Without any syntax errors to speak of. How is your data arranged?
The code is written assuming that col A contains the value to base off
of, column B contains the values to consolidate, and the data start in
row 2 with headers in row 1.
Click to expand...

I tried using the exact same data, I'm still getting the sytax error.
 
I screwed up and I left out two columns:
\
04xxxxxxxx apple HOME Smog
04xxxxxxxx apple HOME alto
04xxxxxxxx apple HOME wagon
04xxxxxxxx apple HOME carriage
04xxxxxxxx apple HOME beeer
04xxxxxxxx apple HOME wine
04xxxxxxxx apple HOME cola
04xxxxxxxx apple HOME bourbon
04yyyyyyyy orange WORK Smog
04yyyyyyyy orange WORK alto
04yyyyyyyy orange WORK wagon
04yyyyyyyy orange WORK carriage
04yyyyyyyy orange WORK beeer
04yyyyyyyy orange WORK wine
04yyyyyyyy orange WORK bourbon
04zzzzzzzz banana OFFICE Smog
04zzzzzzzz banana OFFICE alto
04zzzzzzzz banana OFFICE wagon
04zzzzzzzz banana OFFICE carriage
04zzzzzzzz banana OFFICE beeer
04zzzzzzzz banana OFFICE wine
04zzzzzzzz banana OFFICE bourbon
04zzzzzzzz banana OFFICE magic
04zzzzzzzz banana OFFICE zulu
04zzzzzzzz banana OFFICE tango

Output should look like:

04xxxxxxxx apple HOME Smog alto wagon carriage beeer wine cola
bourbon
04yyyyyyyy orange WORK Smog alto wagon carriage beeer wine
bourbon
04zzzzzzzz banana OFFICE Smog alto wagon carriage beeer
wine bourbon magic zulu tango

Thanks!

Dolphy
Click to expand...

Ah, didn't see this post before I posted the other one. In that case,
simply change the offset:
Sub consil()
Dim r As Long, bRow As Long
bRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To bRow
If IsEmpty(Cells(r, 1)) Then Exit For
If Cells(r, 1).Text = Cells(r, 1).Offset(1, 0).Text Then
Cells(r, Range("IV" & r).End(xlToLeft).Offset(0,
1).Column) = _
Cells(r, 1).Offset(1, 3).Text
Cells(r, 1).Offset(1, 1).EntireRow.Delete
r = r - 1: bRow = bRow - 1
End If
Next
End Sub
 
Ah, didn't see this post before I posted the other one. In that case,
simply change the offset:
Sub consil()
Dim r As Long, bRow As Long
bRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To bRow
If IsEmpty(Cells(r, 1)) Then Exit For
If Cells(r, 1).Text = Cells(r, 1).Offset(1, 0).Text Then
Cells(r, Range("IV" & r).End(xlToLeft).Offset(0,
1).Column) = _
Cells(r, 1).Offset(1, 3).Text
Cells(r, 1).Offset(1, 1).EntireRow.Delete
r = r - 1: bRow = bRow - 1
End If
Next
End Sub
Click to expand...

Hi JW,

Thanks for you assitance.

However, the script still isn't working.

I also forget another column.

I'm such a moron. I clearly cannot multitask.

04xxxxxxxx apple HOME Smog
04xxxxxxxx apple HOME alto
04xxxxxxxx apple HOME wagon
04xxxxxxxx apple HOME carriage
04xxxxxxxx apple HOME beeer
04xxxxxxxx apple HOME wine
04xxxxxxxx apple HOME cola
04xxxxxxxx apple HOME bourbon
04yyyyyyyy orange WORK Smog
04yyyyyyyy orange WORK alto
04yyyyyyyy orange WORK wagon
04yyyyyyyy orange WORK carriage
04yyyyyyyy orange WORK beeer
04yyyyyyyy orange WORK wine
04yyyyyyyy orange WORK bourbon
04zzzzzzzz banana OFFICE Smog
04zzzzzzzz banana OFFICE alto
04zzzzzzzz banana OFFICE wagon
04zzzzzzzz banana OFFICE carriage
04zzzzzzzz banana OFFICE beeer
04zzzzzzzz banana OFFICE wine
04zzzzzzzz banana OFFICE bourbon
04zzzzzzzz banana OFFICE magic
04zzzzzzzz banana OFFICE zulu
04zzzzzzzz banana OFFICE tango

Output should look like:

04xxxxxxxx apple HOME Smog alto wagon
carriage beeer wine cola
bourbon
04yyyyyyyy orange WORK Smog alto wagon
carriage beeer wine
bourbon
04zzzzzzzz banana OFFICE Smog alto wagon
carriage beeer
wine bourbon magic zulu tango

Thanks!

Dolphy
 
Another fine example of the futility of trying to follow a thread without
wearing out the scroll wheel.
Thank you ... to all you bottom posters ... who don't know the meaning of
<snip>.
 
Another fine example of the futility of trying to follow a thread without
wearing out the scroll wheel.
Thank you ... to all you bottom posters ... who don't know the meaning of
<snip>.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !



wine cola bourbon> > > > > 04yyyyyyyy Smog alto wagon carriage beeer
wine bourbon

wine bourbon magic zulu


bourbon magic
Click to expand...

You valuable contribution has been noted.
 
Your syntax error is probably coming from the line-wrapping in the post.

Try this revision.

Sub consil()
Dim r As Long, bRow As Long
bRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To bRow
If IsEmpty(Cells(r, 1)) Then Exit For
If Cells(r, 1).Text = Cells(r, 1).Offset(1, 0).Text Then
Cells(r, Range("IV" & r).End(xlToLeft).Offset(0, _
1).Column) = _
Cells(r, 1).Offset(1, 1).Text
Cells(r, 1).Offset(1, 1).EntireRow.Delete
r = r - 1: bRow = bRow - 1
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
Your syntax error is probably coming from the line-wrapping in the post.

Try this revision.

Sub consil()
Dim r As Long, bRow As Long
bRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To bRow
If IsEmpty(Cells(r, 1)) Then Exit For
If Cells(r, 1).Text = Cells(r, 1).Offset(1, 0).Text Then
Cells(r, Range("IV" & r).End(xlToLeft).Offset(0, _
1).Column) = _
Cells(r, 1).Offset(1, 1).Text
Cells(r, 1).Offset(1, 1).EntireRow.Delete
r = r - 1: bRow = bRow - 1
End If
Next
End Sub

Gord Dibben MS Excel MVP
Click to expand...

Gord, that most certainly could be the culprit. Dolphy, please let me
know if Gord's edited code works for you. Thanks Gord!
 
Back
Top