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" wrote in message
...
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!