Help! Need to copy only some columns in rows that have X condtn [Solved/Closed]
TallPoppy3
Posts Friday June 10, 2016Registration date
June 11, 2016
Last seen
-
Jun 10, 2016 at 04:28 AM - Latest reply: vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
- Jun 11, 2016 at 06:34 AM
OK, Full disclosure, I am a Novice Excel user at best.....I have a Google Sheet Workbook that contains Google Form Responses (the Sheet name that these go to is called Form Responses)....the Form itself is a many sectioned beast....so the information needs to be filtered. I have created separate Sheets within the Workbook to make it easier for people to get what they need for reporting. They are even less novice than me!
I have a column in my Response Form that contains a few different result options......I would like to extract, into a different sheet, information from my Response Form that contains either the words "Phone Call" and/or "Digital Interaction". For the rows that contain this result, I would like to copy over only certain Columns: They are B, C, D, E and J,K, L and AA, AB....NOT the entire Row and I do not want to have any blank rows in the new Sheet. (eg I don't want FALSE results to be included).
Whew, I am totally out of my league with this. Is there anyone out there that can help? What formula do I need to insert to make this all magically happen? Thank you good citizens of the world.......My Name is Sarah
I'm not sure if I follow fully, but the following code may work for you:-
Sub TransferData()
Dim lr As Long
Application.ScreenUpdating = False
ActiveSheet.Columns("A").EntireColumn.Hidden = True
ActiveSheet.Columns("F:I").EntireColumn.Hidden = True
ActiveSheet.Columns("M:Z").EntireColumn.Hidden = True
lr = Range("A" & Rows.Count).End(xlUp).Row
Sheet2.UsedRange.Offset(1).ClearContents
With ActiveSheet
.AutoFilterMode = False
With Range("B1", Range("B" & Rows.Count).End(xlUp))
.AutoFilter 1, "Phone Call", xlOr, "Digital Interaction"
On Error Resume Next
Range("B2:AB" & lr).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End With
.AutoFilterMode = False
End With
ActiveSheet.Columns("A").EntireColumn.Hidden = False
ActiveSheet.Columns("F:I").EntireColumn.Hidden = False
ActiveSheet.Columns("M:Z").EntireColumn.Hidden = False
Sheet2.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed.", vbExclamation, "STATUS"
End Sub
The code checks Column B for the criteria ("Phone Call" or "Digital Interaction") in the Active Sheet (your main sheet) and on finding the criteria, copies the relevant data from the columns that you specified to sheet2.
Please note that the test work book is an Excel file and may not work in Google Sheets as Google Sheets do not support macros. You can convert Google Sheets to an Excel file.
I do not have any experience with Google Sheets but I hope that this points you in the right direction at least.
Thank you vcoolio for taking the time to do this for me. This is exactly what I needed the worksheet to do. I just wish I knew how to make this happen with Google sheets. For the time being though, it will be just fine. I will still put it out to the forum to see if anyone can provide something that isn't a macro but a simple Formula I can add to the Google Sheet. Have a great day and thanks again!
A simple IF statement should work in Google Sheets. The following formulae are based on the sample work book in my first post. Play with the formulae in the test work book to see if its what you are after. Just don't click on the button!
In Sheet 2, starting in row 2, place the following formulae:-
Column A:-
=IF(Sheet1!$B2<>"",Sheet1!B2,"") then drag it down as far as needed.
Column B:-
=IF(Sheet1!$B2<>"",Sheet1!C2,"") dragged down again.
The formulae above will bring across to sheet 2 only the data from the columns that you specified in your original post.
Using formulae, you will have blank rows in sheet2 if there isn't a value in the criteria column (Column B, sheet1). Do you wish to have a temporary value in the blank rows (such as N/A) until such time as a criteria is placed in the relevant cell in Column B, sheet1?
You should get the drift of what the formulae are doing, so you should be able to change things around to suit your actual work book.
Excellent thank you! I will definitely use this IF formula. It does exactly what I need (apart from not including the blank results in the row)....but I can live with this. I am really grateful to you, thank you very much for all of your help!
