Which formula?

ExcelN00B06 · #1 04-17-2006, 04:50 PM

Hi all,

Perhaps you may have heard this question a thousand times but here it is again for the thousand and one time. I have two column of IDs that I am trying to compare and figure out which one is missing.

The IDs look like this:

KD036078 AS070634
MS028846 AA063405
RV009526 AG059891
SG033911 ak071410
AR053757 AP056068
BD028031 AT067547
BP044319 AC054501
DC032030 AP054795
DN047994 AT038118
JD016833 AS048834
JW047209 AS068626
MB030686 AL054618
RS061603 AM040365
SK005695 AM047477
SL020353 AQ039733
VC059131 AR072148
JK038041 AC028342
RO034327 AG063351
HK071317 AP032493
MM050011 TPAO8173
AC057342 AF052939
FK053590 AP035357
HP051837 AB029329

So basically, I would like to match let's say ID KD036078 in the first row to the second row to see first if it exists and then if not, send a flag.

I've tried working with HLOOKUP, VLOOKUP, and MATCH but to no avail. ANY assistance, advice or guidance you can provide is greatly appreciated.

Thanks all,

ExLN00B06

#2 04-17-2006, 05:15 PM

Hi!

Try one of these:

=IF(COUNTIF(B$1:B$23,A1),"OK","Missing")

=IF(ISNUMBER(MATCH(A1,B$1:B$23,0)),"OK","Missing")

Copy down as needed.

Biff

"ExcelN00B06" <ExcelN00B06.26fexh_1145307060.3433@excelforum-nospam.com>
wrote in message
news:ExcelN00B06.26fexh_1145307060.3433@excelforum-nospam.com...
>
> Hi all,
>
> Perhaps you may have heard this question a thousand times but here it
> is again for the thousand and one time. I have two column of IDs that I
> am trying to compare and figure out which one is missing.
>
> The IDs look like this:
>
> KD036078 AS070634
> MS028846 AA063405
> RV009526 AG059891
> SG033911 ak071410
> AR053757 AP056068
> BD028031 AT067547
> BP044319 AC054501
> DC032030 AP054795
> DN047994 AT038118
> JD016833 AS048834
> JW047209 AS068626
> MB030686 AL054618
> RS061603 AM040365
> SK005695 AM047477
> SL020353 AQ039733
> VC059131 AR072148
> JK038041 AC028342
> RO034327 AG063351
> HK071317 AP032493
> MM050011 TPAO8173
> AC057342 AF052939
> FK053590 AP035357
> HP051837 AB029329
>
> So basically, I would like to match let's say ID KD036078 in the first
> row to the second row to see first if it exists and then if not, send a
> flag.
>
> I've tried working with HLOOKUP, VLOOKUP, and MATCH but to no avail.
> ANY assistance, advice or guidance you can provide is greatly
> appreciated.
>
> Thanks all,
>
> ExLN00B06

>
>
> --
> ExcelN00B06
> ------------------------------------------------------------------------
> ExcelN00B06's Profile:
> http://www.excelforum.com/member.php...o&userid=33576
> View this thread: http://www.excelforum.com/showthread...hreadid=533551
>

#3 04-17-2006, 05:40 PM

=IF(COUNTIF($A$1:$A$23,B1)=0,"Flag","")

And fill down.

HTH
Regards,
Howard

"ExcelN00B06" <ExcelN00B06.26fexh_1145307060.3433@excelforum-nospam.com>
wrote in message
news:ExcelN00B06.26fexh_1145307060.3433@excelforum-nospam.com...
>
> Hi all,
>
> Perhaps you may have heard this question a thousand times but here it
> is again for the thousand and one time. I have two column of IDs that I
> am trying to compare and figure out which one is missing.
>
> The IDs look like this:
>
> KD036078 AS070634
> MS028846 AA063405
> RV009526 AG059891
> SG033911 ak071410
> AR053757 AP056068
> BD028031 AT067547
> BP044319 AC054501
> DC032030 AP054795
> DN047994 AT038118
> JD016833 AS048834
> JW047209 AS068626
> MB030686 AL054618
> RS061603 AM040365
> SK005695 AM047477
> SL020353 AQ039733
> VC059131 AR072148
> JK038041 AC028342
> RO034327 AG063351
> HK071317 AP032493
> MM050011 TPAO8173
> AC057342 AF052939
> FK053590 AP035357
> HP051837 AB029329
>
> So basically, I would like to match let's say ID KD036078 in the first
> row to the second row to see first if it exists and then if not, send a
> flag.
>
> I've tried working with HLOOKUP, VLOOKUP, and MATCH but to no avail.
> ANY assistance, advice or guidance you can provide is greatly
> appreciated.
>
> Thanks all,
>
> ExLN00B06

>
>
> --
> ExcelN00B06
> ------------------------------------------------------------------------
> ExcelN00B06's Profile:
> http://www.excelforum.com/member.php...o&userid=33576
> View this thread: http://www.excelforum.com/showthread...hreadid=533551
>

Welcome to the Excel Forum

Forum Statistics