+ Reply to Thread
Results 1 to 7 of 7

Searching for matches from 2 columns and copying data from matches

  1. #1
    Registered User
    Join Date
    08-29-2014
    Location
    St Paul, MN
    MS-Off Ver
    2007
    Posts
    3

    Searching for matches from 2 columns and copying data from matches

    Hi, after searching the net for days without much of a clue exactly what I'm looking for I found this place, a quick look at the first 10 pages started to make my brain explode. hopefully I can get some help.


    What I need to do is search all the cells containing data in column A one at a time and when a match is found in column E copy the data found in E and paste it into a 3rd row.

    The length of the list in column A can vary in size generally about 40-400 rows. Each cell contains unique data being compared to a master list(~100,000) in column E with extra data that is needed.

    The sample attached has before and after sheets named raw and completed.

    Thanks
    -Jesper
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Searching for matches from 2 columns and copying data from matches

    What do you mean by
    "paste it into a 3rd row."
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    08-29-2014
    Location
    St Paul, MN
    MS-Off Ver
    2007
    Posts
    3

    Re: Searching for matches from 2 columns and copying data from matches

    I meant column, my apologies.

    what I'd like is a 3rd column exactly as column A is but with the extra bits of data that column E contains.

    IE if row 1 column A contains abc12345 and a search finds in row 500 of column E abc12345 qxz 884882 ax2a3 I'd like for column I to contain abc12345 qxz 884882 ax2a3 in the same row as column A

  4. #4
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Searching for matches from 2 columns and copying data from matches

    for your data as you posted
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Searching for matches from 2 columns and copying data from matches

    A formula can work
    I1 = INDEX(E$1:E$20,SUM(IF(ISERROR(SEARCH(A1,E$1:E$20)),0,ROW(E$1:E$20))),1)
    Confirm using Control + Shift + Enter for array formula
    then copy down as needed
    Attached Files Attached Files
    Last edited by PCI; 08-30-2014 at 07:08 PM.

  6. #6
    Registered User
    Join Date
    08-29-2014
    Location
    St Paul, MN
    MS-Off Ver
    2007
    Posts
    3

    Re: Searching for matches from 2 columns and copying data from matches

    Tried this when I got into work last night, the macro was a life saver saved us from literally hours of work.

    Though PCI I tried your formula first and for whatever reason it just copied the top lines in order from Column E to the row # A stopped at. Perhaps I typed something wrong but a couple checks I couldn't come up with a typo. (didn't have the option to cut & paste =p) Probably doesn't help that that formula is all but Greek to me. the VBA I could at least understand by reading, hehe.

    I was going to try and figure out how to do this by formula on my own but I wouldn't in 1,000 years come up with anytlhing close that, impressive skills, I thank you both again.

    --Jesper

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Searching for matches from 2 columns and copying data from matches

    Jesper,
    Can you please send a sample of the file with the issue with the formula: This will help me to understand and have a good night.
    Thanks.
    Patrick

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-26-2013, 12:16 PM
  2. Replies: 0
    Last Post: 08-11-2012, 03:25 PM
  3. Searching 2 columns for similiar matches
    By jlonh in forum Excel General
    Replies: 2
    Last Post: 04-12-2012, 03:35 PM
  4. Copying data if it matches criteria
    By yuckysocks in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2008, 11:54 AM
  5. Searching Data with Multiple Matches
    By boylejob in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-11-2007, 12:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1