Excell-merging data from 2 files

[LRP2]

I have 2 files from a customer that I need to merge into one file.

File 1 has typical columns (ID #, Last name, First Name, Address, City, St
etc)
File 2 has only 2 columns (ID # and Hosp. Aff. Code)

The ID #'s in both files may have multiple listings (in this case the IDs
refer to doctors which may have multiple offices so each office gets its own
listing or row in File 1).
ex: ID # Last name First name City St
100 McCoy Bones OKC OK
100 McCoy Bones Guthrie OK

File 2 has a row for each ID # and Hosp. Aff. Code
ex: ID # Hosp Code
100 OKCMH
100 MWCH
100 GMH

The end result needs to be this:
ID # Last name First name City St Hosp Code
100 McCoy Bones OKC OK OKCMH, MWCH,
GMH
100 McCoy Bones Guthrie OK OKCMH, MWCH,
GMH

Each ID # in File 1 needs to have ALL of the hospital codes from File 2.
Have tried a couple of add-in programs that claim to be able to do this, but
when it hits the different hosp codes for the same ID, it either plugs in the
1st one for all of them or the last one for all of them (ignoring the other
codes completely)

This is a rather lengthy database (9,000 rows of doctors) manually
rearranging the info line by line is not feasible.

Thanks in advance for any info!

 

[Earl Kiosterud]

LRP2,

I was doing OK until I saw you want the hospital codes in the same cell with each
Doctor-location. Are you sure you want to combine them like that? If you'll be doing any
kind of analysis or summarizing based on those hospital codes, you'll be pretty much out of
luck. In that case there should be a separate row for each hospital code in the output list
(for each doctor-location combination). Consider this very carefully before you continue.
The question is "what's this list going to be needed for?"

If you do want the hospital codes combined, it's going to take macro coding, I'm pretty
sure. This isn't a very standard operation. Perhaps this reflects my bias away from
formula solutions, which can quickly get difficult, geeky and hard to debug and maintain.
Macro solutions are straightforward.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[LRP2]

hi, in answer to your question: Doesn't really matter to me if the Hosp
Codes are in the same cell or different ones, as long as they are all there
and can be seperated by a "comma + space" either in Excel or added later in
InDesign. I am merging these two excel files in order to export as a
tab-deliminated txt file hat I can bring into InDesgn for a catalog. There
will be extensive global formating once I get the raw data into the page
layout program which is a moot point right now, until I can get the data
ready for export.

Right now "File 2" which has the hospital codes has a INDIVIDUAL ROW for
each code and we need them all in the SAME ROW for the coresponding ID #,
but keeping the ID in the column (May not have shown up clearly in my post,
it looks like the column alignment vanished)

 

[Earl Kiosterud]

LRP2,

First of all, if you connect directly to news.microsoft.com using a newsreader (like Outlook
Express), you can set it for plain text, using Courier for the font. Then the column
alignment will be maintained. I don't know how this is handled, if at all, using a web
interface to the newsgroup.

This is a barebones, brute-force macro that will accumulate the doctor-hospital codes in
column 6 of the doctor-location table (the first one). It can be optimized, but it not may
be worth the effort for a one-time operation. It puts spaces between the hospital codes.

Sub GetHospCodes()
Dim ID1 As Range
Dim ID2 As Range
Dim HospCodes As Range

Set ID1 = Sheets("Dr-Loc").Cells(2, 1)
Set HospCodes = Sheets("Dr-Loc").Cells(2, 6)
Set HospCodes = Sheets("Dr-Loc").Cells(2, 6)

Do ' go through Dr-Loc table
Set ID2 = Sheets("Dr-Hosp").Cells(2, 1)
Do ' loop through Dr-Hosp table
If ID1.Value = ID2.Value Then ' match?
HospCodes = HospCodes & " " & ID2.Offset(0, 1)
End If
Set ID2 = ID2.Offset(1, 0) ' move down Dr-Hosp table
Loop While ID2.Value <> ""
Set ID1 = ID1.Offset(1, 0) ' move down
Set HospCodes = HospCodes.Offset(1, 0)
Loop While ID1.Value <> ""
End Sub

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------