• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Merging 2 rows in excel

A

AlwaysRU

Member
Hi, I have a spreadsheet with data like this (x's are blank spaces) (a space is a different cell):

Public Dean David xxxxxxxxxxxxx 9999 Spencer Hwy La Porte
Public Dean David Teresa Holland 9999 Spencer Hi La Porte

Now, I want to have both rows the same, with the "greater" (The one with more text in it) column replacing the text, for example:

Public Dean David Teresa Holland 9999 Spencer Hwy La Porte
Public Dean David Teresa Holland 9999 Spencer Hwy La Porte

Is this possible? Any help would be VERY VERY VERY appreciated. Thanks!


Jeff


I did a macro to do it for 1 record... how can i make this macro not cell-specific?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/3/01 by jeberhard
'

'
Application.CutCopyMode = False
Selection.Copy
Range("E4&quot😉.Select
ActiveSheet.Paste
Range("D4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("D3&quot😉.Select
ActiveSheet.Paste
Range("I4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("I3&quot😉.Select
ActiveSheet.Paste
Range("M4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("M3&quot😉.Select
ActiveSheet.Paste
Range("N4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("N3&quot😉.Select
ActiveSheet.Paste
Range("R4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("R3&quot😉.Select
ActiveSheet.Paste
Range("W4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("W3&quot😉.Select
ActiveSheet.Paste
Range("AE4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("AE3&quot😉.Select
ActiveSheet.Paste
Range("AQ4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("AQ3&quot😉.Select
ActiveSheet.Paste
Range("AR4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("AR3&quot😉.Select
ActiveSheet.Paste
Range("AS4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("AS3&quot😉.Select
ActiveSheet.Paste
Range("AU4:BM4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("AU3&quot😉.Select
ActiveSheet.Paste
Range("BN3&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("BN4&quot😉.Select
ActiveSheet.Paste
Range("FM4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("FM3&quot😉.Select
ActiveSheet.Paste
Range("GA4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("GA3&quot😉.Select
ActiveSheet.Paste
Range("GS3&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("GS4&quot😉.Select
ActiveSheet.Paste
Range("GY3:HE3&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("GY4&quot😉.Select
ActiveSheet.Paste
Range("HF4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("HF3&quot😉.Select
ActiveSheet.Paste
Range("HI4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("HI3&quot😉.Select
ActiveSheet.Paste
ActiveSheet.Paste
Range("HI4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("HI3&quot😉.Select
ActiveSheet.Paste
Range("HK3&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("HK4&quot😉.Select
ActiveSheet.Paste
Range("HQ3&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("HQ4&quot😉.Select
ActiveSheet.Paste
Range("HR4:IV4&quot😉.Select
Application.CutCopyMode = False
Selection.Copy
Range("HR3&quot😉.Select
ActiveSheet.Paste
Range("A5&quot😉.Select
End Sub

 
I have to say this is not like any post I have ever seen here.
And it's about time😉.

The only way I can think of to do it is to export it to a database and do some sort of GROUP BY. If you can do it just in Excel without some absurdly complicated macro, I don't know how (not that that means anything).
 
Well, it all was in an ACT database, and unfortunatly ACT doesnt have anything to merge everything for duplicates. Boooo ACT! Any other ideas?
 
do a character count using VB of how many are in the cell, subtract any spaces. Then compare the 2 cells, and then use an IF newtextcount > oldtextcount then go ahead and replace.

I don't think it would be too horrible to set up, but it might get a little messy if you don't know VB text/code all that well.

But I guess it all depends on how often you'd be using a feature such as this.
 
Duh. That's a good idea, azncarjunkie. I was fixated on "merge" but this is really a substitution. It would have to be a vb function called from the cell. I don't think it's that bad, but I don't have excel here at home, only at work. If you don't get it resolved by Thursday I'll give it a try. It would go something like

Is the value in cell a the same as in this row as in the next one?
If it is, is the text length in cell b shorter than the text in the other row?
If it is, replace the text in cell b with the text from the other row, or vice versa.

I don't think it's that bad. I'm right in the middle of some excel macro stuff, so whatever I know is fairly fresh in my mind.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top