Help! Compare one cell against a whole column, report adjacent cel
 

I have two ODBC queries reporting back to the same worksheet.

In column A, I have "work Order #", it is formatted as "number", sorted
ascending and has 5 preceeding zeros, thanks to the database that creates
it.... so it looks like this:

0000035681

In column D, I have another query returning "invoice number" and it is also
formatted as number and has THREE preceeding zeros, thanks again to the
second database creating the number....so it looks like this:

00035681

*If* I have a number in column D, Column E will have a date.

I need a formula that looks at a cell in column A, looks for the same number
in column D, if it finds it, report the date it finds right next to it in
column E. It has to be able to ignore the leading zeros (or can someone make
me a macro or VBA thingie to delete the leading zeros?)

There are several thousand work order numbers, but only about 1/2 of those
have a matching invoice numbers (column D). So, column A is maybe 4000 rows,
but columns D&E would fill only about 2500 rows. They do NOT correspond in
any way and the query that reports them is dynamic, so one day cell A55 might
show 0000035681 and the next refresh it might show 0000035686. The same
thing for columns D&E.

Can anyone help?

Help! Compare one cell against a whole column, report adjacent cel
 

Usually the leading zeros are there to force a number-like string to have a
certain number of digits. I mention this because you state that columns A
and D have a specific number of leading zeros rather than a specific number
of characters.

If you are confident that you simply need to check for column A's value LESS
THE FIRST 2 ZEROS, then use

=VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0)

If you need to use 8 characters (instead of simple dropping the 2 leading
zeros), then use

=VLOOKUP(RIGHT(A2,8),$D$2:$E$4000,2,0)



"Spam spam bacon spam" wrote:

I have two ODBC queries reporting back to the same worksheet.

In column A, I have "work Order #", it is formatted as "number", sorted
ascending and has 5 preceeding zeros, thanks to the database that creates
it.... so it looks like this:

0000035681

In column D, I have another query returning "invoice number" and it is also
formatted as number and has THREE preceeding zeros, thanks again to the
second database creating the number....so it looks like this:

00035681

*If* I have a number in column D, Column E will have a date.

I need a formula that looks at a cell in column A, looks for the same number
in column D, if it finds it, report the date it finds right next to it in
column E. It has to be able to ignore the leading zeros (or can someone make
me a macro or VBA thingie to delete the leading zeros?)

There are several thousand work order numbers, but only about 1/2 of those
have a matching invoice numbers (column D). So, column A is maybe 4000 rows,
but columns D&E would fill only about 2500 rows. They do NOT correspond in
any way and the query that reports them is dynamic, so one day cell A55 might
show 0000035681 and the next refresh it might show 0000035686. The same
thing for columns D&E.

Can anyone help?

Help! Compare one cell against a whole column, report adjacent
 

Dude?

you are my hero :)

I used the first formula.
It reports "N/A" for ones without a match.... can I change that to say
"missing"?


"Duke Carey" wrote:

Usually the leading zeros are there to force a number-like string to have a
certain number of digits. I mention this because you state that columns A
and D have a specific number of leading zeros rather than a specific number
of characters.

If you are confident that you simply need to check for column A's value LESS
THE FIRST 2 ZEROS, then use

=VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0)

If you need to use 8 characters (instead of simple dropping the 2 leading
zeros), then use

=VLOOKUP(RIGHT(A2,8),$D$2:$E$4000,2,0)



"Spam spam bacon spam" wrote:

I have two ODBC queries reporting back to the same worksheet.

In column A, I have "work Order #", it is formatted as "number", sorted
ascending and has 5 preceeding zeros, thanks to the database that creates
it.... so it looks like this:

0000035681

In column D, I have another query returning "invoice number" and it is also
formatted as number and has THREE preceeding zeros, thanks again to the
second database creating the number....so it looks like this:

00035681

*If* I have a number in column D, Column E will have a date.

I need a formula that looks at a cell in column A, looks for the same number
in column D, if it finds it, report the date it finds right next to it in
column E. It has to be able to ignore the leading zeros (or can someone make
me a macro or VBA thingie to delete the leading zeros?)

There are several thousand work order numbers, but only about 1/2 of those
have a matching invoice numbers (column D). So, column A is maybe 4000 rows,
but columns D&E would fill only about 2500 rows. They do NOT correspond in
any way and the query that reports them is dynamic, so one day cell A55 might
show 0000035681 and the next refresh it might show 0000035686. The same
thing for columns D&E.

Can anyone help?

Help! Compare one cell against a whole column, report adjacent
 

try

=if(iserror(VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0)),"Missing",VLOOKUP(RIGHT(A2,L EN(A2)-2),$D$2:$E$4000,2,0))

if you have Excel 2007, use

=iferror(VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0),"missing")



"Spam spam bacon spam" wrote:

Dude?

you are my hero :)

I used the first formula.
It reports "N/A" for ones without a match.... can I change that to say
"missing"?


"Duke Carey" wrote:

Usually the leading zeros are there to force a number-like string to have a
certain number of digits. I mention this because you state that columns A
and D have a specific number of leading zeros rather than a specific number
of characters.

If you are confident that you simply need to check for column A's value LESS
THE FIRST 2 ZEROS, then use

=VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0)

If you need to use 8 characters (instead of simple dropping the 2 leading
zeros), then use

=VLOOKUP(RIGHT(A2,8),$D$2:$E$4000,2,0)



"Spam spam bacon spam" wrote:

I have two ODBC queries reporting back to the same worksheet.

In column A, I have "work Order #", it is formatted as "number", sorted
ascending and has 5 preceeding zeros, thanks to the database that creates
it.... so it looks like this:

0000035681

In column D, I have another query returning "invoice number" and it is also
formatted as number and has THREE preceeding zeros, thanks again to the
second database creating the number....so it looks like this:

00035681

*If* I have a number in column D, Column E will have a date.

I need a formula that looks at a cell in column A, looks for the same number
in column D, if it finds it, report the date it finds right next to it in
column E. It has to be able to ignore the leading zeros (or can someone make
me a macro or VBA thingie to delete the leading zeros?)

There are several thousand work order numbers, but only about 1/2 of those
have a matching invoice numbers (column D). So, column A is maybe 4000 rows,
but columns D&E would fill only about 2500 rows. They do NOT correspond in
any way and the query that reports them is dynamic, so one day cell A55 might
show 0000035681 and the next refresh it might show 0000035686. The same
thing for columns D&E.

Can anyone help?

Help! Compare one cell against a whole column, report adjacent
 

Duke,

Please check to make sure there is an "S" on your chest.... if there isn't,
I will put one there. Again, you are my hero. Worked like a charm.

Is there a place on the web you can suggest I can go to learn more? I
understand a LOT of what you wrote, but not all... and it seems I could do a
lot more if I had more skills...

Thanks!

"Duke Carey" wrote:

try

=if(iserror(VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0)),"Missing",VLOOKUP(RIGHT(A2,L EN(A2)-2),$D$2:$E$4000,2,0))

if you have Excel 2007, use

=iferror(VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0),"missing")



"Spam spam bacon spam" wrote:

Dude?

you are my hero :)

I used the first formula.
It reports "N/A" for ones without a match.... can I change that to say
"missing"?


"Duke Carey" wrote:

Usually the leading zeros are there to force a number-like string to have a
certain number of digits. I mention this because you state that columns A
and D have a specific number of leading zeros rather than a specific number
of characters.

If you are confident that you simply need to check for column A's value LESS
THE FIRST 2 ZEROS, then use

=VLOOKUP(RIGHT(A2,LEN(A2)-2),$D$2:$E$4000,2,0)

If you need to use 8 characters (instead of simple dropping the 2 leading
zeros), then use

=VLOOKUP(RIGHT(A2,8),$D$2:$E$4000,2,0)



"Spam spam bacon spam" wrote:

I have two ODBC queries reporting back to the same worksheet.

In column A, I have "work Order #", it is formatted as "number", sorted
ascending and has 5 preceeding zeros, thanks to the database that creates
it.... so it looks like this:

0000035681

In column D, I have another query returning "invoice number" and it is also
formatted as number and has THREE preceeding zeros, thanks again to the
second database creating the number....so it looks like this:

00035681

*If* I have a number in column D, Column E will have a date.

I need a formula that looks at a cell in column A, looks for the same number
in column D, if it finds it, report the date it finds right next to it in
column E. It has to be able to ignore the leading zeros (or can someone make
me a macro or VBA thingie to delete the leading zeros?)

There are several thousand work order numbers, but only about 1/2 of those
have a matching invoice numbers (column D). So, column A is maybe 4000 rows,
but columns D&E would fill only about 2500 rows. They do NOT correspond in
any way and the query that reports them is dynamic, so one day cell A55 might
show 0000035681 and the next refresh it might show 0000035686. The same
thing for columns D&E.

Can anyone help?