This *almost* works. The problem seems to be (and I'm an excel rookie, so I
may not be seeing it correctly) that this formula merges cells H2 & H3, H4 &
H5, H6 & H7, ad infinitum. So, using the example I submitted earlier, it's
going to compare Joe and Bob in rows 2 & 3 instead of Bob and Bob in rows 3 &
4. If I delete Joe and make Bob the first row, then it works, until it hits a
similar situation down the line. The problem is that not every employee has
two rows; any that have only a home address also have only one row on the
spreadsheet.

Does this make any sense? I was pretty excited because the formula seemed to
be working at first, but as I went down the spreadsheet I could see where it
got offset, and it doesn't work past that point until it hits another pair
that matches up.

I really appreciate your help on this.

Thanks,

Terrell

"Bob Tarburton" wrote:

Assuming row 1 is headers and no sheet has more than 4999 records, copy the
following to cell H2 then copy down.

=IF(ISNA(MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000 ="Mail"),0),0)),IF(ROW(A2)-1=MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="Home" ),0),0),"Keep","Duplicate"),IF(ROW(A2)-1=MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="Mail" ),0),0),"Keep","Duplicate"))

Sort on Column H descending.
It will "Keep" the first Mail record for each ID, if there is one, of the
first Home if there is no mail (it also allows for multiple home or mail,
and I could change it to select the last if you prefer).
Note that if you do not start in row 2, not only change the $2 parts but
also the ROW(A2)-1 parts. If you start at $3, then you need to start with
ROW(A3)-2 and so on.

You still have to deal with multiple sheets, but each is a breeze.

"Terrell" wrote in message
...
I have several spreadsheets with as many as 4000 rows apiece in which I
need
to combine certain fields. Here's an example of the spreadsheet I have:

| A. ID | B. Name | C. Add Type | D. Add 1 | E. City | F. State | G.
ZIP
---------------------------------------------------------------------------------------
1.| 1000 | Joe | Home | 101 Main | OKC | OK |
77777
2.| 1001 | Bob | Home | 201 2nd | OKC | OK |
77777
3.| 1001 | Bob | Mail | 100 12th | OKC | OK
|
77771
4.| 1003 | Jim | Home | 222 Main | EDM | OK |
77703
5.| 1005 | Sue | Home | 301 Ave | EDM | OK |
77703
6.| 1005 | Sue | Mail |400 East | OKC | OK
| 77777

What I basically need to do is find the employees (with the same ID #)
that
have both a home and a mailing address, and either delete the Home
address,
or combine the rows in a way in which only the mailing address remains, so
I
can mail merge the spreadsheet without getting two envelopes for each
employee that has both a mailing and a home address. I could do this
manually, but with multiple spreadsheets and over 4000 employees, it gets
pretty tedious and mistakes tend to be made. Anyone have any suggestions?