Copy an entire column to another sheet based on multiple cell value in that column

  • Hello everyone,


    I have a spreadsheet that has columns of info. I would like to copy columns into other sheets, based on the multiple cell criteria. I have no idea how to perform this. Can someone help me please? thanks in advance for any help!!



    Files

    • Sample.xlsx

      (1.23 MB, downloaded 39 times, last: )
  • When in Sheet1, run this macro and have a look at what is selected.

    Code
    1. Sub AAAAA()
    2. Sheet1.Range("1:1").Select
    3. End Sub

    After that, go to Sheet2 and run this macro and see what is selected.

    Code
    1. Sub AAAAA()
    2. Sheet2.Range("G11").Select
    3. End Sub

    Is the two selections what you want to compare?

    If not, let us know what you want to do by accurately describing, not code that does not work, what you want to achieve.

    I have not downloaded your file. I try to stay away from doing that. Been unpleasantly surprised too many times.

  • Hello jolivanes!


    Thank you for the consideration…

    I have three Headers in sheet1 ......"1:1”,"2:2" & "3:3"

    Compare Sheet2 "G11" in sheet1 "1:1"

    Compare Sheet2 "AF10" in sheet1 "2:2"

    Compare Sheet2 "AF11" in sheet1 "3:3"


    Sheet2 cell value "G11", "AF10" and "AF11"........ If all matches in any single column of the Sheet1 header, copy that entire column with two more offset columns and also column B that is Date column......... and paste it onto Sheet 3.


    Please have a look attached file; I have given an example and marked the matching criteria in sheet 1 and also copied the full expected result in sheet 3.


    I hope this brief explanation make any sense.

  • In your sample workbook why is Sheet1 cells F1 and F3 not a match with cells I1 and I3, but cells I1 and I3 is a match with cells O1 and O3?


    Cells F1 and F3 are exactly the same as cells O1 and O3

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Basically there are total 6 criteria’s in sheet2, to be match with sheet1.

    The contents of the sheet 1 column header are fixed and the criteria’s in sheet 2 (cell contents) are always changing.

    If the 3 criteria (e.g G11, AF10 & AF11) from sheet 2 match all together in a single column of sheet 1, then copy the whole column with 2 more offset columns.


    The following criteria are currently matched in the attached Excel.

    1- Sheet2 G11, AF10 & AF11 matches with Sheet1 column 9 (i.e “I”)

    2- Sheet2 G11, AG10 & AF11 matches with Sheet1 column 15 (i.e “O”)


    3- Sheet2 G11, AI10 & AI11 matches with Sheet1 column 30 (i.e “AD”)

    4- Sheet2 G11, AJ10 & AI11 matches with Sheet1 column 33 (i.e “AG”)


    5- Sheet2 G11, AL10 & AL11 matches with Sheet1 column 30 (i.e “AD”)

    6- Sheet2 G11, AM10 & AL11 matches with Sheet1 column 33 (i.e “AG”)

  • OK, that makes more sense now, I was looking for the matches wrongly.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hello Everyone....


    I've created a code to extract the data if any one header matches. Code working fine. However, it is also extracting three unwanted columns in sheet3 columnA to columnC. I was trying to stop extraction of unwanted column. Any help would be greatly appreciated.