+ Reply to Thread
Results 1 to 2 of 2

Copying rows down into cells across until row=false

  1. #1
    Registered User
    Join Date
    03-26-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Copying rows down into cells across until row=false

    Sorry about the title, let me explain in more detail:

    In row A, I have various listings. The title is always in caps, the user is always in mixed case.

    For example:

    COW
    SHEEP
    Jim
    Bob
    Stu
    RHINO
    Dave
    Ollie

    So, no-one is interested in COWS but Jim, Bob and Stu like SHEEP. And what I need to end up with is:

    COW
    SHEEP | Jim, Bob and Stu
    RHINO | Dave and Ollie

    You get the picture. It doesn't matter if the readers names go into one cell each, I can concatenate them later.

    Now, what I've worked out is that I can put a TRUE or a FALSE in the next column along, with:
    =IF(AND(LEN(A1)>1,EXACT(A1, UPPER(A1))),TRUE,FALSE)

    So I've got

    Please Login or Register  to view this content.
    Now, what I HAD planned on doing was a formula which would test if the cell to the right of A1 said true or false, then concatenate the contents of the cell below the contents of A1 in the most recent TRUE cell, then concatenate that plus the next on and the next one until we get another TRUE. But of course, the cell value changes too.

    So I'm clearly out of my depth here! I just showed that to prove that I have at least tried and thought about it, as I know there's worse than a newb posting when they've not even tried!

    Thanks, and hello.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Copying rows down into cells across until row=false

    Probably best done by macro.

    Please Login or Register  to view this content.
    Martin

+ 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