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

sharicn

New Member
Joined
May 24, 2010
Messages
32
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:

Last Name
First Name
Address1
Address2
CityState
Smith
Bob
12345 Main St
Oregon City
OR
Jones
Mary
Mann Mann Klor and Jones
1212 SW 5th Ave
Portland
OR
Evans
Mike
US Department of Justice
999 E 5th
Eugene
OR
Nelson
Diane
Stoel Rives
888 SW 9th
Portland
OR
Potter
Carl
222 Madison Ave
New York
NY

<tbody>
</tbody>

Firm Attribute Table:

FIRM
FIRMSIZE
Anderson Anderson Sm
Midsize
Mann Mann Klor and Jo
Large
Stoel Rives
Large
US Department of Just
US Govt

<tbody>
</tbody>

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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
will this work.


Excel 2013/2016
ABCDEFGHIJ
1Last NameFirst NameAddress1Address2CityStateFirm SizeFIRMFIRMSIZE
2SmithBob12345 Main StOregon CityOR#N/AAnderson Anderson SmMidsize
3JonesMaryMann Mann Klor and Jones1212 SW 5th AvePortlandORLargeMann Mann Klor and JoLarge
4EvansMikeUS Department of Justice999 E 5thEugeneORUS GovtStoel RivesLarge
5NelsonDianeStoel Rives888 SW 9thPortlandORLargeUS Department of JustUS Govt
6PotterCarl222 Madison AveNew YorkNY#N/A
Sheet2
Cell Formulas
RangeFormula
G2=LOOKUP(2^15,SEARCH(LEFT(C2,5)&"*",$I$2:$I$5),$J$2:$J$5)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top