| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reorganising Data
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 19, 10:35 pm, Dolphy wrote:
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 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 1:42 pm, JW wrote:
On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 2:00 pm, Dolphy wrote:
On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 12:00 am, Dolphy wrote:
On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 2:11 pm, JW wrote:
On Sep 20, 12:00 am, Dolphy wrote: On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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. I tried using the exact same data, I'm still getting the sytax error. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 12:11 am, Dolphy wrote:
On Sep 20, 2:00 pm, Dolphy wrote: On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 2:16 pm, JW wrote:
On Sep 20, 12:11 am, Dolphy wrote: On Sep 20, 2:00 pm, Dolphy wrote: On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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 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 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
May be u could try creatin a Pivot and then sorting the data.
"Dolphy" wrote: On Sep 20, 2:16 pm, JW wrote: On Sep 20, 12:11 am, Dolphy wrote: On Sep 20, 2:00 pm, Dolphy wrote: On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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 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 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
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 ! --------------------------------------------------------------------------- "Dolphy" wrote in message ps.com... On Sep 20, 2:11 pm, JW wrote: On Sep 20, 12:00 am, Dolphy wrote: On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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. I tried using the exact same data, I'm still getting the sytax error. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 5:00 pm, "Ragdyer" wrote:
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 ! ---------------------------------------------------------------------------"Dolphy" wrote in message ps.com... On Sep 20, 2:11 pm, JW wrote: On Sep 20, 12:00 am, Dolphy wrote: On Sep 20, 1:42 pm, JW wrote: On Sep 19, 10:35 pm, Dolphy wrote: 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 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 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 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. I tried using the exact same data, I'm still getting the sytax error. You valuable contribution has been noted. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
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 On Wed, 19 Sep 2007 21:14:06 -0700, Dolphy wrote: I tried using the exact same data, I'm still getting the sytax error. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reorganising Data
On Sep 20, 11:43 am, Gord Dibben <gorddibbATshawDOTca wrote:
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 On Wed, 19 Sep 2007 21:14:06 -0700, Dolphy wrote: I tried using the exact same data, I'm still getting the sytax error. Gord, that most certainly could be the culprit. Dolphy, please let me know if Gord's edited code works for you. Thanks Gord! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) | |||
Linear Mode
