Evaluate beginning of cell, compare to list, and return another cell's contents.

[TLKlysen]

I have a workbook with 3 worksheets:

• WS1 has 3 columns (A,B&C). Column A contains a set of numbers and column B contains text, both beginning in Row 2. (Column C needs the formula I'm having trouble writing.)
• WS2 contains 2 columns of text (A&B), both beginning in Row 2.
• WS3 contains 2 columns of text (A&B), both beginning in Row 2.

I need a formula in WS1, column C, that does the following:

1. Determine if cell A2 contains the number 836600;
2. If it does, look in WS2 column A and find a cell that begins with same 6-8 characters that are in WS1, column B;
3. If found, return data in column B of WS2; if not found, return "Misc"

IF A2 does NOT contain 836600, steps 2 and 3 are still followed, but the lookup is on WS3 instead.

Below are examples of each WS, along with what the results in column C SHOULD be.

WS1:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Department[/TD]
[TD]Vendor[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]836600[/TD]
[TD]PRICE CONSTR CO., INC[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]836600[/TD]
[TD]SOUTHERN EQUIP CO INC[/TD]
[TD]SEC[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]835512[/TD]
[TD]SOUTHERN ROPE WORKS[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]836600[/TD]
[TD]SOUTHERN EQUIPMENT COMPANY[/TD]
[TD]SEC[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]836600[/TD]
[TD]PRICE CONSTRUCTION COMPANY[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]827600[/TD]
[TD]KINTECH[/TD]
[TD]Kintech[/TD]
[/TR]
</tbody>[/TABLE]













WS2 (for 836600 lookup):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Vendor[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Southern Equip[/TD]
[TD]SEC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Price Const[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Southern Rope[/TD]
[TD]SRW[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABC Type[/TD]
[TD]ABC[/TD]
[/TR]
</tbody>[/TABLE]

WS3 (for NON-836600 lookup):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Vendor[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Jones Taxi Service[/TD]
[TD]JJ Taxi[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC Type[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Southern Equip[/TD]
[TD]SEC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]KINTECH[/TD]
[TD]Kintech[/TD]
[/TR]
</tbody>[/TABLE]


The other half of this problem is this: the lookup can't be based on the same number of characters that column B begins with. For instance, with Southern Equipment and Southern Rope, I need it to look at least 10 characters from the beginning (to differentiate between the two), but it can't look that far for Kintech, since it's only 7 characters long to begin with.

I currently have something like:
=IF(LEFT(B2,13)="SOUTHERN ROPE","SRW",IF(LEFT(B2,5)="PRICE","PRICE",IF(LEFT(B2,5)="ABC T","ABC"

with an IF statement for each of my companies (less than 20) but this doesn't include the lookup on the two separate worksheets. I tried to use the wildcard (=if(B2="SOUTHERN R*") but couldn't get it to work.

Bottom line, I'm trying to apply a consistent identifier to each company, regardless of how it's listed. i.e., John Doe, Co., Inc. and John Doe Enterprises and John Doe, LLC would all get the identifier of John Doe.

One change I CAN make, if it helps, is to combine WS2 and WS3 adding a column for Department number, so the lookup has to find the rows matching the department number (836600 or otherwise) on WS2 first, then continue as above.

I know this is a lot, but I'm trying to give as clear a picture as I can. Any help would be greatly appreciated!!

 

[István Hirsch]

This is not a complete solution to your question, but may help. The formula compares the several individual names with the name considered to be the reference name. The reference name for a given individual name is chosen by the number of the identical characters in the two, measured from the start of the strings. The reference name is found where that number is the maximum.


Reference names should be entered in the first row, individual names in the first column. Enter the formula in C2 and copy down and across. Then enter the other formula in B2 and copy down.


Column B shows the most probable reference names for the individual names in column A, estimated by the process above.

Excel Workbook
	A	B	C	D	E
1	Individual names/Reference names		SOUTHERN EQUIP CO INC	SOUTHERN ROPE WORKS	PRICE CONSTRUCTION COMPANY
2	Southern Rope	SOUTHERN ROPE WORKS	9	13	0
3	Price Cons	PRICE CONSTRUCTION COMPANY	0	0	10
4	ABC Type	ABC Type	0	0	0
5	Southern E	SOUTHERN EQUIP CO INC	10	9	0
6	Southern Equip	SOUTHERN EQUIP CO INC	14	9	0
7	Price	PRICE CONSTRUCTION COMPANY	0	0	5
8	AB	AB	0	0	0
Sheet4

 

[TLKlysen]

I'm not sure that will serve my needs. Let me further explain my problem and end goal. My initial report (WS1) has tens of thousands of rows. Columns include a department number, a vendor name and an amount. Vendors names will be listed a variety of ways, but typically begin with the same 6-10 letters for each entry. I need to be able to summarize amounts paid to designated primary vendors vs. all others (to be identified as "Misc"). Without a formula, a pivot table lists B G Constr., B G Construction, and B G Construction, Inc., as three separate vendors, thus my problem.

Here is the exact formula I have used in the past based on 23 primary vendors (Column X contains the variations of vendor names or "NA"):

=IF(LEFT(X2,3)="B G","B G",IF(LEFT(X2,8)="BENCHMAR","BENCHMARK",IF(LEFT(X2,8)="BRAKE SU","BRAKE SUPPLY",IF(LEFT(X2,8)="COURTNEY","COURTNEY",IF(LEFT(X2,8)="CROSS CL","CROSS CLEANING",IF(LEFT(X2,8)="CUMMINS ","CUMMINS",IF(LEFT(X2,8)="HOIST & ","HOIST & CRANE",IF(LEFT(X2,8)="HOLT CAT","HOLT",IF(LEFT(X2,8)="JANI-KIN","JANI-KING",IF(LEFT(X2,8)="JOEL WIN","JOEL WINK",IF(LEFT(X2,5)="JOYCE","JOYCE CRANE",IF(LEFT(X2,8)="MINE SER","MINE SERVICES",IF(LEFT(X2,8)="PALMETTO","PALMETTO",IF(LEFT(X2,6)="PIERCE","PIERCE",IF(LEFT(X2,5)="POINT","POINT-2-POINT",IF(LEFT(X2,5)="PRICE","PRICE",IF(LEFT(X2,5)="ROMCO","ROMCO",IF(LEFT(X2,8)="RUTHERFO","RUTHERFORD",IF(LEFT(X2,11)="SOUTHERN CR","SOUTHERN CRANE",IF(LEFT(X2,11)="SOUTHERN TI","SOUTHERN TIRE",IF(LEFT(X2,4)="TODD","TODD'S",IF(LEFT(X2,6)="WARFAB","WARFAB",IF(LEFT(X2,8)="WAUKESHA","WPI",IF(X2="NA","NA","MISC"))))))))))))))))))))))))

This worked as long as all departments had the same designated primary vendors, but now I have a separate vendor list for department 836600 only. I need to somehow incorporate this 2nd list.

My first thought (and I'm fine with this if someone can tell me how), is to keep what I have, add some sort of IF/AND statements for =836600 and <>836600, and add the 2nd set of vendors to the formula. I just couldn't figure out how to work in the department lookup.

My second thought was my original posting: Have each list on a separate worksheet and have the formula look at the department to know which list to refer to, then return the proper result. This solution is preferable only because the lists change annually, so if the first solution is easier, I'm happy to stick with that and just revise to match the new vendors.

The other advantage to the first option is that the names don't ALWAYS begin with the same letters. For instance, Rusk County Electric may be listed as RCEC. If I keep my current formula format, I can add a qualifier for such exceptions.