I have two sets of data that I am struggling to combine withiout having to do
so manually. The data is from two different years, some names appear in
both lists, some only in one list and not the other (sample below). I need
to combine the two sections of data into one list, eliminate the duplicate
names and combine the data for the duplicated names in to one entry.

first last 07-08 first last 06-07
Connie Aalderink 16 Connie Aalderink 24
Ryan Aalderink 8 Jerry Aardema 27.75
Jerry Aardema 51.5 Mary Abbring 41.5
Reta Ademe 30.75 Reta Ademe 11.5
Adan Aguilar 32 Nikki Afendulis 16
Anthony Aiuppy 24 Adan Aguilar 37.5
Khamtanh Akhavong 31.25 Patricia Aittama 28

What I need to do is if the name appeats in the 06-07 AND 07-08 columns. I
need move it to a new report that combines the data with first name, last
name, 06-07 and 07-08 data so it looks something like the sample below.

I also need to move the data that is unique to one list and not the other
list,

first last 07_08 06_07
Connie Aalderink 16 24
Ryan Aalderink 8
Jerry Aardema 51.5 27.75
Mary Abbring 41.5
Reta Ademe 30.75 11.5
Nikki Afendulis 16
Adan Aguilar 32 37.5
Patricia Aittama 28
Anthony Aiuppy 24
Khamtanh Akhavong 31.25

Have tired several ways around this and not finding a solution that works.

Appreciate any assisstance
--
debra

First of all you need to obtain a unique list of names from both sets,
so insert a new worksheet and copy columns A and B from the existing
sheet into the new sheet, including the headings. Then copy the names
from D2:E2 and below, and paste them immediately below the names you
already have in A and B of the new sheet. Highlight the combined list
of names in the new sheet (including the header row), and then click
on Data | Filter | Advanced Filter. In the pop-up you should choose
Unique Records only, and also Copy to another location, and specify D1
as the destination, and then click OK.

This will give you your unique list in columns D and E, and you may
wish to delete columns A to C as well as sort the names, because the
ones that are unique to the second list will appear near the bottom.

You can then use a VLOOKUP formula to bring the data across to this
new sheet.

Hope this helps.

Pete

On Apr 5, 7:22*pm, debra wrote:
I have two sets of data that I am struggling to combine withiout having to do
so manually. * The data is from two different years, some names appear in
both lists, some only in one list and not the other (sample below). *I need
to combine the two sections of data into one list, eliminate the duplicate
names and combine the data for the duplicated names in to one entry.

first * * * * *last * * * * * 07-08 * * * *first * * * * *last * * * * * 06-07
Connie *Aalderink * * * 16 * * *Connie *Aalderink * * * 24
Ryan * *Aalderink * * * 8 * * * Jerry * Aardema 27.75
Jerry * Aardema 51.5 * *Mary * *Abbring 41.5
Reta * *Ademe * 30.75 * Reta * *Ademe * 11.5
Adan * *Aguilar 32 * * *Nikki * Afendulis * * * 16
Anthony Aiuppy *24 * * *Adan * *Aguilar 37.5
Khamtanh * * * *Akhavong * * * *31.25 * Patricia * * * *Aittama 28

What I need to do is if the name appeats in the 06-07 AND 07-08 columns. *I
need move it to a new report that combines the data with first name, last
name, 06-07 and 07-08 data so it looks something like the sample below. *

I also need to move the data that is unique to one list and not the other
list,

first * last * *07_08 * 06_07
Connie *Aalderink * * * 16 * * *24
Ryan * *Aalderink * * * 8 * * *
Jerry * Aardema 51.5 * *27.75
Mary * *Abbring * * * * 41.5
Reta * *Ademe * 30.75 * 11.5
Nikki * Afendulis * * * * * * * 16
Adan * *Aguilar 32 * * *37.5
Patricia * * * *Aittama * * * * 28
Anthony Aiuppy *24 * * *
Khamtanh * * * *Akhavong * * * *31.25 *

Have tired several ways around this and not finding a solution that works.

Appreciate any assisstance
--
debra

The code below does something similar to Pete'e instructions. the code
assumes the data is in columns A - F

1) Puts Column D & E at the end of Column A & B
2) Puts Column F data in Column D next to the D & E data
3) Sorts data by Last Name then first Name
4) Combine rows with the same First and Last Name

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/6/2008 by jwarburg
'

'
Col_A_LastRow = Range("A" & Rows.Count).End(xlUp).Row
Col_D_LastRow = Range("D" & Rows.Count).End(xlUp).Row
Col_A_NewRow = Col_A_LastRow + 1

'move columns D & E to end of A & B
Range("D2:E" & Col_D_LastRow).Cut _
Destination:=Range("A" & Col_A_NewRow)

'Move column F to end of column D
Range("F2:F" & Col_D_LastRow).Cut _
Destination:=Range("D" & Col_A_NewRow)

'fix header for columns D thru F
Range("F1").Copy Destination:=Range("D1")
Range("E1:F1").Clear

'sort data
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A1:D" & LastRow)

SortRange.Sort _
Key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Key3:=Range("C2"), _
Order3:=xlAscending, _
Header:=xlGuess

'combine rows when names are the same

RowCount = 2
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Range("D" & RowCount) = Range("D" & (RowCount + 1))
Rows(RowCount + 1).Delete
End If
RowCount = RowCount + 1
Loop
End Sub


"Pete_UK" wrote:

First of all you need to obtain a unique list of names from both sets,
so insert a new worksheet and copy columns A and B from the existing
sheet into the new sheet, including the headings. Then copy the names
from D2:E2 and below, and paste them immediately below the names you
already have in A and B of the new sheet. Highlight the combined list
of names in the new sheet (including the header row), and then click
on Data | Filter | Advanced Filter. In the pop-up you should choose
Unique Records only, and also Copy to another location, and specify D1
as the destination, and then click OK.

This will give you your unique list in columns D and E, and you may
wish to delete columns A to C as well as sort the names, because the
ones that are unique to the second list will appear near the bottom.

You can then use a VLOOKUP formula to bring the data across to this
new sheet.

Hope this helps.

Pete

On Apr 5, 7:22 pm, debra wrote:
I have two sets of data that I am struggling to combine withiout having to do
so manually. The data is from two different years, some names appear in
both lists, some only in one list and not the other (sample below). I need
to combine the two sections of data into one list, eliminate the duplicate
names and combine the data for the duplicated names in to one entry.

first last 07-08 first last 06-07
Connie Aalderink 16 Connie Aalderink 24
Ryan Aalderink 8 Jerry Aardema 27.75
Jerry Aardema 51.5 Mary Abbring 41.5
Reta Ademe 30.75 Reta Ademe 11.5
Adan Aguilar 32 Nikki Afendulis 16
Anthony Aiuppy 24 Adan Aguilar 37.5
Khamtanh Akhavong 31.25 Patricia Aittama 28

What I need to do is if the name appeats in the 06-07 AND 07-08 columns. I
need move it to a new report that combines the data with first name, last
name, 06-07 and 07-08 data so it looks something like the sample below.

I also need to move the data that is unique to one list and not the other
list,

first last 07_08 06_07
Connie Aalderink 16 24
Ryan Aalderink 8
Jerry Aardema 51.5 27.75
Mary Abbring 41.5
Reta Ademe 30.75 11.5
Nikki Afendulis 16
Adan Aguilar 32 37.5
Patricia Aittama 28
Anthony Aiuppy 24
Khamtanh Akhavong 31.25

Have tired several ways around this and not finding a solution that works.

Appreciate any assisstance
--
debra