Moving Columns to Rows

March 26, 2009 at 13:41:41
Specs: Windows XP
Spreadsheet currently:
Col A
Supply A1
Stnd Pricing 01/01/09 5.28

Supply A2
Stnd Pricing 01/01/09 11.21

etc

What is needed
Row 1
Col A = Supply A1
Col B = Stnd Pricing
Col C = 01/01/09
Col D = 5.28

Row 2
Col A = Supply A2
Col B = Stnd Pricing
Col C = 01/01/09
Col D = 11.21


See More: Moving Columns to Rows

Report •


#1
March 26, 2009 at 14:39:19
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

Report •

#2
March 26, 2009 at 15:43:50
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.87

SKIN BARRIER 7806
STANDARD 01/01/01 69.87

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


Report •

#3
March 26, 2009 at 17:04:13
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.


Report •

Related Solutions

#4
March 26, 2009 at 17:15:32
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.87

or is it:

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

Just the info in cell A1....

MIKE


Report •

#5
March 26, 2009 at 17:42:55
Thanks again. I can make this work.

Sorry for the confusion Mike: Hopefully this is a better example
Col A only

Ace Bandage 2"x2"
Standard 01/01/09 6.78

Gauze Tape
Standard 01/01/09 9.62

Cath Tray 16 FR
Standard 01/01/09 24.12



Report •

#6
March 26, 2009 at 19:29: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 Values

Then delete column A

MIKE


Report •

#7
March 26, 2009 at 19:38:14
Thank you so much, Mike. This works beautifully.

Report •

#8
March 26, 2009 at 19:57:26
Glad I could help.

MIKE


Report •


Ask Question