Hi all,
I have a very complicated (at least for me) question for anyone who's up for a challenge. Thank you in advance for your patience during this long explanation.
I frequently download spreadsheets of data from Google AdWords and format the tables to send to clients. For those unfamiliar with AdWords, there are a total of 34 columns available within the interface from which one can receive reporting. These include things such as cost, clicks, impressions, etc. When downloaded to excel, they appear in roughly the same format, with column headers starting in row two and the data following thereafter.
The formatting involves taking out certain columns of data while leaving others. This can become very time consuming, so I thought a macro would be valuable. Here is where it gets complicated: I work with several client accounts and while the names of the column headers are consistent across all accounts, their locations on the spreadsheet are not. For example, one report may place the "Cost" column in column B, while another may place it in column C. As you can see, this is a referencing nightmare. In short, I want to be able to download a report from AdWords for any client, and in the stroke of one shortcut key, delete all the unnecessary columns, regardless of their location in the spreadsheet.
The closest I have come is by choosing relative reference and then individually "finding" (cntrl f) each header that I don't need and deleting it from the spreadsheet. This works great except for one more problem: every client only selects certain columns out of the 34 available. Whichever of the 34 they view in AdWords are those that are downloaded to excel. So of each report, one may include impressions cost and clicks, while another may only include cost and impressions, but not clicks. This means that if I do cntrl f and find and delete the "clicks" column, for example, on an account that doesn't have a clicks column, then I pull an error code. Please realize, however, that the columns I need to delete are all the same, across all accounts - they are not specific to each account. It is just the columns available for viewing in the spreadsheet that vary.
This is where I'm stuck, and this is why I'm reaching out to anyone who could offer any assistance/advice. Truth is, I'm not even sure if macros are capable of achieving such complicated functions. Please let me know if there is any part of this you do not understand. Below is an example of the code I'm using with the error code where it tried to delete a column that didn't exist.
Thank you in advance for all your help!
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+i
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Cells.Find(What:="campaign state", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="budget", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="status", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="cost", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Avg. CPC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Lost IS (budget)", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Lost IS (rank)", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Avg. CPM", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Columns("L:AA").Select
Selection.Delete Shift:=xlToLeft
Range("H5").Select
End Sub
I have a very complicated (at least for me) question for anyone who's up for a challenge. Thank you in advance for your patience during this long explanation.
I frequently download spreadsheets of data from Google AdWords and format the tables to send to clients. For those unfamiliar with AdWords, there are a total of 34 columns available within the interface from which one can receive reporting. These include things such as cost, clicks, impressions, etc. When downloaded to excel, they appear in roughly the same format, with column headers starting in row two and the data following thereafter.
The formatting involves taking out certain columns of data while leaving others. This can become very time consuming, so I thought a macro would be valuable. Here is where it gets complicated: I work with several client accounts and while the names of the column headers are consistent across all accounts, their locations on the spreadsheet are not. For example, one report may place the "Cost" column in column B, while another may place it in column C. As you can see, this is a referencing nightmare. In short, I want to be able to download a report from AdWords for any client, and in the stroke of one shortcut key, delete all the unnecessary columns, regardless of their location in the spreadsheet.
The closest I have come is by choosing relative reference and then individually "finding" (cntrl f) each header that I don't need and deleting it from the spreadsheet. This works great except for one more problem: every client only selects certain columns out of the 34 available. Whichever of the 34 they view in AdWords are those that are downloaded to excel. So of each report, one may include impressions cost and clicks, while another may only include cost and impressions, but not clicks. This means that if I do cntrl f and find and delete the "clicks" column, for example, on an account that doesn't have a clicks column, then I pull an error code. Please realize, however, that the columns I need to delete are all the same, across all accounts - they are not specific to each account. It is just the columns available for viewing in the spreadsheet that vary.
This is where I'm stuck, and this is why I'm reaching out to anyone who could offer any assistance/advice. Truth is, I'm not even sure if macros are capable of achieving such complicated functions. Please let me know if there is any part of this you do not understand. Below is an example of the code I'm using with the error code where it tried to delete a column that didn't exist.
***Ultimately, I really just need to figure out a way to make a list of columns I want to be deleted and have excel delete those that do exist in the spreadsheet while ignoring those that do not exist in the spreadsheet.***
Thank you in advance for all your help!
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+i
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Cells.Find(What:="campaign state", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="budget", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="status", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="cost", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Avg. CPC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Lost IS (budget)", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Lost IS (rank)", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Avg. CPM", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Columns("L:AA").Select
Selection.Delete Shift:=xlToLeft
Range("H5").Select
End Sub