Help with a Macro

[Guest]

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

 

[Tom Ogilvy]

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,F:F").Delete
End Sub

 

[Guest]

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]

You said you had this

Name|Company|Prefix|Last|First|Suffix|stuff1|stuff2|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

 

[Guest]

Tom, Maybe I didn't say it right. I have...

Name|Company|Prefix|Last|First|Suffix|stuff1|stuff2|stuff3

and I would like...

Company&Last, First Suffix|stuff1|stuff2|stuff3

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

Rob,

Did Tom's suggestion fix it, or is it still outstanding?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Perfect!!! Thank you Tom!!!

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

 

[Guest]

Yes, Tom fixed my problem. Thanks!

Rob,

Did Tom's suggestion fix it, or is it still outstanding?

--

HTH

RP
(remove nothere from the email address if mailing direct)