How to combine values from multiple rows into a single row

Question

I have an Excel 2007 spreadsheet with 2250 rows and 19 columns. In these rows, I may have two rows of duplicate customer information that need to be combined, but only if the cell above is empty. I may also have rows of customer data that are not in need of combining. A unique customer member number can be used to identify the rows that need to be combined together. I'm struggling on developing the right VBA script to combine the unique customer data into the one row (on top) and delete the row that is remaining after combining. Is anyone willing to assist? It will save me hours/days of hand combining these rows and we are in the middle of a time sensitive audit.

Sample of our data:

MEMBER FIRST NAME   MEMBER LAST NAME    MEMBER #    MVP SYSTEM ENTRY DATE   ENROLL DATE MVP POINTS  DRAWING ENTRIES ENROLL FORM?    POINTS CORRECT? POINTS MISSED   FINAL POINTS    DRAWING ENTRIES SP Talon #  WP Talon #  BD  DEPT    EMPLOYEE    NOTES   DLR
Gene    S   550061  3/2/2013        0   0               0   #N/A                            
Gene    S   550061      3/2/2013                                1539    137     MC  MJ      SP
Steve   G   550087      3/2/2013                                30019   1588        PA  NR      WP
Curtis  S   550128  4/24/2013       5   0               5   #N/A                            
Curt    S   550128      4/24/2013                               358 47      MC  MJ      SP

Edit (not from OP) to add pipe/paragraph delimited version with underlines for spaces in headings:

MEMBER_FIRST_NAME|MEMBER_LAST_NAME|MEMBER_#|MVP_SYSTEM_ENTRY_DATE|ENROLL_DATE|MVP_POINTS|DRAWING_ENTRIES|ENROLL_FORM?|POINTS_CORRECT?|POINTS_MISSED|FINAL_POINTS|DRAWING_ENTRIES|SP_Talon_#|WP_Talon_#|BD|DEPT|EMPLOYEE|NOTES|DLR
Gene|S|550061|03/02/2013||0|0||||0|#N/A|||||||
Gene|S|550061||03/02/2013||||||||1539|137||MC|MJ||SP
Steve|G|550087||03/02/2013||||||||30019|1588||PA|NR||WP
Curtis|S|550128|4/24/2013||5|0||||5|#N/A|||||||
Curt|S|550128||4/24/2013||||||||358|47||MC|MJ||SP

Answer 1

I'm not too sure of the clarification you provided, but here goes anyway!:

Key point- the following assumes that within MEMBER # MVP SYSTEM ENTRY DATE will always feature above ENROLL DATE.

For safety, work on a copy and add an index number to each row (say insert ColumnA, put 1 in A1, =A1+1 in A2 and copy the formula down to Row2250. Copy ColumnA and Paste Special/Values over the top.

Select D2, Home > Styles – Conditional Formatting, New Rule, Use a formula to determine which cells to format, Format values where this formula is true: insert =AND(NOT(ISBLANK(D2)),OR(D1=D2,D2=D3)), Format, Fill, select yellow, OK, OK. In Conditional Formatting - Manage Rules, in Applies to enter =$D$2:$D$2250, Apply. OK.

Select entire spreadsheet (click on triangle to the left of A and above 1 in the Headings), Data > Sort & Filter –Filter and forColumnD Filter by color, select yellow.

Copy Row1 down to the last row numbered in blue and paste into A1 of a different sheet (say Sheet2).

In Sheet2, delete F1, Shift cells up, OK. Also N1:T1. (This is where some additional eyeballing may be required.)

Add a new ColumnA to Sheet2. Put 1 in A1, 2 in A2, select A1:A2, grab the bottom right hand corner of the selection, keep left mouse button depressed while dragging down as far as required and until after pressing and holding Ctrl.

Select Sheet2, Data > Sort & Filter – Sort, check My data has headers, Sort by ColumnA (the first of the 1s!), Sort on Values, Order Smallest to Largest, OK.

Note the lowest row number that contains 2 in ColumnA of Sheet2 and the number of the highest occupied row. Delete ColumnA.

Go back to your first sheet and delete all rows containing yellow highlighting.

In Sheet2, select the lower row number and all other occupied rows with a higher number copy and past back in to ColumnA at the bottom of your first sheet.

Hopefully that achieves most of what you require - or if not that it is 'a step in the right direction'! To check, your last occupied row should now be 2250+1 less the difference between the two numbers noted above.

To check MEMBER FIRST NAME I suggest creating a lookup table of MEMBER # and that and then comparing MEMBER FIRST NAME on that basis in the sheet that you took a copy of. Curt or Curtis is presumably a judgement call.

Answer 2

Here is an another possible approach. It depends on three conditions:

• A unique identifier must be available to distinguish duplicate from non-duplicate fields. In this case, the field MEMBER# serves that purpose. In other instances, the identifier might be built up as the combination of the values in several fields.This id could be the value in a single field or a composite of the values in several fields.
• No more than two duplicates of any MEMBER#, i.e., no triple or higher multiple "duplicate" records.
• The rows are sorted on the MEMBER# identifier.

The idea is to construct a transformed table, most conveniently to the right of the existing table, that uses formulas to consolidate -- into a single row -- the partial data that is shared between two duplicate rows, leaving one filled row and one blank row.

After this is done, a filter can be applied to the result table to exclude the blank rows, leaving the filled rows to be copied to another location.

As shown below, I've added a flag field "DUP" in column A: It equals 1 if a MEMBER# in column C equals the MEMBER# in the preceding row, and equals 0 otherwise. The two sets of rows in the example data with duplicate MEMBER#'s are highlighted in yellow.

Here's what the result table of formulas looks like. As expected, the complementary information that was shared between two records has been gather into one of the records, leaving the other record filled with double-dashes ("--"). (The two sets of duplicate rows in the example data are highlighted in darker blue in the table.)

Looking at the first two rows of the table, which held duplicate versions for MEMBER# 550061, the second "Gene" in row 4 of the MEMBER_FIRST_NAME column has been replaced with "--"; the previously blank ENROLLMENT_DATE in row 3 is now filled with 3/2/2013, moved up from row 4; the N/A values for the second DRAWING_ENTRIES field (column M in the original table, column AS in the new one) have been replaced by blanks.

All that's left to be done is apply a filter, use the DUP column as the criterion column, select only the rows where DUP equals 0 - and copy the result to a new location.

The formulas used to consolidate the duplicates are essentially identical in structure, so it makes sense to examine one at length. Here's the first formula in the table, from cell AH3, for the MEMBER_FIRST_NAME column (I'm including at the end of this post the full set of formulas for the first row of the result table).

=IF($A3=1,                               If this is row 2 of a DUP set,
  "--",                                    Set value of the result cell to "--"
                                         Otherwise it's a row 1 (maybe a dup, maybe not)
  IF($A4=0,                                Is the following row its dup?
    IF(IFERROR(B3="",FALSE),"",B3),          No, set result to the value on this row 
    IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)), Yes, but is this row's value blank or error?
      IF(IFERROR(B4="",FALSE),"",B4),          Yes, use the value from the following row
      IF(IFERROR(B3="",FALSE),"",B3))))        No, use the value from this row

One additional comment on the code: the somewhat roundabout locution IFERROR(<cell address>="",FALSE) is needed to properly screen out the N/A error values in some rows.

Code for the First Row of the Results Table

DUP         =IF(D3=D2,1,0)
FNAME       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(B3="",FALSE),"",B3),IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)),IF(IFERROR(B4="",FALSE),"",B4),IF(IFERROR(B3="",FALSE),"",B3))))
LNAME       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(C3="",FALSE),"",C3),IF(OR(IFERROR(C3="",FALSE),ISERROR(C3)),IF(IFERROR(C4="",FALSE),"",C4),IF(IFERROR(C3="",FALSE),"",C3))))
MEMBER#     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(D3="",FALSE),"",D3),IF(OR(IFERROR(D3="",FALSE),ISERROR(D3)),IF(IFERROR(D4="",FALSE),"",D4),IF(IFERROR(D3="",FALSE),"",D3))))
ENTRY DT    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(E3="",FALSE),"",E3),IF(OR(IFERROR(E3="",FALSE),ISERROR(E3)),IF(IFERROR(E4="",FALSE),"",E4),IF(IFERROR(E3="",FALSE),"",E3))))
ENROL_DT    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(F3="",FALSE),"",F3),IF(OR(IFERROR(F3="",FALSE),ISERROR(F3)),IF(IFERROR(F4="",FALSE),"",F4),IF(IFERROR(F3="",FALSE),"",F3))))
MVP_PTS     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(G3="",FALSE),"",G3),IF(OR(IFERROR(G3="",FALSE),ISERROR(G3)),IF(IFERROR(G4="",FALSE),"",G4),IF(IFERROR(G3="",FALSE),"",G3))))
ENTRIES     =IF($A3=1,"--",IF($A4=0,IF(IFERROR(H3="",FALSE),"",H3),IF(OR(IFERROR(H3="",FALSE),ISERROR(H3)),IF(IFERROR(H4="",FALSE),"",H4),IF(IFERROR(H3="",FALSE),"",H3))))
FORM        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(I3="",FALSE),"",I3),IF(OR(IFERROR(I3="",FALSE),ISERROR(I3)),IF(IFERROR(I4="",FALSE),"",I4),IF(IFERROR(I3="",FALSE),"",I3))))
PTS_CORRECT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(J3="",FALSE),"",J3),IF(OR(IFERROR(J3="",FALSE),ISERROR(J3)),IF(IFERROR(J4="",FALSE),"",J4),IF(IFERROR(J3="",FALSE),"",J3))))
PTS_MISSED  =IF($A3=1,"--",IF($A4=0,IF(IFERROR(K3="",FALSE),"",K3),IF(OR(IFERROR(K3="",FALSE),ISERROR(K3)),IF(IFERROR(K4="",FALSE),"",K4),IF(IFERROR(K3="",FALSE),"",K3))))
FINAL_PTS   =IF($A3=1,"--",IF($A4=0,IF(IFERROR(L3="",FALSE),"",L3),IF(OR(IFERROR(L3="",FALSE),ISERROR(L3)),IF(IFERROR(L4="",FALSE),"",L4),IF(IFERROR(L3="",FALSE),"",L3))))
DR_ENTRIES  =IF($A3=1,"--",IF($A4=0,IF(IFERROR(M3="",FALSE),"",M3),IF(OR(IFERROR(M3="",FALSE),ISERROR(M3)),IF(IFERROR(M4="",FALSE),"",M4),IF(IFERROR(M3="",FALSE),"",M3))))
SP_TALON    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(N3="",FALSE),"",N3),IF(OR(IFERROR(N3="",FALSE),ISERROR(N3)),IF(IFERROR(N4="",FALSE),"",N4),IF(IFERROR(N3="",FALSE),"",N3))))
WP_TALON    =IF($A3=1,"--",IF($A4=0,IF(IFERROR(O3="",FALSE),"",O3),IF(OR(IFERROR(O3="",FALSE),ISERROR(O3)),IF(IFERROR(O4="",FALSE),"",O4),IF(IFERROR(O3="",FALSE),"",O3))))
BD          =IF($A3=1,"--",IF($A4=0,IF(IFERROR(P3="",FALSE),"",P3),IF(OR(IFERROR(P3="",FALSE),ISERROR(P3)),IF(IFERROR(P4="",FALSE),"",P4),IF(IFERROR(P3="",FALSE),"",P3))))
DEPT        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(Q3="",FALSE),"",Q3),IF(OR(IFERROR(Q3="",FALSE),ISERROR(Q3)),IF(IFERROR(Q4="",FALSE),"",Q4),IF(IFERROR(Q3="",FALSE),"",Q3))))
EMPL        =IF($A3=1,"--",IF($A4=0,IF(IFERROR(R3="",FALSE),"",R3),IF(OR(IFERROR(R3="",FALSE),ISERROR(R3)),IF(IFERROR(R4="",FALSE),"",R4),IF(IFERROR(R3="",FALSE),"",R3))))
NOTES       =IF($A3=1,"--",IF($A4=0,IF(IFERROR(S3="",FALSE),"",S3),IF(OR(IFERROR(S3="",FALSE),ISERROR(S3)),IF(IFERROR(S4="",FALSE),"",S4),IF(IFERROR(S3="",FALSE),"",S3))))
DLR         =IF($A3=1,"--",IF($A4=0,IF(IFERROR(T3="",FALSE),"",T3),IF(OR(IFERROR(T3="",FALSE),ISERROR(T3)),IF(IFERROR(T4="",FALSE),"",T4),IF(IFERROR(T3="",FALSE),"",T3))))