Removing duplicates and consolidating data

Microsoft Microsoft excel 2000
March 29, 2010 at 15:25:19
Specs: Windows XP
Column A - 2009 Attendee SSNs
Column B - The amount paid by Attendee in 2009

Column C - 2010 Attendee SSNs
Column D - The amount paid by Attendee in 2010

Column A and Column C have some overlapping individuals, but, Column A is longer than Column C, and some people who attended in Column A 2009, did not attend in Column C 2010

I need one list, either on Column E or on a separate tab, that has one consolidated list of all attendees (2009 and 2010) with no duplicates; but for the people who attended in both 2009 and 2010, tell me how much they paid in both years

So, the new list takes up Columns E, F, and G; where E is the individual's SSN; F is the amount they paid in 2009, and G is the amount they paid in 2010

So, if Column A Line 13 matches Column C Line 45; I need Column E to list the information from Column A LIne 13 (which is the same information from Column C line 45); and I need Column F to list the information in Column B line 13 and I need Column G to list the information in Column D Line 13.

Is there anyway to do this in an automated way?


See More: Removing duplicates and consolidating data

Report •

#1
March 29, 2010 at 15:49:43
re: I need Column G to list the information in Column D Line 13.

Don't you mean Column D Line 45?


Report •

#2
March 29, 2010 at 16:16:56
Assuming your lists start in Row 1, give this a try...

Option Explicit
Sub AllAttendees()
Dim last2009, last2010, nxtRw, ssn, lastBoth As Integer
'Find bottom of list in Column A
  last2009 = Range("A" & Rows.Count).End(xlUp).Row
'Copy all 2009 Attendees and price to E:F
    Range("A1:B" & last2009).Copy Destination:=Range("e1")
'Find Bottom of list in Column C
  last2010 = Range("C" & Rows.Count).End(xlUp).Row
'Search for each SSN in Column E
   With Range("E1:E" & last2009)
    For nxtRw = 1 To last2010
     Set ssn = .Find(Range("C" & nxtRw), lookat:=xlWhole)
'If found, then place 2010 price in Column G
      If Not ssn Is Nothing Then
       Range("G" & ssn.Row) = Range("D" & nxtRw)
'If not found...
      Else
'Find bottom of list in Column E
       lastBoth = Range("E" & Rows.Count).End(xlUp).Row + 1
'Place SSN in E and 2010 Price in G
       Range("E" & lastBoth) = Range("C" & nxtRw)
       Range("G" & lastBoth) = Range("D" & nxtRw)
      End If
    Next
   End With
 End Sub


Report •

#3
March 29, 2010 at 17:46:17
You seem like you really know what you're doing/talking about... how do I get to the macro/coding page in Excel where I can put in the formula?

Report •

Related Solutions

#4
March 29, 2010 at 17:48:12
And, if I use this verbatim, what do I name the columns? Do I need to name column A, "last2009" or just "2009" (so last is an operator?)?

Report •

#5
March 29, 2010 at 18:20:13
You don't have to name anything.

last2009, last2010, etc. are just variable names.

Range("A" & Rows.Count).End(xlUp).Row

will return the number of the last row that contains data in Column A. If A35 contains your last piece of data in ColumnA, then last2009 will be set equal to 35.

last2009 = Range("A" & Rows.Count).End(xlUp).Row

Whenever the code sees last2009, it will consider it to be 35.

e.g. Range("A1:B" & last2009) will evaluate to Range("A1:B35")

I used last2009 to remind myself that it will be set equal to the row number for the last piece of data in the 2009 SSN list. I could just as easily have used X or rngRow or Fred.

To use the code you need to open the VBA editor. A simple way would be to press Alt-F11, Click Insert...Module and paste the code into the pane that opens.

I strongly suggest that you try this in a backup copy of your workbook. Macros can not be undone.


Report •

Ask Question