Finding Unique Entries Among Two Columns

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >> >
> >
> >
> >

Jim,
Thanks for the offer! You can send the add-in to [email protected].
I assume that other users in my group will also need to install the add-in
whenever they use my spreadsheet. True?
Thanks again!
Regards, Bob

"Jim Thomlinson" wrote:

> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >> >
> > >
> > >
> > >