Hi,

I have workbook with two worksheets. Sheet1 pulls projects with activity
and Sheet1 pulls all the projects with and without activity. What I need is
a formula or macro that compares sheet1 to sheet2 and returns the projects
from sheet2 that do not exist in sheet1. I would like to see those right
underneath the list in sheet1. I'm using excel 2007 and the projects format
is text. Thank you in advance.

This macro should do it for you. Change the values of the 4 'Const' declared
values to match the sheet names and column identifiers actually used in your
workbook.

To put the code into your workbook, press [Alt]+[F11] to open the VB Editor,
choose Insert -- Module from its menu. Copy the code below and paste it
into the module presented to you. Make changes to the code as needed. Make
sure you have macros enabled, and run the macro from the Developer tab or as
you may setup already to get to macros easily.

LIMITATION: obviously once it has been run once, all projects from both
sheets are going to be listed on the first sheet. I 'marked' the boundary so
that if you need to refresh the list later, you can delete entries from the
'boundary' entry on down the sheet, run it again, and get an up-to-date list.

Sub ListMissingProjects()
'name of sheet1 with projects & activities
Const s1Name = "Sheet1" ' change as required
'column projects are listed in on sheet1
Const s1PCol = "A" ' change as required
'name of sheet with projects only on it
Const s2Name = "Sheet2" ' change as required
'column on 2nd sheet with projects list
Const s2PCol = "A" ' change as required
Dim s1WS As Worksheet
Dim s1List As Range
Dim anyS1Entry As Range
Dim s2WS As Worksheet
Dim s2List As Range
Dim anyS2Entry As Range
Dim foundFlag As Boolean

'get the address of projects listed on sheet1
Set s1WS = Worksheets(s1Name)
Set s1List = s1WS.Range(s1PCol & "1:" & _
s1WS.Range(s1PCol & Rows.Count).End(xlUp).Address)
'get the address of projects listed on sheet2
Set s2WS = Worksheets(s2Name)
Set s2List = s2WS.Range(s2PCol & "1:" & _
s1WS.Range(s2PCol & Rows.Count).End(xlUp).Address)
'mark the 'boundary'
s1WS.Range(s1PCol & Rows.Count).End(xlUp). _
Offset(1, 0) = "Unlisted Projects from " & s2Name
For Each anyS2Entry In s2List
foundFlag = False
For Each anyS1Entry In s1List
If anyS1Entry = anyS2Entry Then
'it is already in the list
foundFlag = True
Exit For ' out of inner loop
End If
Next
If foundFlag = False Then
'must add to list on sheet1
s1WS.Range(s1PCol & Rows.Count).End(xlUp). _
Offset(1, 0) = anyS2Entry
End If
Next
'good housekeeping
Set s1List = Nothing
Set s2List = Nothing
Set s1WS = Nothing
Set s2WS = Nothing
End Sub


"Gilbert" wrote:

Hi,

I have workbook with two worksheets. Sheet1 pulls projects with activity
and Sheet1 pulls all the projects with and without activity. What I need is
a formula or macro that compares sheet1 to sheet2 and returns the projects
from sheet2 that do not exist in sheet1. I would like to see those right
underneath the list in sheet1. I'm using excel 2007 and the projects format
is text. Thank you in advance.

Worked great JLatham. I appreciate your help.

"JLatham" wrote:

This macro should do it for you. Change the values of the 4 'Const' declared
values to match the sheet names and column identifiers actually used in your
workbook.

To put the code into your workbook, press [Alt]+[F11] to open the VB Editor,
choose Insert -- Module from its menu. Copy the code below and paste it
into the module presented to you. Make changes to the code as needed. Make
sure you have macros enabled, and run the macro from the Developer tab or as
you may setup already to get to macros easily.

LIMITATION: obviously once it has been run once, all projects from both
sheets are going to be listed on the first sheet. I 'marked' the boundary so
that if you need to refresh the list later, you can delete entries from the
'boundary' entry on down the sheet, run it again, and get an up-to-date list.

Sub ListMissingProjects()
'name of sheet1 with projects & activities
Const s1Name = "Sheet1" ' change as required
'column projects are listed in on sheet1
Const s1PCol = "A" ' change as required
'name of sheet with projects only on it
Const s2Name = "Sheet2" ' change as required
'column on 2nd sheet with projects list
Const s2PCol = "A" ' change as required
Dim s1WS As Worksheet
Dim s1List As Range
Dim anyS1Entry As Range
Dim s2WS As Worksheet
Dim s2List As Range
Dim anyS2Entry As Range
Dim foundFlag As Boolean

'get the address of projects listed on sheet1
Set s1WS = Worksheets(s1Name)
Set s1List = s1WS.Range(s1PCol & "1:" & _
s1WS.Range(s1PCol & Rows.Count).End(xlUp).Address)
'get the address of projects listed on sheet2
Set s2WS = Worksheets(s2Name)
Set s2List = s2WS.Range(s2PCol & "1:" & _
s1WS.Range(s2PCol & Rows.Count).End(xlUp).Address)
'mark the 'boundary'
s1WS.Range(s1PCol & Rows.Count).End(xlUp). _
Offset(1, 0) = "Unlisted Projects from " & s2Name
For Each anyS2Entry In s2List
foundFlag = False
For Each anyS1Entry In s1List
If anyS1Entry = anyS2Entry Then
'it is already in the list
foundFlag = True
Exit For ' out of inner loop
End If
Next
If foundFlag = False Then
'must add to list on sheet1
s1WS.Range(s1PCol & Rows.Count).End(xlUp). _
Offset(1, 0) = anyS2Entry
End If
Next
'good housekeeping
Set s1List = Nothing
Set s2List = Nothing
Set s1WS = Nothing
Set s2WS = Nothing
End Sub


"Gilbert" wrote:

Hi,

I have workbook with two worksheets. Sheet1 pulls projects with activity
and Sheet1 pulls all the projects with and without activity. What I need is
a formula or macro that compares sheet1 to sheet2 and returns the projects
from sheet2 that do not exist in sheet1. I would like to see those right
underneath the list in sheet1. I'm using excel 2007 and the projects format
is text. Thank you in advance.