Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
Dear All,
I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
The simpliest approach is to put a simple equation into the paid column
for row 1 =if(counta(B1)1,"Y","N") where column B is the Chq # column. Copy the formula down all rows in the Paid column. "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
in the paid column (assume that is D)
d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
Tom it should either be 0 or =1
"Tom Ogilvy" wrote: in the paid column (assume that is D) d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
It should be as I wrote it. It will count the original invoice, so it should
always be at least 1. -- Regards, Tom Ogilvy "Joel" wrote: Tom it should either be 0 or =1 "Tom Ogilvy" wrote: in the paid column (assume that is D) d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
Tom: 1 doesn't include 1.
"Tom Ogilvy" wrote: It should be as I wrote it. It will count the original invoice, so it should always be at least 1. -- Regards, Tom Ogilvy "Joel" wrote: Tom it should either be 0 or =1 "Tom Ogilvy" wrote: in the paid column (assume that is D) d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
I see the confusion Joel and also see I didn't really explain this completely
or well. If all of column A values are stored as text values, then the formula as written works fine. (and as I envisioned.) If the invoice numbers are alpha or alpha numberic and stored as text or numberic and stored as text then as in the example below A111 A112 A113 A111, A113 then it works as written. =IF(COUNTIF(A:A,"*"&A2&"*")1,"Y","N") If the invoice numbers are numeric and stored as numbers 111 112 113 111,113 then the invoice number itself will not be counted and you are correct that the formula should use 0. =IF(COUNTIF(A:A,"*"&A2&"*")0,"Y","N") However, in this latter case, if only a single invoice is paid with a check, then this will be problematic again if that invoice number is stored as numeric. My advice would be to format Column A as Text and use the original formula. Does that clear it up. (thanks for pointing this out). -- regards, Tom Ogilvy -- Regards, Tom Ogilvy "Joel" wrote: Tom: 1 doesn't include 1. "Tom Ogilvy" wrote: It should be as I wrote it. It will count the original invoice, so it should always be at least 1. -- Regards, Tom Ogilvy "Joel" wrote: Tom it should either be 0 or =1 "Tom Ogilvy" wrote: in the paid column (assume that is D) d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
I don't know if the invoice column is really needed in the formula. I'm not
sure what Lookup means in original posting. Neither Tom's approach or my approach actually performs a LOOKUP! If we are not doing a lookup then why look at column A when just having a check number implies that the item has been paid????? Column A has no value in the process. "Tom Ogilvy" wrote: I see the confusion Joel and also see I didn't really explain this completely or well. If all of column A values are stored as text values, then the formula as written works fine. (and as I envisioned.) If the invoice numbers are alpha or alpha numberic and stored as text or numberic and stored as text then as in the example below A111 A112 A113 A111, A113 then it works as written. =IF(COUNTIF(A:A,"*"&A2&"*")1,"Y","N") If the invoice numbers are numeric and stored as numbers 111 112 113 111,113 then the invoice number itself will not be counted and you are correct that the formula should use 0. =IF(COUNTIF(A:A,"*"&A2&"*")0,"Y","N") However, in this latter case, if only a single invoice is paid with a check, then this will be problematic again if that invoice number is stored as numeric. My advice would be to format Column A as Text and use the original formula. Does that clear it up. (thanks for pointing this out). -- regards, Tom Ogilvy -- Regards, Tom Ogilvy "Joel" wrote: Tom: 1 doesn't include 1. "Tom Ogilvy" wrote: It should be as I wrote it. It will count the original invoice, so it should always be at least 1. -- Regards, Tom Ogilvy "Joel" wrote: Tom it should either be 0 or =1 "Tom Ogilvy" wrote: in the paid column (assume that is D) d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMPLICATED VLOOKUP
On Oct 11, 5:34 pm, Tom Ogilvy
wrote: I see the confusion Joel and also see I didn't really explain this completely or well. If all of column A values are stored as text values, then the formula as written works fine. (and as I envisioned.) If the invoice numbers are alpha or alpha numberic and stored as text or numberic and stored as text then as in the example below A111 A112 A113 A111, A113 then it works as written. =IF(COUNTIF(A:A,"*"&A2&"*")1,"Y","N") If the invoice numbers are numeric and stored as numbers 111 112 113 111,113 then the invoice number itself will not be counted and you are correct that the formula should use 0. =IF(COUNTIF(A:A,"*"&A2&"*")0,"Y","N") However, in this latter case, if only a single invoice is paid with a check, then this will be problematic again if that invoice number is stored as numeric. My advice would be to format Column A as Text and use the original formula. Does that clear it up. (thanks for pointing this out). -- regards, Tom Ogilvy -- Regards, Tom Ogilvy "Joel" wrote: Tom: 1 doesn't include 1. "Tom Ogilvy" wrote: It should be as I wrote it. It will count the original invoice, so it should always be at least 1. -- Regards, Tom Ogilvy "Joel" wrote: Tom it should either be 0 or =1 "Tom Ogilvy" wrote: in the paid column (assume that is D) d2: =if(countif(A:A,"*"&A2&"*")1,"Y","N") This assumes that your invoice numbers are unique. In otherwords if your invoice numbers were 1 5 10 5,10 then the formula in D2 would match the 1 found in the string in 5,10 to the first invoice number (1) which would be incorrect. This isn't a problem if you have invoice numbers like 111 112 113 114 112,114 where one invoice number is not a subset of another invoice number. -- Regards, Tom Ogilvy "shaqil" wrote: Dear All, I have the data in following format: Invoice # Chq # Amount Paid 1 10 2 20 3 30 1,3 987 40 I want to lookup the invoice # which has been paid thru chq and mark "Y" in Paid column. In above example invoice # 1 and 3 has been paid thru Chq # 987 so they should be marked "Y". Can anybody help me? Thnx in advance.- Hide quoted text - - Show quoted text - Thanks a lot, I really want exactly as u explained. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated vlookup/min-max query...please help | Excel Discussion (Misc queries) | |||
a little complicated | Excel Worksheet Functions | |||
Need help with complicated Vlookup, or possibly other function | Excel Worksheet Functions | |||
Complicated VLOOKUP | Excel Programming | |||
Complicated Vlookup/count problem | Excel Worksheet Functions |