Copying cells from one worksheet if their value matches cells in another worksheet

[sharicn]

Hello!

I have Googled and searched the forum here, but I haven't been able to find the exact solution I'm looking for. The context of this request is that I'm doing a database update. The source of the records includes addresses, some of which have firm names in them, some of which do not. Our database has a firm attribute table that I need to match up with all of these records (17,000+) so that everyone gets categorized to their proper firm. I have been doing this by hand, downloading the firm attribute table, sorting the excel file with the records, and copying and pasting the appropriate firm name and firm size into the Excel sheet that I'll import. Here's what the 2 worksheets look like.

Source of records:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Last Name
[/TD]
[TD]First Name
[/TD]
[TD]Address1
[/TD]
[TD]Address2
[/TD]
[TD]City[/TD]
[TD]State
[/TD]
[/TR]
[TR]
[TD]Smith
[/TD]
[TD]Bob
[/TD]
[TD]12345 Main St
[/TD]
[TD][/TD]
[TD]Oregon City
[/TD]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Jones
[/TD]
[TD]Mary
[/TD]
[TD]Mann Mann Klor and Jones
[/TD]
[TD]1212 SW 5th Ave
[/TD]
[TD]Portland
[/TD]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Evans
[/TD]
[TD]Mike
[/TD]
[TD]US Department of Justice
[/TD]
[TD]999 E 5th
[/TD]
[TD]Eugene
[/TD]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Nelson
[/TD]
[TD]Diane
[/TD]
[TD]Stoel Rives
[/TD]
[TD]888 SW 9th
[/TD]
[TD]Portland
[/TD]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Potter
[/TD]
[TD]Carl
[/TD]
[TD]222 Madison Ave
[/TD]
[TD][/TD]
[TD]New York
[/TD]
[TD]NY
[/TD]
[/TR]
</tbody>[/TABLE]

Firm Attribute Table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FIRM
[/TD]
[TD]FIRMSIZE
[/TD]
[/TR]
[TR]
[TD]Anderson Anderson Sm
[/TD]
[TD]Midsize
[/TD]
[/TR]
[TR]
[TD]Mann Mann Klor and Jo
[/TD]
[TD]Large
[/TD]
[/TR]
[TR]
[TD]Stoel Rives
[/TD]
[TD]Large
[/TD]
[/TR]
[TR]
[TD]US Department of Just
[/TD]
[TD]US Govt
[/TD]
[/TR]
</tbody>[/TABLE]

An additional complication, as you can see, is that my database truncates the firm field to 20 characters, so it will not always be an exact match to the address1 field.

So I guess I need something that looks at each address1 field in my import file, compares to see if the first 20 characters match anything in the FIRM column of the firm attribute table and, if so, copies the values in both the FIRM and FIRMSIZE cells next to the cell that it matches. Does that make sense?

I will still nave a bit of cleaning up to do, since there can be spelling errors/inconsistencies when people enter their own address information. But this would save me so much time. I'd be most appreciative if someone could help me work it out.

Best,
Shari

 

[Sam_D_Ben]

will this work.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Last Name	First Name	Address1	Address2	City	State	Firm Size		FIRM	FIRMSIZE
2	Smith	Bob	12345 Main St		Oregon City	OR	#N/A		Anderson Anderson Sm	Midsize
3	Jones	Mary	Mann Mann Klor and Jones	1212 SW 5th Ave	Portland	OR	Large		Mann Mann Klor and Jo	Large
4	Evans	Mike	US Department of Justice	999 E 5th	Eugene	OR	US Govt		Stoel Rives	Large
5	Nelson	Diane	Stoel Rives	888 SW 9th	Portland	OR	Large		US Department of Just	US Govt
6	Potter	Carl	222 Madison Ave		New York	NY	#N/A
Sheet2

Cell Formulas
Range	Formula
G2	=LOOKUP(2^15,SEARCH(LEFT(C2,5)&"*",$I$2:$I$5),$J$2:$J$5)

 

[sharicn]

Thank you for this suggestion! I wasn't thinking that this could be solved with a formula, but this gets me a lot closer than I was before. It's not perfect, but I don't expect that anything will be, given the data I'm working with. I think if I use the formula, re-sort the #NA results, and then adjust the parameters and try the formula again, I can get most of what I need without having to do much by hand.

Much appreciated.
Shari

 

[Sam_D_Ben]

Thanks for the feedback.

I would say there are experts here that could help you with a perfect formula or coding. May be they would have missed this.