Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Name Lookup
Vendor Name
ABC Programming ABC Programming ABS Imaging ABC Programming ACP Services ABC Programming What would be the formula that searches this column of vendor names by looking at the first 6 characters/letters and returns a "True" if there is another vendor name that is similar, or a "False" if it is not. I can't put in the exact lookup since it varies from cell to cell. Thanks!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Name Lookup
stephiesunny wrote:
Vendor Name ABC Programming ABC Programming ABS Imaging ABC Programming ACP Services ABC Programming What would be the formula that searches this column of vendor names by looking at the first 6 characters/letters and returns a "True" if there is another vendor name that is similar, or a "False" if it is not. I can't put in the exact lookup since it varies from cell to cell. Thanks!! =SUMPRODUCT(--(LEFT($A$2:$A$7,6)=LEFT(A2,6)))1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Name Lookup
I would use a helper column with formulas like:
=left(a1,6) And then compare the value against this helper column. But you could use an array formula: =ISNUMBER(MATCH(A1,LEFT(B1:B10,6),0)) or maybe: =ISNUMBER(MATCH(LEFT(A1,6),LEFT(B1:B10,6),0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. stephiesunny wrote: Vendor Name ABC Programming ABC Programming ABS Imaging ABC Programming ACP Services ABC Programming What would be the formula that searches this column of vendor names by looking at the first 6 characters/letters and returns a "True" if there is another vendor name that is similar, or a "False" if it is not. I can't put in the exact lookup since it varies from cell to cell. Thanks!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Name Lookup
=SUMPRODUCT(--(LEFT($A$1:$A$100,6)=LEFT(A1,6)))1
"stephiesunny" wrote: Vendor Name ABC Programming ABC Programming ABS Imaging ABC Programming ACP Services ABC Programming What would be the formula that searches this column of vendor names by looking at the first 6 characters/letters and returns a "True" if there is another vendor name that is similar, or a "False" if it is not. I can't put in the exact lookup since it varies from cell to cell. Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet | Excel Worksheet Functions | |||
Add numbers belonging to a vendor | Excel Discussion (Misc queries) | |||
Creating a Vendor ID from Vendor Name... | Excel Worksheet Functions | |||
How do I set up a way to keep track of vendor invoices? | Excel Worksheet Functions | |||
INSERT LINE after Vendor name change | Excel Worksheet Functions |