Results 1 to 8 of 8

Thread: Filter Sheet2 Data based on Sheet1 List Values

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Filter Sheet2 Data based on Sheet1 List Values

    Hi can anyone help with a macro?

    I have many hundreds of rows of Data on Sheet2 "A1:Z" with headers.
    "A2:A" has 'Ref values' for each row of Data.

    Sheet1 column "R5:R" has 'Ref values' added that change daily.

    I want to Filter Sheet2 "A:Z" using "A2:A" as field, based on the Values entered on Sheet1
    "R5:R" and then copy the filtered Data to last row on Sheet3

    Sheet2 Data example

    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC
    1 Ref Cnt Ref Cnt Ref Cnt Ref Cnt
    2 1 2 1 18 1 31 1 32
    3 1 2 1 18 1 31 1 32
    4 1 2 1 18 1 31 1 32
    5 2 6 2 7 2 14 2 16
    6 2 6 2 7 2 14 2 16
    7 2 6 2 7 2 14 2 16
    8 3 5 3 24 3 30 3 33
    9 3 5 3 24 3 30 3 33
    10 3 5 3 24 3 30 3 33
    11 4 2 4 5 4 10 4 24
    12 4 2 4 5 4 10 4 24
    13 4 2 4 5 4 10 4 24
    14 5 18 5 21 5 22 5 33
    15 5 18 5 21 5 22 5 33
    16 5 18 5 21 5 22 5 33
    17 6 9 6 21 6 29 6 32
    18 6 9 6 21 6 29 6 32
    19 6 9 6 21 6 29 6 32
    20 7 20 7 25 7 27 7 31
    21 7 20 7 25 7 27 7 31
    22 7 20 7 25 7 27 7 31
    23
    24
    25

    If Sheet1 column "R5:R9" has values 1,2,4,6,7 then the copied filtered Data on Sheet3 would be

    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC
    1 Ref Cnt Ref Cnt Ref Cnt Ref Cnt
    2 1 2 1 18 1 31 1 32
    3 1 2 1 18 1 31 1 32
    4 1 2 1 18 1 31 1 32
    5 2 6 2 7 2 14 2 16
    6 2 6 2 7 2 14 2 16
    7 2 6 2 7 2 14 2 16
    8 4 2 4 5 4 10 4 24
    9 4 2 4 5 4 10 4 24
    10 4 2 4 5 4 10 4 24
    11 6 9 6 21 6 29 6 32
    12 6 9 6 21 6 29 6 32
    13 6 9 6 21 6 29 6 32
    14 7 20 7 25 7 27 7 31
    15 7 20 7 25 7 27 7 31
    16 7 20 7 25 7 27 7 31
    17
    18
    19
    20
    21
    22
    23
    24
    25

    Any help would be appreciated

    regards

    pwill

    Last edited by pwill; Jul 18th, 2019 at 04:41 PM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    Quote Originally Posted by pwill View Post
    Hi can anyone help with a macro?

    I have many hundreds of rows of Data on Sheet2 "A1:Z" with headers.
    "A2:A" has 'Ref values' for each row of Data.

    Sheet1 column "R5:R" has 'Ref values' added that change daily.

    I want to Filter Sheet2 "A:Z" using "A2:A" as field, based on the Values entered on Sheet1
    "R5:R" and then copy the filtered Data to last row on Sheet3


    If Sheet1 column
    "R5:R9" has values 1,2,4,6,7 then the copied filtered Data on Sheet3 would be

    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC
    1 Ref Cnt Ref Cnt Ref Cnt Ref Cnt
    2 1 2 1 18 1 31 1 32
    3 1 2 1 18 1 31 1 32
    4 1 2 1 18 1 31 1 32
    5 2 6 2 7 2 14 2 16
    6 2 6 2 7 2 14 2 16
    7 2 6 2 7 2 14 2 16
    8 4 2 4 5 4 10 4 24
    9 4 2 4 5 4 10 4 24
    10 4 2 4 5 4 10 4 24
    11 6 9 6 21 6 29 6 32
    12 6 9 6 21 6 29 6 32
    13 6 9 6 21 6 29 6 32
    14 7 20 7 25 7 27 7 31
    15 7 20 7 25 7 27 7 31
    16 7 20 7 25 7 27 7 31
    17

    Any help would be appreciated
    regards
    pwill
    You can check your example, on sheet 1, in cells R5:R9 the values ​​are 14,14,14,10,10.


    Also tell me in which row the headings are on sheet1. The names of the headings of sheet1 are the same as those of sheet2.


    After reviewing the R5:R9 range, can you explain, according to your example, what data should be copied?
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    Hi DanteAmor,

    I didn't give an example for sheet1, the above example is the Data on sheet2 and the output on sheet3 if 'Sheet1 column "R5:R9" had values 1,2,4,6,7'

    The headers are the same on sheet2 and sheet3
    There are no headers on sheet1

    The data that should be copied would be
    Sheet2 "A2:Z" when applying the filter to 'column A or field 1 on sheet2 to only show the values from Sheet1

    Sheet1 values
    "R5" = 1
    "R6" = 2
    "R7" = 4
    "R8" = 6
    "R9" = 7

    hope that makes more sense?

    regards
    Last edited by pwill; Jul 18th, 2019 at 08:33 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    My mistake, I thought it was the sheet1.Try this


    Code:
    Sub Filter_with_multiple_conditions()
        Dim c As Range, n As Long, arr() As Variant
        For Each c In Sheets("Sheet1").Range("R5", Sheets("Sheet1").Range("R" & Rows.Count).End(xlUp))
            ReDim Preserve arr(n)
            arr(n) = c.Text
            n = n + 1
        Next
        Sheets("Sheet2").UsedRange.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
        Sheets("Sheet2").AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
    End Sub
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    Hi DanteAmor,
    Thanks for your reply I've been working all day I will give it a try and let you know how I get on
    Much appreciated

    pwill
    Last edited by pwill; Jul 19th, 2019 at 05:52 PM.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    Quote Originally Posted by pwill View Post
    Hi DanteAmor,
    Thanks for your reply I've been working all day I will give it a try and let you know how I get on
    Much appreciated

    pwill
    Ok, Let me know if you have any doubt.
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    Quote Originally Posted by DanteAmor View Post
    My mistake, I thought it was the sheet1.Try this


    Code:
    Sub Filter_with_multiple_conditions()
        Dim c As Range, n As Long, arr() As Variant
        For Each c In Sheets("Sheet1").Range("R5", Sheets("Sheet1").Range("R" & Rows.Count).End(xlUp))
            ReDim Preserve arr(n)
            arr(n) = c.Text
            n = n + 1
        Next
        Sheets("Sheet2").UsedRange.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
        Sheets("Sheet2").AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
    End Sub

    Works perfect, thank you DanteAmor

    pwill
    Last edited by pwill; Jul 19th, 2019 at 06:14 PM.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Filter Sheet2 Data based on Sheet1 List Values

    Quote Originally Posted by pwill View Post
    Works perfect, thank you DanteAmor

    pwill
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •