• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy row to different sheet if criteria is met

S

Simensg

New Member
Hi.

I have a worksheet with values from A1:K1901. Lets call it Worksheet1
The critical values are in the K column. I want to copy all the rows to Worksheet2 if a text criteria is met in the K column.

I am not familiar with macros, so a formula would be much appreciated :)
 
Hi, Simensg!

From Excel, press Alt-F11 to go to the VBA editor, Insert, Module, and place this code there:
Code: 
Option Explicit
 
Sub CopyWithAutoFilter()
    ' constants
    Const ksWSSource = "Hoja1"
    Const ksDataSource = "A1:A1901"
    Const ksConditionSource = "K1:K1901"
    Const ksConditionCriteria = "L1"
    Const ksWSTarget = "Hoja2"
    Const ksDataTarget = "A1"
    ' declarations
    ' start
    ' process
    With Worksheets(ksWSSource)
        .Range(ksConditionSource).AutoFilter 1, "=" & .Range(ksConditionCriteria).Value
        .Range(ksDataSource).Copy Worksheets(ksWSTarget).Range(ksDataTarget)
    End With
    ' end
End Sub

Just advise if any issue.

Regards!
 
Hi, Simensg!

A formula approach using a helper column in source worksheet might be these:
L1: criteria for filtering (same as previous method with VBA)
M1: =CONTAR.SI(K$1:K1;L$1) -----> in english: =COUNTIF(K$1:K1,L$1)
Copy down M1 as required (thru M1901).

In target worksheet:
A1: =INDICE(Hoja1!A$1:A$1901;COINCIDIR(FILA();Hoja1!M$1:M$1901;0)) -----> in english: =INDEX(Hoja1!A$1:A$1901,MATCH(ROW(),Hoja1!M$1:M$1901,0))
Copy down A1.

Regards!
 
In vb it is;

Code: 
Sub MoveData()
Sheet1.[K1:K1901].AutoFilter 1, "K"
Sheet1.[K1:K1901].Copy Sheet2.[A1]
End Sub

Where "K" is your criteria.

Take care

Smallman
 
I am sorry.
I think I made myself a bit unclear and I need to go further into details. The data is recordings from the Spanish football leauge for the 5 last years:

Column A: Leauge name (SP1)
Column B: Date
Column C: Home Team
Column D: Away Team
Column E: Goals by home team
Column F: Goals by away team
Column G: Total goals
Column H: Odds home team to win
Column I: Odds for a draw
Column J: Odds away team to win
Column K: Odds home team to win / Odds away team to win

I want to copy all the rows (or at least column K) when e.g Barcelona is playing at home.

In my head it will look like this (if excel had a copyif formula)

Sheet2 Cell A1: copyif=(A1:K1901;"*Barca*")

Best regards!
 
Hi Simensg

You only need to change the code you have been given slightly. Might want to clear the contents of sheet2 first. If this does not work open a fresh instance of XL put your data in Sheet1.



Code: 
Sub MoveData()
    Sheet2.[A1:K1901].ClearContents
    Sheet1.[K1:K1901].AutoFilter 1, "*Barca*"
    Sheet1.[A1:K1901].Copy Sheet2.[A1]
    Sheet1.[A1].AutoFilter
End Sub

I realise this is not a formula but it runs so smoothly you might want to look at learning a bit of vba.

Take care

Smallman
 
Hi, Simseng!

Both my VBA and formula solution and Smallman's wire/telegram VB works.

SirJB7 VBA:
- change ksDataSource constant to "A1:K1901"
- place in L1 the value for filtering accordingly to column C (home team) values

SirJB7 formulas:
- change A1 formula to:
A1: =INDICE(Hoja1!A$1:A$1901;COINCIDIR(FILA();Hoja1!$M$1:$M$1901;0)) -----> in english: =INDEX(Hoja1!A$1:A$1901,MATCH(ROW(),Hoja1!$M$1:$M$1901,0))
- copy across thru K1
- copy down thru row 1901

Smallman wire VBA:
- change "K" for the value of column C (home team)
- change [K1:K1901] to [A1:K1901] in copy statement

If any issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the red marked sticky posts at this forums main page for uploading guidelines.

Regards!

EDITED

PS: It seems we were both writing at the same time.
 
Thank you so much guys! :) I am very grateful!
I understand that I need to work on my VBA skills. Are there any good tutorials out there you know about?
 
I am trying to use the Sir JB7 method below. My data starts in row 2 on the source sheet (data headings in row one). I am trying to copy the data starting in row 2 on the new sheet (below similar data headings). For some reason, my formula is pulling data from row 3 on the source. I've tried making small changes but I can't get it to start pulling from row 2. Any suggestions? Please help. I'm going crazy.

Oscar

L1: criteria for filtering (same as previous method with VBA)
M1: =CONTAR.SI(K$1:K1;L$1) -----> in english: =COUNTIF(K$1:K1,L$1)
Copy down M1 as required (thru M1901).

- change A1 formula to:
A1: =INDICE(Hoja1!A$1:A$1901;COINCIDIR(FILA();Hoja1!$M$1:$M$1901;0)) -----> in english: =INDEX(Hoja1!A$1:A$1901,MATCH(ROW(),Hoja1!$M$1:$M$1901,0))
- copy across thru K1
- copy down thru row 1901
 
I found one of the best answer which lets you do it without using VBA, with just 1 helper column.
I'm glad that I found this answer because above given VBA methods weren't suiting me.
 
@Smallman

This was a huge help, love the VBA code. Can it be modified so it looks for the criteria in 3 different columns?

I have a data table, and i want it to copy like the above script if the criteria example: "MH" is in either column D, E or F.

At the moment i can get it to filter for column D, but no in E or F, since after the first filter is applied, then E and F filter doesn't have "MH" since the criteria is only in one of the columns.

Thanks beforehand.

I hope my explanation makes sence.
Thanks
 
Hi ,

It is difficult to do a filter over multiple columns ; an easy workaround is to concatenate the 3 columns of interest (D , E and F) in a helper column , and then apply the code to this column.

If any of the columns contains the text MH , the concatenated text will also contain it , and the filter on the helper column will consider that row for inclusion.

Narayan
 
Hello!

I'm taking my first stab at using VBA and I'm attempting to use the code @Smallman provided but I keep getting a Compile error "Expected:end of statement" after the AutoFilter 1 in 'Sheet1.[K1:K1901].AutoFilter 1, "*Barca*"'

What am I doing wrong??
 
You must log in or register to reply here.
Back
Top