Compare values in one column against another and display differenc

[cpetta]

How can I compare a master part numbers list in one Excel column or worksheet
against actual values in a second Excel column or worksheet, and display the
missing part numbers that were not in the second column in a new column or
worksheet?

For Example
Part Number Master Part List Missing Parts
12A221315 12A221315 12A221332
12A221316 12A221316 12A221333
12A221317 12A221317
12A221318 12A221318
12A221319 12A221319
12A221320 12A221320
12A221321 12A221321
12A221322 12A221322
12A221323 12A221323
12A221324 12A221324
12A221325 12A221325
12A221326 12A221326
12A221327 12A221327
12A221328 12A221328
12A221329 12A221329
12A221330 12A221330
12A221331 12A221331
12A221316 12A221332
12A221315 12A221333

[Biff]

See reply in .Functions

Biff

>-----Original Message-----
>How can I compare a master part numbers list in one Excel
column or worksheet
>against actual values in a second Excel column or
worksheet, and display the
>missing part numbers that were not in the second column
in a new column or
>worksheet?
>
>For Example
>Part Number Master Part List Missing Parts
>12A221315 12A221315 12A221332
>12A221316 12A221316 12A221333
>12A221317 12A221317
>12A221318 12A221318
>12A221319 12A221319
>12A221320 12A221320
>12A221321 12A221321
>12A221322 12A221322
>12A221323 12A221323
>12A221324 12A221324
>12A221325 12A221325
>12A221326 12A221326
>12A221327 12A221327
>12A221328 12A221328
>12A221329 12A221329
>12A221330 12A221330
>12A221331 12A221331
>12A221316 12A221332
>12A221315 12A221333
>
>.
>

[Max]

One non-array approach, "inefficient" but imo, easily implemented <g>

Assuming the comparison is col B's items against col A's (Master), with data
in row2 down

Put in C2:
=IF(ISERROR(SMALL(D:D,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROWS($A$1
:A1)),D:D,0)))

Put in D2:
=IF(COUNTIF(A:A,B2)<1,ROW(),"")

Select B2:C2 and copy down as many rows as there is data in col B

Col C will list the items in col B not found in col A

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"cpetta" <[email protected]> wrote in message
news:[email protected]...
> How can I compare a master part numbers list in one Excel column or
worksheet
> against actual values in a second Excel column or worksheet, and display
the
> missing part numbers that were not in the second column in a new column or
> worksheet?
>
> For Example
> Part Number Master Part List Missing Parts
> 12A221315 12A221315 12A221332
> 12A221316 12A221316 12A221333
> 12A221317 12A221317
> 12A221318 12A221318
> 12A221319 12A221319
> 12A221320 12A221320
> 12A221321 12A221321
> 12A221322 12A221322
> 12A221323 12A221323
> 12A221324 12A221324
> 12A221325 12A221325
> 12A221326 12A221326
> 12A221327 12A221327
> 12A221328 12A221328
> 12A221329 12A221329
> 12A221330 12A221330
> 12A221331 12A221331
> 12A221316 12A221332
> 12A221315 12A221333
>

[cpetta]

Max Thank you! Where do I insert this non-array approach code in my worksheet?

"Max" wrote:

> One non-array approach, "inefficient" but imo, easily implemented <g>
>
> Assuming the comparison is col B's items against col A's (Master), with data
> in row2 down
>
> Put in C2:
> =IF(ISERROR(SMALL(D:D,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROWS($A$1
> :A1)),D:D,0)))
>
> Put in D2:
> =IF(COUNTIF(A:A,B2)<1,ROW(),"")
>
> Select B2:C2 and copy down as many rows as there is data in col B
>
> Col C will list the items in col B not found in col A
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "cpetta" <[email protected]> wrote in message
> news:[email protected]...
> > How can I compare a master part numbers list in one Excel column or
> worksheet
> > against actual values in a second Excel column or worksheet, and display
> the
> > missing part numbers that were not in the second column in a new column or
> > worksheet?
> >
> > For Example
> > Part Number Master Part List Missing Parts
> > 12A221315 12A221315 12A221332
> > 12A221316 12A221316 12A221333
> > 12A221317 12A221317
> > 12A221318 12A221318
> > 12A221319 12A221319
> > 12A221320 12A221320
> > 12A221321 12A221321
> > 12A221322 12A221322
> > 12A221323 12A221323
> > 12A221324 12A221324
> > 12A221325 12A221325
> > 12A221326 12A221326
> > 12A221327 12A221327
> > 12A221328 12A221328
> > 12A221329 12A221329
> > 12A221330 12A221330
> > 12A221331 12A221331
> > 12A221316 12A221332
> > 12A221315 12A221333
> >
>
>
>

[Max]

"cpetta" <[email protected]> wrote
> Max Thank you! Where do I insert this non-array approach code in my
worksheet?

> Part Number Master Part List Missing Parts
> 12A221315 12A221315 12A221332
> 12A221316 12A221316 12A221333
> 12A221317 12A221317
> 12A221318 12A221318
> 12A221319 12A221319
etc

Hi, I had assumed that your "Part Number Master" is in col A, "Part List"
in col B, with data from row2 down

With your label "Missing Parts" in C1,
just put (i.e. copy and paste) the suggested formulas into C2 and D2 as
mentioned, then select C2:D2, and copy down until the last item in col B

You'll get the results, viz. the missing parts listed in col C, in C2 down

Try it again ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[cpetta]

It worked. Thanks Max.

Part Number Master Part List
12A221315 12A221315 12A221332
12A221316 12A221316 12A221333
12A221317 12A221317
12A221318 12A221318
12A221319 12A221319
12A221320 12A221320
12A221321 12A221321
12A221322 12A221322
12A221323 12A221323
12A221324 12A221324
12A221325 12A221325
12A221326 12A221326
12A221327 12A221327
12A221328 12A221328
12A221329 12A221329
12A221330 12A221330
12A221331 12A221331
12A221316 12A221332
12A221315 12A221333

If I have a question again, how can I reach you?

"Max" wrote:

> "cpetta" <[email protected]> wrote
> > Max Thank you! Where do I insert this non-array approach code in my
> worksheet?
>
> > Part Number Master Part List Missing Parts
> > 12A221315 12A221315 12A221332
> > 12A221316 12A221316 12A221333
> > 12A221317 12A221317
> > 12A221318 12A221318
> > 12A221319 12A221319
> etc
>
> Hi, I had assumed that your "Part Number Master" is in col A, "Part List"
> in col B, with data from row2 down
>
> With your label "Missing Parts" in C1,
> just put (i.e. copy and paste) the suggested formulas into C2 and D2 as
> mentioned, then select C2:D2, and copy down until the last item in col B
>
> You'll get the results, viz. the missing parts listed in col C, in C2 down
>
> Try it again ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

[Max]

"cpetta" <[email protected]> wrote
> It worked. Thanks Max.

Glad to hear that !

> If I have a question again, how can I reach you?

Just post your Qs* in the excel newsgroups. And I'm sure your posts,
(clearly described with sufficient detail, of course <g>) will receive good
and fast responses from the *many* experienced, helpful folks who linger
around these excel newsgroups. Monitor your posts and provide feedback to
all who respond to your posts. Cheers

*But do not multi-post, post a particular Q *only* in one newsgroup

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[mariposa]

Hi max

i have read your reply to cpetta and i was really glad that when i used the formula in my case, it works! great job! and thank you so so much...Now, i am stuck with how can i do this another tedious and hard excel problem i dealt with. How can i compare a master list in Sheet1 against actual values in Sheet 2 and with a result of highlighting the values that are in Sheet 2 in "Sheet 1".I hope i am clear...Sorry for my poor English. I will try this way; whatever values i have in sheet 2 that are found in Sheet 1-it should be highlighted (in any color) in Sheet1. I have attach a sample file. Please help me when you can. I would be very very thankful to you.

Im new to this site, I am actually learning how to post and be registered...and this is the best way so far that i think i can do to ask help from you, the experts.

best regards,
mariposa