How to transpose data with VBA

[SydneyLucy]

I have an excel sheet with columns of data that then needs to be transposed into rows, however each column is a different length and there are loads of columns so to do each one individually takes too long. I have managed to get the keywords over and walking through the macro it brings the top 2 titles over but the pulls down the wrong data.

Original columns look like this, with various numbers of keywords

campaign1
adgroup1
keyword1
keyword2
keyword3
keyword4
keyword5

this needs to move to the spreadsheet to look like this

campaign1 adgroup1 keyword1
campaign1 adgroup1 keyword2
campaign1 adgroup1 keyword3 etc

This is the macro I'm using. Any help really appreciated!

Sub Keyword_Transform()
Dim lngInputColumnID As Long, lngOutputRow As Long
Dim wksSource As Worksheet, wksdest As Worksheet
Dim lngColNo As Long
Dim strTemp As String

Set wksSource = Worksheets("Campaign Structure")
Set wksdest = Worksheets("DS_Kwds")

Application.ScreenUpdating = False
' Delete existing content
wksdest.Range("A7:Z7", wksdest.Range("A7:Z7").End(xlDown)).ClearContents

lngOutputRow = 7

For lngInputColumnID = 2 To 256

Application.StatusBar = "Processing column " & lngInputColumnID - 1 & " of 255"

'see if there's an Ad group title & at least 1 keyword
If wksSource.Cells(10, lngInputColumnID).Value <> "" Then

If wksSource.Cells(71, lngInputColumnID).Value <> "" Then

'wksSource.Cells(10, lngInputColumnID).Copy
wksdest.Cells(lngOutputRow, 17).Value = wksSource.Cells(10, lngInputColumnID).Value 'Campaign
wksdest.Cells(lngOutputRow, 16).Value = wksSource.Cells(70, lngInputColumnID).Value 'Ad group

strTemp = wksSource.Cells(68, lngInputColumnID).Value 'cost per click
strTemp = Application.WorksheetFunction.Substitute(strTemp, "$", "") 'remove dollar signs ($)
wksdest.Cells(lngOutputRow, 9).Value = strTemp 'PasteSpecial xlPasteValues
wksdest.Cells(lngOutputRow, 10).Value = strTemp 'PasteSpecial xlPasteValues

wksdest.Cells(lngOutputRow, 5).Value = wksSource.Cells(65, lngInputColumnID).Value 'Link URL

'Keywords
If wksSource.Cells(71, lngInputColumnID).Offset(1, 0) = "" Then
wksSource.Cells(71, lngInputColumnID).Copy
Else
wksSource.Range(wksSource.Cells(71, lngInputColumnID), wksSource.Cells(71, lngInputColumnID).End(xlDown)).Copy
End If
wksdest.Cells(lngOutputRow, 2).PasteSpecial xlPasteValues

'Engine-specific data
Select Case wksSource.Range("Engine").Value
Case "Yahoo"
wksdest.Cells(lngOutputRow, 3).Value = "Advanced" 'Match type
wksdest.Cells(lngOutputRow, 8).Value = 0.1 'Min bid
Case "Google"
wksdest.Cells(lngOutputRow, 3).Value = "Broad" 'Match type
wksdest.Cells(lngOutputRow, 8).Value = 0.01 'Min bid
End Select

If wksdest.Cells(lngOutputRow, 2).Offset(1, 0).Value = "" Then
lngOutputRow = lngOutputRow + 1

Else
With wksdest.Cells(lngOutputRow, 2).End(xlDown).Offset(0, 1)
wksdest.Range(.Cells, .End(xlUp)).FillDown
'lngOutputRow = .Offset(1).Row
End With
With wksdest.Cells(lngOutputRow, 2).End(xlDown).Offset(0, 3).Range("A1:m1")
wksdest.Range(.Cells, .End(xlUp)).FillDown
lngOutputRow = .Offset(1).Row
End With

End If
End If
End If

Next lngInputColumnID

wksdest.Select
wksdest.Range("A6").Select
' wksSource.Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 

[GTO]

It is hard to see what you exactly want, as this is not a straight transposing. Could you provide at least two columns of data before and what its suppposed to look like after, using...

Excel Jeanie
Or
Richard Schollar's HTML Maker

 

[jeffreybrown]

If you choose to x-post on other forums please help us by providing a link.

http://www.excelguru.ca/node/7

 

[SydneyLucy]

Also posted here

I shall update posts if I find a solution

 

[SydneyLucy]

This is the initial data

Excel Workbook
	B	C	D	E	F
10	Campaign 2	Campaign 3	Campaign 4	Campaign 5	Campaign 6
12	adgroup1	adgroup2	adgroup3	adgroup4	adgroup5
14	Ad title
15	Description 1
16	Description 2
17	Display URL
20
21	Click-thru URL	Click-thru URL	Click-thru URL	Click-thru URL	Click-thru URL
22
23
24	Cost Per Click	Cost Per Click	Cost Per Click	Cost Per Click	Cost Per Click
25
26
27	adgroup1	adgroup2	adgroup3	adgroup4	adgroup5
28	Keyword1	Keyword1	Keyword1	Keyword1	Keyword1
29	Keyword2	Keyword2	Keyword2	Keyword2	Keyword2
30	Keyword3	Keyword3	Keyword3	Keyword3	Keyword3
31	Keyword4	Keyword4	Keyword4	Keyword4	Keyword4
32	Keyword5	Keyword5	Keyword5	Keyword5	Keyword5
33	Keyword6	Keyword6	Keyword6	Keyword6	Keyword6
34	Keyword7	Keyword7	Keyword7	Keyword7	Keyword7
35	Keyword8	Keyword8	Keyword8	Keyword8	Keyword8
36	Keyword9	Keyword9	Keyword9	Keyword9	Keyword9
37	Keyword10	Keyword10	Keyword10	Keyword10	Keyword10
38	Keyword11	Keyword11	Keyword11	Keyword11	Keyword11
39	Keyword12	Keyword12	Keyword12	Keyword12	Keyword12
40	Keyword13	Keyword13	Keyword13	Keyword13	Keyword13
41	Keyword14	Keyword14	Keyword14	Keyword14	Keyword14
42	Keyword15	Keyword15	Keyword15	Keyword15	Keyword15
43	Keyword16	Keyword16	Keyword16	Keyword16	Keyword16
44	Keyword17	Keyword17	Keyword17		Keyword17
45	Keyword18	Keyword18	Keyword18		Keyword18
46	Keyword19		Keyword19
47	Keyword20		Keyword20
48	Keyword21		Keyword21
49	Keyword22		Keyword22
50	Keyword23
51	Keyword24
Campaign Structure

 

[SydneyLucy]

This is how I want it to end up, the highlighted yellow columns are the ones I'm worried about, not too concerned about the rest.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6	Keyword	Match Type	Keyword Alt. Text	Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	Ad Group	Campaign
7	Keyword1	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
8	Keyword2	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
9	Keyword3	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
10	Keyword4	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
11	Keyword5	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
12	Keyword6	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
13	Keyword7	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
14	Keyword8	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
15	Keyword9	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
16	Keyword10	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
17	Keyword11	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
18	Keyword12	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
19	Keyword13	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
20	Keyword14	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
21	Keyword15	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
22	Keyword16	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
23	Keyword17	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
24	Keyword18	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
25	Keyword19	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
26	Keyword20	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
27	Keyword21	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
28	Keyword22	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
29	Keyword23	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
30	Keyword24	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup1	Campaign 2
31	Keyword1	Broad		Link URL	URL Parameters	Exchange Parameters	Min. Bid	Search Engine Bid	Max. Bid	Bid Strategy	Target Pos.	Display URL	Keyword Type	Category	adgroup2	Campaign 3
DS_Kwds