Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Help! Need to copy only some columns in rows that have X condtn [Solved/Closed]

Report
Posts
3
Registration date
Friday June 10, 2016
Status
Member
Last seen
June 11, 2016
 -
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 -
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

3 replies

Answer 1 / 3
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello 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


Following is the link to my test work book:-

https://www.dropbox.com/s/7cldr8jz6o0sx3f/TallPoppy%28Transfer%20data%2C%202%20criteria%2C%20autofilter%29.xlsm?dl=0

Click on the "RUN" button to see it work.

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.

Cheerio,
vcoolio.
Report
Posts
3
Registration date
Friday June 10, 2016
Status
Member
Last seen
June 11, 2016
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!
Answer 2 / 3
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Sarah,

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.

Column C:-
=IF(Sheet1!$B2<>"",Sheet1!D2,"")
dragged down.

Column D:-
=IF(Sheet1!$B2<>"",Sheet1!E2,"")
dragged down.

Column E:_
=IF(Sheet1!$B2<>"",Sheet1!J2,"")
dragged down

Column F:-
=IF(Sheet1!$B2<>"",Sheet1!K2,"")
dragged down

Column G:-
=IF(Sheet1!$B2<>"",Sheet1!L2,"")
dragged down.

Column H:-
=IF(Sheet1!$B2<>"",Sheet1!AA2,"")
dragged down.

Column I:-
=IF(Sheet1!$B2<>"",Sheet1!AB2,"")
dragged down.

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.

Let us know what you think.

Cheerio,
vcoolio.
Report
Posts
3
Registration date
Friday June 10, 2016
Status
Member
Last seen
June 11, 2016
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!
Answer 3 / 3
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Sarah,

You're welcome. Glad that I could help.

Good luck!

Cheerio,
vcoolio.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!