Merging Cells in Excel

[fcoomermd]

This is what I need to do. I have spread sheet that looks similar to this
Source Company Contact Address
Mac Jones Inc Joe NULL
Sed Jones Inc Thomas 191 Main St.
Mac Capricorn Mike 15-12 Ohio
Sed Capricorn NULL 192 Arizona

I want to merge the cells where the company name is the same. If any of Mac's values are NULL, I want it to take Sed's value for that column. However, if both have a values, Mac takes priority... Is there a way to do this?
I tried alot of different things in SQL, but no real luck.
Any help, suggestions... Thanks
Fred

 

[Dreamboat]

Always just 2 records per?

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[fcoomermd]

Well, the sheet has about 9000 records, but 4500, needed to merge with the other 4500... each record has a matching record ( but subtle differnced, thus the merge).

 

[Dreamboat]

No prob. More than 2 records per company changes the formula considerably. Working on it.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[KenWright]

One way, have all your data in Cols A,B,C,D starting in A1. Now sort your data on Col B then on Col A, and hopefully you should see something like:-

Mac.xx xx
Sed xx xx xx
Mac xx xx
Sed xx xx
Mac xx xx xx
Sed................

You may have to sort on B then C then A, but the gist is to get your data sorted such that it is Mac's record first then Sed's for each company.

Do an Edit / Find & Replace and if as your data would suggest you have the text NULL in your records, replace it with a blank, but if you already have blanks and NULL was hjust a description for our benefit you can ignore this and leave it as it is.

Now select E1:H1 and type or paste in the following:-

=IF(MOD(ROW(),2)<>0,IF(A11<>&quot;&quot;,A11,A22),&quot;&quot

and then array enter it with CTRL+SHIFT+ENTER

Now just copy E1:H1 and paste down - Only ever other row should a record appear

This will take all data from Mac's record if there, but if not will pull whatever data from Sed's record exists for that field.

At the end just do a copy / paste special / values, sort the data and lose the rows you don't need.

Regards
Ken................