PDA

View Full Version : Consolidating data



PamK
11-10-2014, 05:13 PM
I have a spreadsheet containing a unique ID in column A, however, there are duplicate ID's throughout the data with a few unique as well. Where the ID's are duplicated, I need to gather the data in columns Q through U and append to the first instance of the ID for as many times as the ID is duplicated and then delete those duplicated rows. Any help would be appreciated. I have attached a sample with 49 records plus a header.

p45cal
11-12-2014, 11:54 AM
Not quite what you asked for but would a pivot table do? See attached.
post posting edit:
if you right click on the heading ID (not its dropdown arrow, nor the Count of ID cell above it) and choose Field Setings… then click Layout button (Layout & Print tab in some versions) then tick the box Insert blank line after each item label, this shoould make the grouping a bit more obvious when looking at the right hand part of the table.

PamK
11-18-2014, 07:58 AM
Thanks for the suggestions, but I need 1 row per ID with only the data in columns Q through U appended to the end of the data for each duplicated record. Since I was unable to find a VBA solution, I was able to accomplish this in SQL. Thanks for the input though, it's greatly appreciated.