+ Reply to Thread
Results 1 to 15 of 15

Row to column

  1. #1
    Registered User
    Join Date
    05-15-2015
    Location
    Sofia, Bulgaria
    MS-Off Ver
    2007
    Posts
    26

    Row to column

    Hello,
    i don't even know if this is possible, but i hope you can help me.
    I have a table with names and phone numbers. Every name may have one or more numbers. the numbers are in the row of the name. I need to sort all the number in 1 column. and the name for every number.
    example of the before and after tables.
    Is this possible with formula or vba...
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Row to column

    Hi benbi,

    Please see attache file with with formulas on sheet 1 and sheet 2

    Please note that array formulas and must be entered with Ctrl+Shift+Enter key combination.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    05-15-2015
    Location
    Sofia, Bulgaria
    MS-Off Ver
    2007
    Posts
    26

    Re: Row to column

    You Are Amazing! Thank you very much! It is brilliant!

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Row to column

    You're welcome and thank you for the feedback!

    **I should've mentioned that the long formula must be entered first.

  5. #5
    Registered User
    Join Date
    05-15-2015
    Location
    Sofia, Bulgaria
    MS-Off Ver
    2007
    Posts
    26

    Re: Row to column

    Thanks again! It works great!

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Row to column

    Hi benbi,
    . Here comes the learner straggler with his attempt. I had started as Alkey gave his solution, so I thought I would finish. I find the Threads with multiple answers often the most useful in my learning anyway...
    . VBA I am getting to be Ok, but I am just learning CSE Formulas now, so I had wanted to try a Formula solution. I thought it would take me a while, so I did the VBA Code alternative instead initially which is much easier. I am very glad I did and that Alkey did a CSE Formula solution in the meantime. I was sure it was possible but thought it would be incredibly difficult. I’m really look forward now to going through Alkey’s Solution and learning from it. (If I can get them to work?--- they do not appear to work initially by me??)
    . But anyway, just to give a bit back and contribute to the Thread here is my VBA solution.

    The code takes this ( or longer and wider Lists )

    Using Excel 2007
    -
    A
    B
    C
    D
    1
    Name Number number2 Number3
    2
    3
    David
    9362746384
    8392837451
    4
    Stephan
    7638493846
    8152637463
    8734762536
    5
    Samanta
    4733846354
    Sheet1

    And working on that, after running the code you get this:

    Using Excel 2007
    -
    A
    B
    C
    1
    Name Number
    2
    3
    David 9362746384
    4
    David 8392837451
    5
    Stephan 7638493846
    6
    Stephan 8152637463
    7
    Stephan 8734762536
    8
    Samanta 4733846354
    Sheet1

    Code:
    Please Login or Register  to view this content.
    .. hope the alternative may come in useful for you or anyone else popping into the Thread at a later date

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  7. #7
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Row to column

    An alternate [formula] method...
    Attached Files Attached Files

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Row to column

    Quote Originally Posted by jhren View Post
    An alternate [formula] method...


    Hi jhren,
    . Thanks so much for contributing to the Thread with your alternative Formula version. This was extremely helpful to me. As mentioned in my Post #6. I was looking forward to examining the Formula version from Alkey. But I was getting very frustrated as I cannot get them to work by me!?!?.
    . You Formulas worked fine by me and I have just gone through them, understanding them and learning from them. Just to give something back, here are some notes I made as I went through those formulas. It may help anyone reading the Thread In the future:
    https://app.box.com/s/hvpzlguex52ytd4wyd4vlh3ejno63uxo
    . I hope I am not “Hijacking” the thread with a few questions, should you look back here. ( I think they are relevant follow up questions: -
    . 1 ) At one point in your First Formula you produce a Boolean type array using this part
    . ($B$2:$D$5<>"") . This works and produces the required Array. However, my investigations ( detailed in the above link ) , suggest I should be doing this
    . ($B$2:$D$5<>0) . However this does not work.
    . Can you explain that if you have the time.
    . 2 ) Did you happen to get Alkey’s Formulas to work. The OP appeared to, so I expect I have some problem at my end. ( I have tried opening the File in XL 2007 and Xl 2010 )

    . Many thanks again for your very welcome contribution
    Alan
    ( . Also thanks to Alkey. I am sure they are great formulas that he gave and the problem lies with me that I cannot get them to work )

  9. #9
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Row to column

    "" checks for blank cell or blank cell with formula.
    0 checks for cells with only a zero displayed.

    I got AlKey's formulas to work, only in the sense that I downloaded his file, examined his formula and found it valid. I did not try it on other data.
    Last edited by jhren; 05-22-2015 at 11:41 AM.

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Row to column

    Hi jhren,
    . Thanks for that quick reply

    Quote Originally Posted by jhren View Post
    "" checks for blank cell or blank cell with formula.
    0 checks for cells with only a zero displayed.
    …...
    .. I apologize. I had actually asked completely the wrong question!! In fact I had actually meant to question this part.
    COUNTIF($B$2:$D$5,"")
    .. If I highlight this
    $B$2:$D$5
    in the formula bar and hit F9 I obtain this
    {0\0\0;9362746384\8392837451\0;7638493846\8152637463\8734762536;4733846354\0\0}
    .. which has 6 0s rather than 6 “”s . But maybe I can explain that myself….
    … the COUNTIF($B$2:$D$5,"") is intended to count the number of “”s So it requires the “”
    . However, by hitting F9 in the formula bar I do an instant evaluation of the cells , and I believe evaluating "" would return 0 just as I would receive a 0 if I typed
    =A2 ( where A2 was empty )
    in any other cell

    .. Does that seem a reasonable explanation?

    …………………………….

    Quote Originally Posted by jhren View Post
    I got AlKey's formulas to work, only in the sense that I downloaded his file, examined his formula and found it valid. I did not try it on other data.
    . I cannot get the formulas to work with any data. Even the given data. Must be something strange at my end

    . Many thanks again for taking the time to come back to this Thread
    Alan

  11. #11
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Row to column

    Excel doesn't always "evaluate" as we think it should in our minds.

    Here's an exercise to help you see...

    In some contiguous cells, say A1:B10, type in at least one each: text (including alphanumeric), number (including one with just 0), one completely blank, one with ="", one with =FALSE, and practically anything else you can think of.

    In C1 enter =IF(A1:B10="",1,0). Select A1:B10 and press F9 to see how Excel evaluates the different cells to an array. Undo. Select all and press F9. Note any blank or blank cell with formula resulting in "" evaluates to TRUE (i.e. 1 via the [value if true] parameter).

    From there, when you use COUNTIF(), Excel evaluates the parameters exactly the same the IF() formula above and counts the 1's (i.e. TRUE's).

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Row to column

    Quote Originally Posted by jhren View Post
    Excel doesn't always "evaluate" as we think it should in our minds.

    Here's an exercise to help you see...
    .........
    Thanks very much for taking the time to give me that very useful advice and tips. I shall look at what you suggested in detail ( and include the conclusions from it in my rambling explain notes “ ExcelForumulars.docx “ !!
    https://app.box.com/s/hvpzlguex52ytd4wyd4vlh3ejno63uxo


    Many thanks, much appreciated

    Alan Elston.

  13. #13
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Row to column

    Hi once again jhren ....

    Quote Originally Posted by Doc.AElstein View Post
    .... I shall look at what you suggested in detail ......
    .....
    .. I did that , and I got the point ! Thanks again

    (.. I assumed you meant
    by "Select all " -- select =IF(A1:B10="",1,0)



    . But I checked out all you said and I think I see exactly what you are saying.
    . I have learned something very useful (and something very rarely found in books.

    . Thanks for sharing the benefits of your experience here.

    Alan

  14. #14
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Row to column

    You're welcome.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Row to column

    Hi,
    . Some feedback / Follow up based on what I have learnt from this and similar Threads.

    . This will do the reverse, that is to say re-order the list back to its original..

    . Take for example benbis and jhren’s initial data…

    . He took this:

    Using Excel 2007
    -
    A
    B
    C
    D
    1
    Name Number number2 Number3
    2
    3
    David
    9362746384
    8392837451
    4
    Stephan
    7638493846
    8152637463
    8734762536
    5
    Samanta
    4733846354
    benbijhren

    .. and his formulas returned this

    Using Excel 2007
    -
    G
    H
    1
    Name Number
    2
    David
    9362746384
    3
    David
    8392837451
    4
    Stephan
    7638493846
    5
    Stephan
    8152637463
    6
    Stephan
    8734762536
    7
    Samanta
    4733846354
    benbijhren

    ….

    I take now the last table from jhren and return this:…

    Using Excel 2007
    -
    A
    B
    C
    D
    14
    Unique Name
    15
    David
    9362746384
    8392837451
    16
    Stephan
    7638493846
    8152637463
    8734762536
    17
    Samanta
    4733846354
    benbijhren

    . Here are the two formulas..

    Using Excel 2007
    -
    A
    B
    15
    =IFERROR(INDEX($G$2:$H$7, MATCH(0, COUNTIF($A$14:$A14,$G$2:$G$7), 0),1),"")
    =IFERROR(INDEX($H$2:$H$7,SMALL(IF($G$2:$G$7=$A15,ROW($H$2:$H$7)-ROW($H$2)+1),COLUMNS($B15:B15)),1),"")
    benbijhren


    . Note:
    . 1) The formulas are of the “CSE” sort which are placed in one cell and dragged down and or across…
    . 2) To put each of these formulas in turn into the spreadsheet:
    . 2a) copy ( Ctrl C ) the formula complete from the above table to clipboard
    . 2b) select ( click in ) the cell where the formula should go
    . 2c) Hit F2 or select ( click in ) the formula bar (To be on the safe side do both!! )
    . 2d) paste in the formula from the clipboard ( Ctrl V ) ( check that the formula includes a = at the start)
    . 2e) now you do the famous “CSE” . – That is hold down the keys Ctrl and Shift, and hit Enter.
    . 3) select A15
    . 4) click and hold on the tiny black square at the right bottom corner of this cell, and drag that one formula down.
    . 5) select B15
    . 6) click and hold on the tiny black square at the right bottom corner of this cell, and drag that one formula across
    . 7) click and hold on the tiny black square at the right bottom corner of this selection ( B15 to D15) , and drag that entire selection down

    . Alan

    P.s. Thanks to GerryZ for the difficult formula bit!

+ 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. Compare multiple columns (column A,column B,Column C) and return value (Column D)
    By john008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2015, 08:24 AM
  2. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 4
    Last Post: 04-07-2012, 09:14 AM
  3. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 5
    Last Post: 04-06-2012, 12:02 PM
  4. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-06-2012, 11:19 AM
  5. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2012, 11:13 AM

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