|
Help with a Macro
I need to merge columns, regardless of how many rows there are. I got some
great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Tom,
I appreciate you quick reply. I ran the macro you suggested, but it does not seem to do what I was hoping. Maybe I wasn't very clear. Am I missing something? If I need to explain it again in different terms, let me know. A beginner... Rob "Tom Ogilvy" wrote: Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
You said you had this
Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 As I understood you you wanted to end up with this |Company&Last|Name|Suffix|stuff1|stuff2|stuff3 the macro was off a little. Try this: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:F").Delete End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Tom, Maybe I didn't say it right. I have...
Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 and I would like... Company&Last, First Suffix|stuff1|stuff2|stuff3 "Tom Ogilvy" wrote: You said you had this Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 As I understood you you wanted to end up with this |Company&Last|Name|Suffix|stuff1|stuff2|stuff3 the macro was off a little. Try this: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:F").Delete End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Sub CCC()
Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"", ""&F1&"" ""&G1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:G").Delete End Sub What you said originally A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 What you say now Company&Last, First Suffix|stuff1|stuff2|stuff3 Included name and left out First - so yes, I would say maybe you didn't say it right, particularly when you add in word wrap and spacing. Hopefully this will do it for you. the macro produced this for me: CompanyLast, First Suffix|stuff1|stuff2|Stuff3 -- Regards, Tom Ogilvy "Rob" wrote in message ... Tom, Maybe I didn't say it right. I have... Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 and I would like... Company&Last, First Suffix|stuff1|stuff2|stuff3 "Tom Ogilvy" wrote: You said you had this Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 As I understood you you wanted to end up with this |Company&Last|Name|Suffix|stuff1|stuff2|stuff3 the macro was off a little. Try this: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:F").Delete End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Slight variation
Sub CCC() Dim rng As Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"",""&B1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:F").Delete End Sub You are deleting First as well. -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Tom, I appreciate you quick reply. I ran the macro you suggested, but it does not seem to do what I was hoping. Maybe I wasn't very clear. Am I missing something? If I need to explain it again in different terms, let me know. A beginner... Rob "Tom Ogilvy" wrote: Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Tom,
I apologize that I wrote the wrong thing at first. Thank you for your perseverance even though!!! Something still isn't right. It seems to be only merging data if all cells have data. First instance, the first tow, with the headings of COMPANY, LAST, FIRST etc all had data and your macro worked perfectly. The problem seems to be that only half of the rows below that have a company and only half have a last, first, and suffix. Make sense? Again, maybe I screwed up and should have said this in the first place. I didn't realize it matters if there was data in the columns. Rob "Tom Ogilvy" wrote: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"", ""&F1&"" ""&G1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:G").Delete End Sub What you said originally A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 What you say now Company&Last, First Suffix|stuff1|stuff2|stuff3 Included name and left out First - so yes, I would say maybe you didn't say it right, particularly when you add in word wrap and spacing. Hopefully this will do it for you. the macro produced this for me: CompanyLast, First Suffix|stuff1|stuff2|Stuff3 -- Regards, Tom Ogilvy "Rob" wrote in message ... Tom, Maybe I didn't say it right. I have... Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 and I would like... Company&Last, First Suffix|stuff1|stuff2|stuff3 "Tom Ogilvy" wrote: You said you had this Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 As I understood you you wanted to end up with this |Company&Last|Name|Suffix|stuff1|stuff2|stuff3 the macro was off a little. Try this: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:F").Delete End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Bob,
Thank for your help... I need a little more help!!! Take a look at this again... Here is what I need... From... Name | Company | Prefix | Last | First | Suffix | Stuff1 | Stuff2 | stuff3 To... CompanyLast, First Suffix | Stuff1 |Stuff2 | Stuff3 Also note that not all rows have data in each of these columns. yes, I am deleting Name and Prefix "Bob Phillips" wrote: Slight variation Sub CCC() Dim rng As Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"",""&B1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:F").Delete End Sub You are deleting First as well. -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Tom, I appreciate you quick reply. I ran the macro you suggested, but it does not seem to do what I was hoping. Maybe I wasn't very clear. Am I missing something? If I need to explain it again in different terms, let me know. A beginner... Rob "Tom Ogilvy" wrote: Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Sub CCC()
Columns(1).Insert Set rng = Intersect(Activesheet.UsedRange, _ Activesheet.Columns(3)).Cells rng.Offset(0, -2).Formula = "=C1&E1&"", ""&F1&"" ""&G1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:G").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... Tom, I apologize that I wrote the wrong thing at first. Thank you for your perseverance even though!!! Something still isn't right. It seems to be only merging data if all cells have data. First instance, the first tow, with the headings of COMPANY, LAST, FIRST etc all had data and your macro worked perfectly. The problem seems to be that only half of the rows below that have a company and only half have a last, first, and suffix. Make sense? Again, maybe I screwed up and should have said this in the first place. I didn't realize it matters if there was data in the columns. Rob "Tom Ogilvy" wrote: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"", ""&F1&"" ""&G1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:G").Delete End Sub What you said originally A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 What you say now Company&Last, First Suffix|stuff1|stuff2|stuff3 Included name and left out First - so yes, I would say maybe you didn't say it right, particularly when you add in word wrap and spacing. Hopefully this will do it for you. the macro produced this for me: CompanyLast, First Suffix|stuff1|stuff2|Stuff3 -- Regards, Tom Ogilvy "Rob" wrote in message ... Tom, Maybe I didn't say it right. I have... Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 and I would like... Company&Last, First Suffix|stuff1|stuff2|stuff3 "Tom Ogilvy" wrote: You said you had this Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 As I understood you you wanted to end up with this |Company&Last|Name|Suffix|stuff1|stuff2|stuff3 the macro was off a little. Try this: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:F").Delete End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Rob,
Did Tom's suggestion fix it, or is it still outstanding? -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Bob, Thank for your help... I need a little more help!!! Take a look at this again... Here is what I need... From... Name | Company | Prefix | Last | First | Suffix | Stuff1 | Stuff2 | stuff3 To... CompanyLast, First Suffix | Stuff1 |Stuff2 | Stuff3 Also note that not all rows have data in each of these columns. yes, I am deleting Name and Prefix "Bob Phillips" wrote: Slight variation Sub CCC() Dim rng As Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"",""&B1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:F").Delete End Sub You are deleting First as well. -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Tom, I appreciate you quick reply. I ran the macro you suggested, but it does not seem to do what I was hoping. Maybe I wasn't very clear. Am I missing something? If I need to explain it again in different terms, let me know. A beginner... Rob "Tom Ogilvy" wrote: Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Perfect!!! Thank you Tom!!!
"Tom Ogilvy" wrote: Sub CCC() Columns(1).Insert Set rng = Intersect(Activesheet.UsedRange, _ Activesheet.Columns(3)).Cells rng.Offset(0, -2).Formula = "=C1&E1&"", ""&F1&"" ""&G1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:G").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... Tom, I apologize that I wrote the wrong thing at first. Thank you for your perseverance even though!!! Something still isn't right. It seems to be only merging data if all cells have data. First instance, the first tow, with the headings of COMPANY, LAST, FIRST etc all had data and your macro worked perfectly. The problem seems to be that only half of the rows below that have a company and only half have a last, first, and suffix. Make sense? Again, maybe I screwed up and should have said this in the first place. I didn't realize it matters if there was data in the columns. Rob "Tom Ogilvy" wrote: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"", ""&F1&"" ""&G1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:G").Delete End Sub What you said originally A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 What you say now Company&Last, First Suffix|stuff1|stuff2|stuff3 Included name and left out First - so yes, I would say maybe you didn't say it right, particularly when you add in word wrap and spacing. Hopefully this will do it for you. the macro produced this for me: CompanyLast, First Suffix|stuff1|stuff2|Stuff3 -- Regards, Tom Ogilvy "Rob" wrote in message ... Tom, Maybe I didn't say it right. I have... Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 and I would like... Company&Last, First Suffix|stuff1|stuff2|stuff3 "Tom Ogilvy" wrote: You said you had this Name|Company|Prefix|Last|First|Suffix|stuff1|stuff 2|stuff3 As I understood you you wanted to end up with this |Company&Last|Name|Suffix|stuff1|stuff2|stuff3 the macro was off a little. Try this: Sub CCC() Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:F").Delete End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
Help with a Macro
Yes, Tom fixed my problem. Thanks!
"Bob Phillips" wrote: Rob, Did Tom's suggestion fix it, or is it still outstanding? -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Bob, Thank for your help... I need a little more help!!! Take a look at this again... Here is what I need... From... Name | Company | Prefix | Last | First | Suffix | Stuff1 | Stuff2 | stuff3 To... CompanyLast, First Suffix | Stuff1 |Stuff2 | Stuff3 Also note that not all rows have data in each of these columns. yes, I am deleting Name and Prefix "Bob Phillips" wrote: Slight variation Sub CCC() Dim rng As Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1&"",""&B1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("B:F").Delete End Sub You are deleting First as well. -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Tom, I appreciate you quick reply. I ran the macro you suggested, but it does not seem to do what I was hoping. Maybe I wasn't very clear. Am I missing something? If I need to explain it again in different terms, let me know. A beginner... Rob "Tom Ogilvy" wrote: Sub CCC() Dim rng as Range Columns(1).Insert Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp)) rng.Offset(0, -2).Formula = "=C1&E1" rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value Range("C:D,F:F").Delete End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... I need to merge columns, regardless of how many rows there are. I got some great help last week, now I need to take it a step further... A B C D E F G H I Name Company Prefix Last First Suffix stuff1 stuff2 stuff3 into... A B C D CompanyLast, Name Suffix stuff1 Stuff2 stuff3 (I do realize I am deleting the Prefix in C) Any help would be deeply appreciated!!! Rob |
| All times are GMT +1. The time now is 11:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com