+ Reply to Thread
Results 1 to 10 of 10

Search to find matching cells and copy/transpose adjacent data to original sheet?

  1. #1
    Registered User
    Join Date
    11-02-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Cool Search to find matching cells and copy/transpose adjacent data to original sheet?

    Hello,

    I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.

    As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.

    If it's any help, there are a maximum of 9 matches for a single search term in the real document.

    Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!

    Best,
    Ian
    Attached Files Attached Files
    Last edited by thump4r; 11-12-2009 at 09:20 PM. Reason: solved.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Try this:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-02-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    HiJBeaucaire,

    Thanks for your help!

    Unfortunately this subroutine doesn't seem to work... after receiving a runtime error 9 (subscript out of range), I debugged the script and changed:


    With Sheets("sheet 1 data")

    to

    With Sheets("data")


    This allowed the script to run without error, but nothing happened... no data was copied between sheets. The secondary data columns on the 'list of search criteria' sheet remained empty.

    Any thoughts? Thanks again for your help

    Best,
    Ian

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Well, if your sheetname didn't match the example sheet you posted, perhaps other things don't match? Post up the sheet where you've installed the macro and it doesn't work as designed and I can compare directly.

  5. #5
    Registered User
    Join Date
    11-02-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Hi again JBeaucaire,

    I've attached the original example sheet with the subroutine TransposeSecondaryValues installed. The installed iteration of the code is exactly as posted in your first response.

    Hopefully that can help shed some light on the matter

    Thanks again for your time!

    Cheers,
    Ian
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Oops. Forgot to tell you. Remove that blank row2 on the data sheet.

    We're using Autofilter and having a blank row at the top basically breaks the autofilter's ability to see the data as contiguous.

  7. #7
    Registered User
    Join Date
    11-02-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Ahhh eureka! Works perfectly.

    Also, I just realized that the example sheet I originally posted did not match the version I installed the macro in.... mea culpa. Sorry for giving you the runaround.

    Thanks for your patience, and thanks for a great solution

    Best,
    Ian

  8. #8
    Registered User
    Join Date
    11-02-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Question Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Ok... I got a little ahead of myself and realize now that the search criteria I previously decided to use is not thorough enough for my needs. Sorry JBeaucaire...!

    I've attached an updated example sheet.

    Basically it's exactly the same problem as posed earlier but with additional search criteria.

    I want to satisfy 3 different search criteria at the same time, as seen on the sheet named 'criteria and results'. If these 3 search criteria match the 3 data sets on the 'data' sheet, then i'd like the values in the column named 'data set 4' to be copied and transposed into the 'matched data x' columns found on the 'criteria and results' sheet.

    Hope that makes sense... sorry for being so demanding :\

    Best,
    Ian
    Attached Files Attached Files

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Fortunately, Autofilter is designed to allow sequential filtering, so this only required a couple of additional lines of code...plus editing the original code to match your changing layout.
    Please Login or Register  to view this content.
    You can put an asterisk in a cell for the search2 or search3 criteria to get it to match all values.
    Last edited by JBeaucaire; 11-12-2009 at 08:01 PM.

  10. #10
    Registered User
    Join Date
    11-02-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Smile Re: Search to find matching cells and copy/transpose adjacent data to original sheet?

    Thank you once again JBeaucaire, works like a charm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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