Posts by rehan kazi

    Hi,


    This piece of code is only pulling the latest / maximum date if multiple criteria's match. Instead of just pulling the maximum date, I want to pull the Large date by adding a condition, i.e. if the offset column "rCell.Offset(0, 14).Value" is empty, pull the Large date "fCell.Offset(0, 9).Value = WorksheetFunction.Large(rCell.Offset(0, 11).Value, 3)" , otherwise the latest / maximum date.

    Thank You.

    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.


    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”)

    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.

    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 31 times, last: )

    =LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))

    Hi,


    This formula is extracting value from the cell number AP1134. However , by using same formula , I was again struggling to extract one more value from the second column cell (B1134)


    which is present in the same row (AP1134)


    =OFFSET(AP1134,,COLUMN(AP1134)*-1+2,,)

    this formula is doing well . But how can I apply to below formula


    LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))


    Thank You

    Hi,

    I would like to find a way to write a formula that will return the value of the last non-empty cell in the column. I have three header rows that are criteria.


    MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)


    This formula gives the position of the desire column. however ,need to be retrieve the value of the last non-empty cell in this column.


    Thanks in advance for any/all advise.

    Really noob in coding :D;(...........tried to modify.... not producing output...any help please?




    All formulas in Display Sheet AQ:AY....edited two references to make the formula work dynamically


    From

    INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))


    Changed to

    INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Display!$I$3&Display!$AG$4&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))



    Code
    1. these references need to be made as As long (for all formula)
    2. c = Evaluate("=INDEX(JCB!C6:CB500,MATCH(Display!D" & i & ",JCB!B6:B500,0),MATCH(Display!I3&Display!AJ4&Summary!K6,INDEX(JCB!C1:CB1&JCB!C2:CB2&JCB!C3:CB3,0),0))")