+ Reply to Thread
Results 1 to 5 of 5

Extracting data from one column based on value from an adjacent column

  1. #1
    Registered User
    Join Date
    09-28-2012
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    5

    Extracting data from one column based on value from an adjacent column

    Hello Forum

    I could use some help figuring out a problem where I need to get some data into a different format for use by a certain program.

    If you download the attachment, you will see an example of what I need it to look like in R5CL thru N.

    The idea is to retrieve any values from column J if column I matches M5; and display those values, separated by a space, in a linear fashion, in cell N5.

    This is just a sample of the data that will be converted, so manual(ie copy, paste, man. selection,etc..) actions won't help me much. I'm trying to automate it using formulas as much as possible.

    Thanks in advance for your help!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-10-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Extracting data from one column based on value from an adjacent column

    Hi There

    if column J matches M5, what data needs to be shown?

    as you can use =if(I7=$M$5,J7,"")

  3. #3
    Registered User
    Join Date
    09-28-2012
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Extracting data from one column based on value from an adjacent column

    Thanks for your reply Martinp,

    The challenge is to get the all of the values from from J, where the value of I is equal to M5 in the same row, displayed in a linear format in Row 5. I hand typed an example of the desired format in N5. I am not paticular wether it is all in one cell or multiple cells, but it does need to be linear instead of columnar. The TRANSPOSE function works but the only way I can find to use it is to go through the data an manually select what I want transposed. As indicated before, this is just a small sample of the data, the files will be much bigger. The more autonomy achieved the better.

    Thanks again for your help, I REALLY appreciate it!

    Jon

  4. #4
    Registered User
    Join Date
    09-10-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Extracting data from one column based on value from an adjacent column

    Right, I think I've got it, want to point out that I'm still learning the VBA side so there are ways to make the macros a bit better, and do what I've done all in a macro.

    What it does is it puts an IF formula in cell K5 which if the number in I matches M5 it will display the data in column J. It then copies this down for the rest of the data. (it only goes to row 241, but a quick edit in the vb you could put to 2000 for example, I don't yet know how to make it only go to the end of the data, hopefully I'll figure this out in the next week and maybe get back to you?)

    Then it copies all the data, then pastes as values and at the same time transposing and skipping any blanks

    Is this what you're after? I think with a couple of additions, maybe so the formula is done alone on vba and not on the sheet, and going down a set amount of rows then you have it nailed.

    Hope this has helped a bit

    Martin

    A131CrossSec7StaOffset (1)form#.xlsm

  5. #5
    Registered User
    Join Date
    09-28-2012
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Extracting data from one column based on value from an adjacent column

    Thanks Martin,That is definitely a step closer, but it seems I would have to create the macro for every subsequent string of data I need to transpose. If you continue down the page this step will need to be repeated over and over. Thoughts?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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