Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    May 2010
    Location
    Portland, OR
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 City State
    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

    Firm Attribute Table:

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

    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

  2. #2
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying cells from one worksheet if their value matches cells in another worksheet

    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



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

    Sam_D_Ben

  3. #3
    New Member
    Join Date
    May 2010
    Location
    Portland, OR
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying cells from one worksheet if their value matches cells in another worksheet

    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

  4. #4
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying cells from one worksheet if their value matches cells in another worksheet

    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.
    Sam_D_Ben

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •