Spreadsheet currently:

Col A

Supply A1

Stnd Pricing 01/01/09 5.28Supply A2

Stnd Pricing 01/01/09 11.21etc

What is needed

Row 1

Col A = Supply A1

Col B = Stnd Pricing

Col C = 01/01/09

Col D = 5.28Row 2

Col A = Supply A2

Col B = Stnd Pricing

Col C = 01/01/09

Col D = 11.21

1 - In B1 enter: =IF(LEFT(A1,6)="Supply",A2,"")

2 - Drag this down to the bottom of your data

3 - Edit...Copy...Edit...Paste Special...Values

4 - Data...Text to Columns...Fixed Width...Next

5 - Click on the ruler where you want your columns...Finish

6 - Sort on Column B

7 - Eliminate the empty rows

Thank you for the quick response. I apolgize for not giving actual data the 1st time. The original data is listed in Col A and looks like this with the results I'd like below:

The supply name can be multiple alpha/numeric words but the format of the row below it is always the same

SKIN BARRIER 7

STANDARD 01/01/01 69.87SKIN BARRIER 7806

STANDARD 01/01/01 69.87SKIN BARRIER 7 STANDARD 10/01/08 69.87

SKIN BARRIER 7806 STANDARD 10/01/08 69.87

I don't see much difference. Change the LEFT function to match the data and follow the same procedure.

1 - In B1 enter: =IF(LEFT(A1,4)="Skin",A2,"")

If you still haven't given us an example that matches the data, then please explain.

OK, you've got me confused..... What information is in cell A1 ?

What information is in cell A2 ?Is this it:

A1) SKIN BARRIER 7

A2) STANDARD 01/01/01 69.87or is it:

A1) SKIN BARRIER 7 STANDARD 01/01/01 69.87

Just the info in cell A1....

MIKE

Thanks again. I can make this work. Sorry for the confusion Mike: Hopefully this is a better example

Col A onlyAce Bandage 2"x2"

Standard 01/01/09 6.78Gauze Tape

Standard 01/01/09 9.62Cath Tray 16 FR

Standard 01/01/09 24.12

I Think this is what your looking for provided your data is set up as shown below: Cell A1) Ace Bandage 2"x2"

Cell A2) Standard 01/01/09 6.78

Cell A3)

Cell A4) Gauze Tape

Cell A5) Standard 01/01/09 9.62

Cell A6)

Cell A7) Cath Tray 16 FR

Cell A8) Standard 01/01/09 24.12

In Cell B1 enter: =A1

In Cell C1 enter: =LEFT(A2,FIND(" ",A2,1))

In Cell D1 enter: =LEFT(RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),FIND(" ",RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),1))

In Cell E1 enter: =RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Then copy cells B1 thru E1

to only Cells B4, B7, etc etc

Use the CTRL key to do the copies.

Then Select all of Columns B thru E

Select Edit, Copy, Edit, Paste Special, select ValuesThen delete column A

MIKE

Thank you so much, Mike. This works beautifully.

Glad I could help. MIKE

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History