Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a function to consolidate multiple records into one recor
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? |
#2
|
|||
|
|||
On Mon, 10 Oct 2005 07:41:03 -0700, "Richmond"
wrote: 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? 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","Li st"),"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 __________________________ |
#3
|
|||
|
|||
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 "Richard Buttrey" wrote: On Mon, 10 Oct 2005 07:41:03 -0700, "Richmond" wrote: 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? 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","Li st"),"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 __________________________ |
#4
|
|||
|
|||
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 On Mon, 10 Oct 2005 08:34:06 -0700, "Richmond" wrote: 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 "Richard Buttrey" wrote: On Mon, 10 Oct 2005 07:41:03 -0700, "Richmond" wrote: 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? 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","Li st"),"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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
|
|||
|
|||
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 "Richard Buttrey" wrote: 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 On Mon, 10 Oct 2005 08:34:06 -0700, "Richmond" wrote: 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 "Richard Buttrey" wrote: On Mon, 10 Oct 2005 07:41:03 -0700, "Richmond" wrote: 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? 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","Li st"),"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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import txt file with multiple rows for each record | Excel Discussion (Misc queries) | |||
How to consolidate data from multiple worksheets. | Excel Worksheet Functions | |||
Using solver with function with multiple outputs | Excel Worksheet Functions | |||
How do I use multiple "Does not contain" function in excel? | Excel Worksheet Functions | |||
Multiple Worksheets and Print Merge function | Excel Discussion (Misc queries) |