+ Reply to Thread
Results 1 to 5 of 5

Show contents of vertical cells in horizontal cells

  1. #1
    Forum Contributor
    Join Date
    02-12-2008
    Location
    Doha-Qatar
    MS-Off Ver
    Excel 2007
    Posts
    137

    Show contents of vertical cells in horizontal cells

    Hello friends,

    I am referring to the post made by NBVC at http://www.excelforum.com/excel-gene...xcel-help.html,
    Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down

    I have a similar case but there are no empty rows between lists, what would be the formula to get the same results? (The transposed address should appear next to each first line of each block).

    Earlier, I posted at http://www.excelforum.com/excel-gene...-one-cell.html and I got brilliant answers, I also would like to get benefit of the above mentioned formula but in cases when there are no empty rows between lists.

    Any insight on this is highly appreciated


    Many thanks
    Last edited by VBA Noob; 11-23-2008 at 03:51 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Is there something that is common in the first row of each block?

    For example, will each first row start with salutation, like Mr or Mrs or Ms?

    If yes, then using the same example as the linked thread, but with no spaces:

    Ms a Sample
    1 Sample Lane
    Sample Town
    Sample County
    ME34 LALA
    E-Mail: Sample@aol.ca
    Ms a Sample
    Sample Lane
    Sample Town
    Sample County
    ME34 LALA
    E-Mail: Sample@aol.com
    Starting in A2, then in B2, enter formula:

    =IF(OR(LEFT($A2,2)={"Mr","Ms","Mrs"}),INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"")

    and copy it across and down the columns/rows.

    You should get results like so:

    Please Login or Register  to view this content.

    Hopefully you have a consitent number of items in each block or you may get some overlap...
    Last edited by NBVC; 11-21-2008 at 05:22 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    02-12-2008
    Location
    Doha-Qatar
    MS-Off Ver
    Excel 2007
    Posts
    137
    Many thanks NBVC for your helps, unfortunately, there is nothing common and each cell in column D has a unique value that never repeats.
    I am attaching a sample for your easy reference

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    I will asssume that you want each block to start where there is a new date entered in column A...seems logical.

    So say in G2, enter formula:

    Please Login or Register  to view this content.
    and copy it only down the column as far as you want

    In H2, enter formula:

    Please Login or Register  to view this content.
    Copy it down the same number of rows and then copy across as many columns as you want to extract all vital information.

    Note: Adjust the G2 in this formula if you didn't put the first formula in G2.

    Hope this does it.
    Last edited by NBVC; 11-22-2008 at 06:56 PM. Reason: Simplified first Formula...

  5. #5
    Forum Contributor
    Join Date
    02-12-2008
    Location
    Doha-Qatar
    MS-Off Ver
    Excel 2007
    Posts
    137
    Hi NBVC,

    I have no words to say, this is brilliancy, magic!!! Fantastic

    I have tested the formula for more than 45000 lines and it worked magically, now we use a new jargon in our office “NBVC” and when our colleagues from other offices ask what you mean by (NBVC) we simply reply it means some thing impossible.

    But you helped us to make it possible!!, thanks a lot

+ 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