Column A - 2009 Attendee SSNs

Column B - The amount paid by Attendee in 2009Column C - 2010 Attendee SSNs

Column D - The amount paid by Attendee in 2010Column 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?

re: I need Column G to list the information in Column D Line 13.Don't you mean Column D Line

45?

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

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?

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?)?

You don't have to name anything. last2009, last2010, etc. are just variable names.

Range("A" & Rows.Count).End(xlUp).Rowwill return the number of the last row that contains data in Column A. If A35 contains your last piece of data in ColumnA, then

last2009will be set equal to 35.last2009 = Range("A" & Rows.Count).End(xlUp).RowWhenever 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.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History