+ Reply to Thread
Results 1 to 16 of 16

Find corresponding values from different cells

  1. #1
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Find corresponding values from different cells

    I have multiple values in cells different columns but in same row e.g.

    1;2;3;4;5 and a;b;c;d;e

    I would like to separate them to one value in each cell in different rows but keep relationship with corresponding value in next column....So the data above will show up in two different column but 5 different rows

    1 a
    2 b
    3 c
    4 d
    5 e

    Thanks for your help

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Find corresponding values from different cells

    So are you saying 1;2;3;4;5 is in A1:E1 and a;b;c;d;e is in F1:J1

    You want those in A1:A5 and B1:B5?
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    No

    I have A1 - 1;2;3;4;5 and

    B1 is a;b;c;d;e

    I would like to get A1:A5 - 1,2,3,4,5

    and B1:B5 -a,b,c,d,e

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Find corresponding values from different cells

    cut /paste b1 to a2
    data /text to columns /delimited /pick semi colon /click finish
    copy a1:b5 paste special into a3 transpose
    delete rows 1 and 2
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    Thanks...but it won't work....I have 38000 cells in column A and B

    each semi-colon separated value has relationship so I would like to maintain corresponding relationship.

    I am attaching a sample file.
    Attached Files Attached Files
    Last edited by sd_dude; 06-03-2012 at 05:54 PM.

  6. #6
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    Someone please help!

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Find corresponding values from different cells

    I have put a call out to those much smarter than I...

  8. #8
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    thanks a lot.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Find corresponding values from different cells

    One way:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Find corresponding values from different cells

    Try the attached with Helper columns.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  11. #11
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    Shg....

    This one did not work.

    It separated the values in column A and Column B but did not maintain the relationship between the corresponding values in the cells.

    Thanks......If you can modify I will try again.
    Last edited by sd_dude; 06-03-2012 at 09:34 PM.

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Find corresponding values from different cells

    what's supposed to happen if there are different numbers of items in each column? gaps in the other column?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

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

    Re: Find corresponding values from different cells

    Here's another way, with 38k rows might not be all that fast...

    Also, your sample data has 5 elements in column A and 6 in column B, FYI.

    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!)

  14. #14
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    Josephp,

    Gaps is only way to go...so I need to always stick with highest number of elements.

  15. #15
    Registered User
    Join Date
    03-19-2012
    Location
    LA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find corresponding values from different cells

    JBeaucaire,

    Thanks for your help

    It is giving me error in line

    .Range("A" & NR).Resize(UBound(MyArr) + 1).Value = Application.WorksheetFunction.Transpose(MyArr)

    Number of elements vary in cells and it makes it very difficult to do this job...I have 10 columns and 38K rows on this sheet. It is complete nightmare!

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

    Re: Find corresponding values from different cells

    When you debug that line of code, what are the values of NR and Ubound(MyArr)?

    What is the value of the current cell being evaluated? (Ctrl-G, then ? Cell.Value)

+ 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