Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers. Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the €œnew€� (i.e., unique) Project Numbers
that exist in column A relative to column B, and put the results in column C.
Please note that row 1 is used for column headings, so the data contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the Project
Numbers in columns A and B and outputs the unique Project Numbers to column C
(starting in row 2). The macro would know to stop when it encounters the
last Project Number in column A (FYI - there are blank cells after the last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob

Did the code I supplied last time not work or did you need some kind of
modification of it...
--
HTH...

Jim Thomlinson


"Bob" wrote:

Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers. Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the €œnew€� (i.e., unique) Project Numbers
that exist in column A relative to column B, and put the results in column C.
Please note that row 1 is used for column headings, so the data contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the Project
Numbers in columns A and B and outputs the unique Project Numbers to column C
(starting in row 2). The macro would know to stop when it encounters the
last Project Number in column A (FYI - there are blank cells after the last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob

Jim,
I was hoping you would write back. Unfortunately, when I go to Excel's
Tools | Macro | Macros... menu, I could not run your macro because nothing
was displayed in the list. Being a novice VBA programmer, am I missing a
step?
Thanks again for your help.
Bob

"Jim Thomlinson" wrote:

Did the code I supplied last time not work or did you need some kind of
modification of it...
--
HTH...

Jim Thomlinson


"Bob" wrote:

Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers. Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the €œnew€� (i.e., unique) Project Numbers
that exist in column A relative to column B, and put the results in column C.
Please note that row 1 is used for column headings, so the data contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the Project
Numbers in columns A and B and outputs the unique Project Numbers to column C
(starting in row 2). The macro would know to stop when it encounters the
last Project Number in column A (FYI - there are blank cells after the last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob

Jim,
One more thing, the following lines in your code are showing up in red:

Function CreateDictionary(ByVal Target As Range) As

If Not dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty
Then 'Check the key

Bob


"Jim Thomlinson" wrote:

Did the code I supplied last time not work or did you need some kind of
modification of it...
--
HTH...

Jim Thomlinson


"Bob" wrote:

Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers. Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the €œnew€� (i.e., unique) Project Numbers
that exist in column A relative to column B, and put the results in column C.
Please note that row 1 is used for column headings, so the data contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the Project
Numbers in columns A and B and outputs the unique Project Numbers to column C
(starting in row 2). The macro would know to stop when it encounters the
last Project Number in column A (FYI - there are blank cells after the last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob

Bob,

This is because the line has been broken in the post/email:

So one line should read "Private Function CreateDictionary(ByVal Target As
Range) As Scripting.Dictionary" (with NO line breaks)

....and the other single line should read "If Not
dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty Then 'Check the
key" (also with NO line breaks)

It's just something that happens in the text wrapping of emails.

Regarding your other issue:

John,
When I change the first line to "Public" and then run the macro, I get the
following error message:

Compile error:
Sub or Function not defined

VBE then highlights "CreateDictionary" in the line:

Set Dic1 = CreateDictionary(rngRange1)

.....Have you added the reference that Jim mentioned? To do this go to
Tools/References in the programming window (VBE) and find and check the box
for "Microsoft Scripting Runtime", which should be about half way down the
list. Once checked it will move to the top of the list (or very near the
top).

Best regards

John

"Bob" wrote in message
...
Jim,
One more thing, the following lines in your code are showing up in red:

Function CreateDictionary(ByVal Target As Range) As

If Not dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty
Then 'Check the key

Bob


"Jim Thomlinson" wrote:

Did the code I supplied last time not work or did you need some kind of
modification of it...
--
HTH...

Jim Thomlinson


"Bob" wrote:

Columns A & B contain several thousand Project Numbers (e.g., P1052,
PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers.
Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the "new" (i.e., unique) Project
Numbers
that exist in column A relative to column B, and put the results in
column C.
Please note that row 1 is used for column headings, so the data
contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the
Project
Numbers in columns A and B and outputs the unique Project Numbers to
column C
(starting in row 2). The macro would know to stop when it encounters
the
last Project Number in column A (FYI - there are blank cells after the
last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob

John,

Thanks for the info on the line breaks (I should have caught those). Yes, I
added the reference that Jim mentioned. BTW, I noticed an extra EndIf
statement in the code, so I deleted it. Now the macro runs fine without any
error messages, but unfortunately, it's NOT finding any unique entries (even
though several exist)! I'm getting a "No Matches" dialog box.

Any further help would be greatly appreciated.

Thanks again, Bob


"John" wrote:

Bob,

This is because the line has been broken in the post/email:

So one line should read "Private Function CreateDictionary(ByVal Target As
Range) As Scripting.Dictionary" (with NO line breaks)

....and the other single line should read "If Not
dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty Then 'Check the
key" (also with NO line breaks)

It's just something that happens in the text wrapping of emails.

Regarding your other issue:

John,
When I change the first line to "Public" and then run the macro, I get the
following error message:

Compile error:
Sub or Function not defined

VBE then highlights "CreateDictionary" in the line:

Set Dic1 = CreateDictionary(rngRange1)

.....Have you added the reference that Jim mentioned? To do this go to
Tools/References in the programming window (VBE) and find and check the box
for "Microsoft Scripting Runtime", which should be about half way down the
list. Once checked it will move to the top of the list (or very near the
top).

Best regards

John

"Bob" wrote in message
...
Jim,
One more thing, the following lines in your code are showing up in red:

Function CreateDictionary(ByVal Target As Range) As

If Not dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty
Then 'Check the key

Bob


"Jim Thomlinson" wrote:

Did the code I supplied last time not work or did you need some kind of
modification of it...
--
HTH...

Jim Thomlinson


"Bob" wrote:

Columns A & B contain several thousand Project Numbers (e.g., P1052,
PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers.
Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the "new" (i.e., unique) Project
Numbers
that exist in column A relative to column B, and put the results in
column C.
Please note that row 1 is used for column headings, so the data
contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the
Project
Numbers in columns A and B and outputs the unique Project Numbers to
column C
(starting in row 2). The macro would know to stop when it encounters
the
last Project Number in column A (FYI - there are blank cells after the
last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob

Send me an e-mail. I have an addin that incorporrates exactly what you are
looking for (among a bunch of other kind funky stuff). I will send it to
you...
--
HTH...

Jim Thomlinson


"Bob" wrote:

John,

Thanks for the info on the line breaks (I should have caught those). Yes, I
added the reference that Jim mentioned. BTW, I noticed an extra EndIf
statement in the code, so I deleted it. Now the macro runs fine without any
error messages, but unfortunately, it's NOT finding any unique entries (even
though several exist)! I'm getting a "No Matches" dialog box.

Any further help would be greatly appreciated.

Thanks again, Bob


"John" wrote:

Bob,

This is because the line has been broken in the post/email:

So one line should read "Private Function CreateDictionary(ByVal Target As
Range) As Scripting.Dictionary" (with NO line breaks)

....and the other single line should read "If Not
dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty Then 'Check the
key" (also with NO line breaks)

It's just something that happens in the text wrapping of emails.

Regarding your other issue:

John,
When I change the first line to "Public" and then run the macro, I get the
following error message:

Compile error:
Sub or Function not defined

VBE then highlights "CreateDictionary" in the line:

Set Dic1 = CreateDictionary(rngRange1)

.....Have you added the reference that Jim mentioned? To do this go to
Tools/References in the programming window (VBE) and find and check the box
for "Microsoft Scripting Runtime", which should be about half way down the
list. Once checked it will move to the top of the list (or very near the
top).

Best regards

John

"Bob" wrote in message
...
Jim,
One more thing, the following lines in your code are showing up in red:

Function CreateDictionary(ByVal Target As Range) As

If Not dic.Exists(rngCurrent.Value) And rngCurrent.Value < Empty
Then 'Check the key

Bob


"Jim Thomlinson" wrote:

Did the code I supplied last time not work or did you need some kind of
modification of it...
--
HTH...

Jim Thomlinson


"Bob" wrote:

Columns A & B contain several thousand Project Numbers (e.g., P1052,
PA844,
P6721.6, etc.). Column A has the latest list of Project Numbers.
Column B
has an older list of Project Numbers. As such, column B is a subset of
column A. I need to find all of the "new" (i.e., unique) Project
Numbers
that exist in column A relative to column B, and put the results in
column C.
Please note that row 1 is used for column headings, so the data
contained in
columns A and B start in row 2.

I would appreciate any help in writing a macro that compares the
Project
Numbers in columns A and B and outputs the unique Project Numbers to
column C
(starting in row 2). The macro would know to stop when it encounters
the
last Project Number in column A (FYI - there are blank cells after the
last
Project Number in column A, which obviously is the longest of the two
columns).

Thanks for the help.
Bob