Is there a function to consolidate multiple records into one recor

R

Richmond

I have a spreadsheet with multiple records per person and I need to move the
data from the secondary listings to the first record so that I have only one
record per person, as this data is going to be used for a mail merge.

Any suggestions for how to do this quickly?
 
R

Richard Buttrey

I have a spreadsheet with multiple records per person and I need to move the
data from the secondary listings to the first record so that I have only one
record per person, as this data is going to be used for a mail merge.

Any suggestions for how to do this quickly?
Click to expand...

Assuming you want the second record to completely replace the first
record, then the approach I'd adopt is:

1. Sort the records by person
2. Use a helper column with the following formula

=IF(COUNTIF($A$15:$A$20,A15)>1,IF(A15=A16,"XX","List"),"List")

Assumes the person name is in column A, and a 6 row data set (rows
15:20) - change as appropriate.

3. Now filter the list on "XX" and delete the duplicate (first)
records.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
R

Richmond

Thanks so much, Richard, but unfortunately, I have a need not only to keep
the primary record, but to add to it for each secondary record. So for
instance, on the first record, I have the person's name and address and the
details related to one degree that the person has earned. On the second
line, I have the details related to another degree the person has earned -
and need to move that data from the second record to additional columns under
the first record. Unless someone knows of a way to consolidate the data
using the Word Mail Merge function.

Thanks for any help you can give!
Kristin
 
R

Richard Buttrey

How many potential records are there per person? Is it variable such
that you might need to add to only one column for one person, but two
or maybe more columns for another person?

If there are only ever one or two records per person, and assuming the
layout is as follows, starting with field headings in row 14

A B C D
Name Anyddress Degree1 Degree 2
John Anyaddress BSc
John MSc
Fred Anotheraddress PhD
Jane Yetanother MSc

then you could use the following in column D

=IF(A15=A16,C15,"")

This will now include the degree from column C against all rows
identified by "List" in col E.

Then simply filter out the "List" rows.

If it's more complex than this, then I think I'd be inclined to write
a simple VBA macro which loops down the names, and copies the second
third, fourth...etc. row details into the second, third, fourth etc..
columns against the first record.

If you want to pursue this VBA macro further, please let me know. It's
probably only a few lines of code.

Rgds


Thanks so much, Richard, but unfortunately, I have a need not only to keep
the primary record, but to add to it for each secondary record. So for
instance, on the first record, I have the person's name and address and the
details related to one degree that the person has earned. On the second
line, I have the details related to another degree the person has earned -
and need to move that data from the second record to additional columns under
the first record. Unless someone knows of a way to consolidate the data
using the Word Mail Merge function.

Thanks for any help you can give!
Kristin
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
R

Richmond

Many, many thanks Richard! While I did have a variable number of entries per
person, the majority had only two entries. I used a pivot table to count the
number of entries per person and then drew that count back into my
spreadsheet using vlookup. I sorted on this value and for those with three
or four records, I just tweaked the if statement to bring back the correct
value. I'm down to one record per person in no time flat!

Thanks again!
Kristin
 
Top