Combining Information from rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 
Sorry, all the references to column B should have been to column C.
I didn't mean to look at the name at all, just the ID, and the Home/Mail
column.
Had it set up wrong in my sheet when I was checking the formula.
It shouldn't matter how many entries for each person or what order they
start in, as long as the ID# is consistent and column C has "Home" or "Mail"

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

If there's a problem I'll get back tomorrow.
 
I actually had already found the glitch with rows B and C, and changed it in
the formula you gave me. I still seem to be having the problem I discribed in
my previous message, however. I'm not sure what I'm doing wrong, but It only
puts "keep" or "delete" on every other row consistently down the spreadsheet.
About 40 rows down it merges two different employees and after that it misses
several mailing addresses in a row. Also, when I try to sort on column H, it
gives me the error, "This operation requires the merged cells to be
identically sized."

Any thoughts?
 
First you will want to unmerge your cells
Select all, Format/Cells on the Alignment tab uncheck the merge box.

Next can you tell me what row your headers are in and what row the list data
starts in?
Are there and non-numberic chacters in the IDs? Or numbers stored as text?
Also, the formula can be simplified if tyou are sure there are at most 1
home listing and 1 mail listing for each employee.
 
Perhaps you should e-mail me a sample sheet showing that the formula isn't
working.
Just delete the "_removethis_" part from my email. Post a message if you do
so I know to look for it.
 
Got it! I unmerged the cells in column H and copied down, and it worked like
a charm.

Thanks a million, that's going to save me a whole lot of time.

Terrell
 
I have been reading this thread and it appears to be close to what I am
trying to acheive. Could you possible amend your formula for me?

I have lots of duplicate contacts in Outlook and have run a "remove
duplicates" app which does just that; it removes duplicates! What I really
need is to merge duplicate contacts where there is different data in
different fields. I have exported the data into excel and now have one set
of headers for all contacts but within the rows of contacts I have some
duplicates. One entry might have the mobile number but not the email address
the other might be the reverse of this. What I need to do is to merge rows
where duplicates occur (maybe using the surname and forename as a point of
reference) and so end up with one entry that has a full set of data.

Could you help with this please?

Many thanks

Andrew
 
Back
Top