Taking header columns and switch the data to rows...

[Siagon09]

Hi there,

I have a datasheet with a lot of information. And the values split into 10-20 different columns which I don't need.

Instead, I want to have only 1 column with values and the data currently in the headers should be listed in each row.

So for example, this is what I currently see. Each X is header column with values
x1 x2 x3
A
b
c
d

I want to see it as
Values
A x1
B x2
C x2
D x3
E x1

Is there a simple and quick way to do this? Let me know if this is not clear. Thanks!!

-Sia

 

[VoG]

Copy the row, click in an empty cell in column A, Edit > Paste Special, check Transpose then click OK.

 

[Siagon09]

I don't think it is as simple as transpose

There's hundreds of rows already with other data in each column. The value section is split be another 10-20 columns with values all over the places. Hundreds of cells in each column.

Transpose would just switch the headers to rows but I need the values to follow as well.

How would I transpose the headers and keep the values in only 1 column?

Sorry the title of my thread is not really correct for this question. I just don't know how to phrase it.

 

[VoG]

You need to post a bigger sample showing what you have and what you want.

 

[Siagon09]

ok i think i figured out how to imbed this.. see below

https://spreadsheets.google.com/spr...OXpmWVlnREVRVlE&single=true&gid=0&output=html

As you can see the headers in green (Bus Unit 1,2,3 etc). There are about 20 or so of these. I want to create another column specifically for the Bus Units and one consolidated line for the $ values.

This is what I want the above to actually look like:

https://spreadsheets.google.com/spr...OXpmWVlnREVRVlE&single=true&gid=1&output=html

 

[Siagon09]

ignore the above post. i think i figured out how to imbed this now

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Vendor	Cost Type	Cost Description	GL Account	Bus Unit 1	Bus unit 2	Bus Unit 3	Bus Unit 4	Bus Unit 5
2	Vendor A	Consultant	Automation	60190015	$ 100		$ 5	$ 3,000
3	Vendor B	Software	Security Scans	60190015		$ 200			$ 10,000
4	Vendor C	Harware	Onshore	60190015			$ 5,000		$ 20,000
5	Vendor D	Other	Offshore	60190015		$ 4,000	$ 2,000
6	Vendor E	Software	Misc	60190015			$ 100
7	Vendor F	Consultant	Scheduling	60190015	$ 300			$ 1,500
8	Vendor G	Consultant	Storage	60190015
Sheet1

Excel 2003



As you can see the headers in green (Bus Unit 1,2,3 etc). There are about 20 or so of these. I want to create another column specifically for the Bus Units and one consolidated line for the $ values.

This is what I want the above to actually look like:

Excel Workbook
	A	B	C	D	E	F
1	Vendor	Cost Type	Cost Description	GL Account	BU's	Amount
2	Vendor A	Consultant	Automation	60190015	Bus Unit 1	$ 100
3	Vendor A	Consultant	Automation	60190015	Bus Unit 3	$ 5
4	Vendor A	Consultant	Automation	60190015	Bus Unit 4	$ 3,000
5	Vendor B	Software	Security Scans	60190015	Bus unit 2	$ 200
6	Vendor B	Software	Security Scans	60190015	Bus Unit 5	$ 10,000
7	Vendor C	Harware	Onshore	60190015	Bus Unit 3	$ 5,000
8	Vendor C	Harware	Onshore	60190015	Bus Unit 5	$ 20,000
9	Vendor D	Other	Offshore	60190015	Bus unit 2	$ 4,000
10	Vendor D	Other	Offshore	60190015	Bus Unit 3	$ 2,000
11	Vendor E	Software	Misc	60190015	Bus Unit 3	$ 100
12	Vendor F	Consultant	Scheduling	60190015	Bus Unit 1	$ 300
13	Vendor F	Consultant	Scheduling	60190015	Bus Unit 4	$ 1,500
14	Vendor G	Consultant	Storage	60190015
Sheet2

Excel 2003

 

[VoG]

Try

Sub XPose()
Dim LR As Long, LC As Long, i As Long, j As Long
Sheets("Sheet2").Range("A1:F1").Value = Array("Vendor", "Cost Type", "Cost Description", "GL Account", "BU's", "Amount")
With Sheets("Sheet1")
    LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LC = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    For i = 2 To LR
        For j = 5 To LC
            If .Cells(i, j).Value <> "" Then
                Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = .Range(.Cells(i, 1), .Cells(i, 4)).Value
                Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = .Cells(1, j).Value
                Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = .Cells(i, j).Value
            End If
        Next j
    Next i
End With
End Sub