Results 1 to 6 of 6

Thread: What Formula should i use?

  1. #1

    What Formula should i use?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Attached is a screen shot of the data i am looking to sort. In the first column there is a series of Policy Names and information, one being the Account names of various companies. I would like to figure out a way to have these Account Names pulled from the column, leave all of the other data behind, and have them line up in one row with the groups contact first and last name on the row as well. The first and last names for each account name could be on different rows in the data. Is it possible to get the account name the first and last names on the same row with a formula or sorting technique in excel?

    Thank you!!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	screenshot.JPG 
Views:	16 
Size:	41.4 KB 
ID:	1578  

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    in D2:
    =IF(B2="","",TRIM(SUBSTITUTE(LOOKUP(2,1/(LEFT(A$2:A2,7)="Account"),A$2:A2),"Account:","")))

    copied down


  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Im not sure I understand. Are you saying that (e.g.) the first name relating to a given account name will be in Col C on any of the rows between it and the next account name ? Do the account names cells start with an identical string (eg "Account Name:"). I was thinking that it might be possible to create a macro that will look for an Account Name cell and then delete the cells alongside in columns B and C (option shift cells up) if they are blank. When they are non-blank, look for the next account name and repeat. When all the accounts are processed, sort the data by Col A and the Account Name: cells with the names in B and C will appear together.
    I don't know the extent of your data, but if this idea seems workable, take a copy to play with obviously, so that you dont lose anything.

    HTH

    Hercules

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NBVC View Post
    in D2:
    =IF(B2="","",TRIM(SUBSTITUTE(LOOKUP(2,1/(LEFT(A$2:A2,7)="Account"),A$2:A2),"Account:","")))

    copied down
    It might be me, but I get an error returned from this formula. It highlights the last A2 Ref, saying there are too few arguments. Sorry, I can't follow the logic to pinpoint whats wrong.

    Hercules

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    My formula assumes the OP's data begins in A2:C2 with something like "Account:ABC Company" in A2 and somewhere in B:C names are entered.

    My formula looks to see if there is text in column B (indicating a row with a name in it), then it looks for the last time that a cell from A2 to current row begins with the string "Account" and returns that.

    Although the Substitute part needs a minor adjustment

    =IF(B2="","",TRIM(SUBSTITUTE(LOOKUP(2,1/(LEFT(A$2:A2,7)="Account"),A$2:A2),"Account Name:","")))


  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NBVC View Post
    My formula assumes the OP's data begins in A2:C2 with something like "Account:ABC Company" in A2 and somewhere in B:C names are entered.

    My formula looks to see if there is text in column B (indicating a row with a name in it), then it looks for the last time that a cell from A2 to current row begins with the string "Account" and returns that.

    Although the Substitute part needs a minor adjustment

    =IF(B2="","",TRIM(SUBSTITUTE(LOOKUP(2,1/(LEFT(A$2:A2,7)="Account"),A$2:A2),"Account Name:","")))
    Thanks for that. What a brilliant formula!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •