# What Formula should i use?

#### bflo

##### New member
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!!

#### Attachments

• screenshot.JPG
41.4 KB · Views: 19

#### NBVC

##### Super Moderator
Staff member
in D2:
=IF(B2="","",TRIM(SUBSTITUTE(LOOKUP(2,1/(LEFT(A\$2:A2,7)="Account"),A\$2:A2),"Account:","")))

copied down

#### Hercules1946

##### New member
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

#### Hercules1946

##### New member
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

#### NBVC

##### Super Moderator
Staff member
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:","")))

#### Hercules1946

##### New member
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!!