vlookup : upwards

[f-x.p]

Hi,

I have a sheet coming from another (stupid) program, with
- one line " client: name_of_the_client"
-on many lines, all the products ordered by the client


Of course, to sort this sheet, I would like a new column, with
name_of_the_client repeated in front of each corresponding product.

Can a vlookup formula put in column A the last previous cell from
column B beginning with "client" ?

From :

***********

Client: Mister Blabla
- toothbrush
- broom
-carpet
Client: Miss Blibli
- paper
- car
- plant

To:
**********

Mister Blabla | tootbrush
Mister Blabla | broom
Mister Blabla | carpet
Miss Blibli | paper
Miss Blibli | car
Miss Blibli | plant

Thanks for your help

François

 

[Dave Peterson]

How about an alternative.

Insert a new column A (data in B1:B###)
Put this in A1: =MID(B1,9,255)
put this in A2: =IF(LEFT(B2,7)="client:",MID(B2,9,255),A1)
Then select A2 and drag down as far as you need.
Select column A and convert to values (edit|copy, edit|paste special|values)

Then filter column B to show only begins with client:
delete those visible rows
Maybe even select column B and do
edit|replace
what: _-_ (spacebar, hyphen, spacebar)
with: (leaveblank)
replace all
to clean up those hyphens.

 

[f-x.p]

Hi Dave,

Thank you very much for this very nice formula:

I just translated functions to french, and transformed commas to semi
colon, and it worked great.

Thanks a lot.

François

 

[Dave Peterson]

Glad you got it working.

Hi Dave,

Thank you very much for this very nice formula:

I just translated functions to french, and transformed commas to semi
colon, and it worked great.

Thanks a lot.

François