i have two spreadsheets in the same workbook (yr2005 and yr2007), each with
the columns
a: department B: job title C: over D: current E: to date.

one is for year 2005 and the other is for year 2007.
some of the the departments and positions have changed (some depts and or
job titles no longer exist and some are new) but most of them are the same.
but i need all to be in this spreadsheet without any doubles.

WHAT I NEED IS: a macro that, takes the info on those 2 spreadsheet,
conjugates, and makes one, all inclusive spreadsheet with the columns a:
department B: job title C: over05 D: current05 E: to date05 F: over07 G:
current07 H: to date 07. having only one row per each departments job title
E.G. if its in both (yr2005 and yr 2007) spreadsheets all columns would
have the corresponsing values. if its a new position (didnt appear in yr2005)
columns c d and e would be blank. if its an old position (in yr 2005 but not
in 2007) columns f g and h would be blank.


--
thanks in advance

I'll try to help you with this but one thing is not clear. When you say
"didn't appear in yr2005" or "not in yr2007", what column are you referring
to? Is it the Department? The Job Title? Both?
For coding purposes, what row holds your headers and what row is the first
row of data? What version of Excel are you using? HTH Otto
"jcontrer" wrote in message
...
i have two spreadsheets in the same workbook (yr2005 and yr2007), each with
the columns
a: department B: job title C: over D: current E: to date.

one is for year 2005 and the other is for year 2007.
some of the the departments and positions have changed (some depts and or
job titles no longer exist and some are new) but most of them are the
same.
but i need all to be in this spreadsheet without any doubles.

WHAT I NEED IS: a macro that, takes the info on those 2 spreadsheet,
conjugates, and makes one, all inclusive spreadsheet with the columns a:
department B: job title C: over05 D: current05 E: to date05 F: over07 G:
current07 H: to date 07. having only one row per each departments job
title
E.G. if its in both (yr2005 and yr 2007) spreadsheets all columns would
have the corresponsing values. if its a new position (didnt appear in
yr2005)
columns c d and e would be blank. if its an old position (in yr 2005 but
not
in 2007) columns f g and h would be blank.


--
thanks in advance

The code below makes a copy of the yr2005 worksheet and calls the new sheet
summary. Then it goes through the yr2007 worksheet and checks if an entry
exists or doesn't exist for each dept-job. If it exists it puts the 07 data
in columns F-H. Otherwise it adds a new row, fills in columns A and b with
dept-job, and puts the data in columns F-H.



Sub combine()

'copy 05 worksheet
Worksheets("yr2005").Copy after:=Worksheets(Sheets.Count)
With ActiveSheet
.Name = "Summary"
SumLastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumNewRow = SumLastRow + 1
End With

With Worksheets("yr2007")
RowCount07 = 1
Do While .Range("A" & RowCount07) < ""

Found = False
Dept = .Range("A" & RowCount07)
Job = .Range("B" & RowCount07)
Over07 = .Range("C" & RowCount07)
Current07 = .Range("D" & RowCount07)
Date07 = .Range("E" & RowCount07)

With Sheets("Summary")
For SumRowCount = 1 To (SumNewRow - 1)
If .Range("A" & SumRowCount) = Dept And _
.Range("B" & SumRowCount) = Job Then

Found = True
Exit For
End If
Next SumRowCount
If Found = True Then
.Range("F" & SumRowCount) = Over07
.Range("G" & SumRowCount) = Current07
.Range("H" & SumRowCount) = Date07
Else
.Range("A" & SumNewRow) = Dept
.Range("B" & SumNewRow) = Job
.Range("F" & SumNewRow) = Over07
.Range("G" & SumNewRow) = Current07
.Range("H" & SumNewRow) = Date07
SumNewRow = SumNewRow + 1
End If
End With
RowCount07 = RowCount07 + 1
Loop
End With
End Sub


"jcontrer" wrote:

i have two spreadsheets in the same workbook (yr2005 and yr2007), each with
the columns
a: department B: job title C: over D: current E: to date.

one is for year 2005 and the other is for year 2007.
some of the the departments and positions have changed (some depts and or
job titles no longer exist and some are new) but most of them are the same.
but i need all to be in this spreadsheet without any doubles.

WHAT I NEED IS: a macro that, takes the info on those 2 spreadsheet,
conjugates, and makes one, all inclusive spreadsheet with the columns a:
department B: job title C: over05 D: current05 E: to date05 F: over07 G:
current07 H: to date 07. having only one row per each departments job title
E.G. if its in both (yr2005 and yr 2007) spreadsheets all columns would
have the corresponsing values. if its a new position (didnt appear in yr2005)
columns c d and e would be blank. if its an old position (in yr 2005 but not
in 2007) columns f g and h would be blank.


--
thanks in advance

Joel, everything worked perfectly...
--
thanks in advance


"Joel" wrote:

The code below makes a copy of the yr2005 worksheet and calls the new sheet
summary. Then it goes through the yr2007 worksheet and checks if an entry
exists or doesn't exist for each dept-job. If it exists it puts the 07 data
in columns F-H. Otherwise it adds a new row, fills in columns A and b with
dept-job, and puts the data in columns F-H.



Sub combine()

'copy 05 worksheet
Worksheets("yr2005").Copy after:=Worksheets(Sheets.Count)
With ActiveSheet
.Name = "Summary"
SumLastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumNewRow = SumLastRow + 1
End With

With Worksheets("yr2007")
RowCount07 = 1
Do While .Range("A" & RowCount07) < ""

Found = False
Dept = .Range("A" & RowCount07)
Job = .Range("B" & RowCount07)
Over07 = .Range("C" & RowCount07)
Current07 = .Range("D" & RowCount07)
Date07 = .Range("E" & RowCount07)

With Sheets("Summary")
For SumRowCount = 1 To (SumNewRow - 1)
If .Range("A" & SumRowCount) = Dept And _
.Range("B" & SumRowCount) = Job Then

Found = True
Exit For
End If
Next SumRowCount
If Found = True Then
.Range("F" & SumRowCount) = Over07
.Range("G" & SumRowCount) = Current07
.Range("H" & SumRowCount) = Date07
Else
.Range("A" & SumNewRow) = Dept
.Range("B" & SumNewRow) = Job
.Range("F" & SumNewRow) = Over07
.Range("G" & SumNewRow) = Current07
.Range("H" & SumNewRow) = Date07
SumNewRow = SumNewRow + 1
End If
End With
RowCount07 = RowCount07 + 1
Loop
End With
End Sub


"jcontrer" wrote:

i have two spreadsheets in the same workbook (yr2005 and yr2007), each with
the columns
a: department B: job title C: over D: current E: to date.

one is for year 2005 and the other is for year 2007.
some of the the departments and positions have changed (some depts and or
job titles no longer exist and some are new) but most of them are the same.
but i need all to be in this spreadsheet without any doubles.

WHAT I NEED IS: a macro that, takes the info on those 2 spreadsheet,
conjugates, and makes one, all inclusive spreadsheet with the columns a:
department B: job title C: over05 D: current05 E: to date05 F: over07 G:
current07 H: to date 07. having only one row per each departments job title
E.G. if its in both (yr2005 and yr 2007) spreadsheets all columns would
have the corresponsing values. if its a new position (didnt appear in yr2005)
columns c d and e would be blank. if its an old position (in yr 2005 but not
in 2007) columns f g and h would be blank.


--
thanks in advance