What Formula should i use?

bflo

New member
Joined
Aug 12, 2013
Messages
1
Reaction score
0
Points
0
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
    screenshot.JPG
    41.4 KB · Views: 19

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
in D2:
=IF(B2="","",TRIM(SUBSTITUTE(LOOKUP(2,1/(LEFT(A$2:A2,7)="Account"),A$2:A2),"Account:","")))

copied down
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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:","")))
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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!! :eek:
 
Top