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.

        For Each fCell In xFac
            If fCell.Offset(0, 3).Value = "CC" And fCell.Value <> "" Then
            xCriteria(1) = fCell.Value
            xCriteria(2) = fCell.Offset(0, 1)
            xCriteria(3) = fCell.Offset(0, 2)
            
            With CreateObject("scripting.Dictionary")
            For Each rCell In xRRpt
                    If rCell.Value = xCriteria(1) And rCell.Offset(0, 1).Value = xCriteria(2) And rCell.Offset(0, 2).Value = xCriteria(3) Then 'And rCell.Offset(0, 3).Value = xCriteria(4) Then 'And rCell.Offset(0, 2).Value = xCriteria(4) Then
                      s = rCell.Offset(0, 11).Value & rCell.Value & rCell.Offset(0, 1).Value & rCell.Offset(0, 2).Value '& rCell.Offset(0, 14).Value
                      If .exists(s) Then
                        fCell.Offset(0, 9).Value = WorksheetFunction.Max(rCell.Offset(0, 11).Value, .Item(s))
                     .Item(s) = WorksheetFunction.Max(rCell.Offset(0, 11).Value, .Item(s))
                    Else
                         fCell.Offset(0, 9).Value = rCell.Offset(0, 11).Value                         
                         .Add s, rCell.Offset(0, 11).Value
                      End If
                     End If
             Next rCell
            End With
        End If
      Next fCell

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.

Private Sub CommandButton1_Click()
 Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, a() As Variant, b() As Variant
  Dim i As Long, j As Long, lr As Long, lc As Long
  Set sh1 = Sheets("Sheet1")          'origin
  Set sh2 = Sheets("Sheet3")  'destination
  Set sh3 = Sheets("Sheet2")
  
  sh2.Rows("11:" & Rows.Count).ClearContents
   
      If sh3.Range("AF10") <> "" Or sh3.Range("AG10") <> "" Or sh3.Range("AI10") <> "" Or sh3.Range("AJ10") <> "" Or sh3.Range("AL10") <> "" Or sh3.Range("AM10") <> "" Then
          
With sh3
        
        sh2.Range("F10").Value = .Range("AF10").Value
        sh2.Range("I10").Value = .Range("AG10").Value
        sh2.Range("L10").Value = .Range("AI10").Value
        sh2.Range("O10").Value = .Range("AJ10").Value
        sh2.Range("R10").Value = .Range("AL10").Value
        sh2.Range("U10").Value = .Range("AM10").Value
       
    End With
  
  lr = sh1.Range("C" & Rows.Count).End(xlUp).Row
 
  lc = sh1.Cells(2, Columns.Count).End(xlToLeft).Column
  a = WorksheetFunction.Transpose(sh1.Range("A2", sh1.Cells(2, lc)).Value)
 
  lc = sh2.Cells(10, Columns.Count).End(xlToLeft).Column
  b = WorksheetFunction.Transpose(sh2.Range("A10", sh2.Cells(10, lc)).Value)
  

    
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)
      If b(j, 1) = a(i, 1) Then
         sh2.Cells(11, 5).Resize(lr).Value = sh1.Cells(6, 2).Resize(lr).Value
         sh2.Cells(11, j).Resize(lr).Value = sh1.Cells(6, i).Resize(lr).Value
          sh2.Cells(11, j + 1).Resize(lr).Value = sh1.Cells(6, i + 1).Resize(lr).Value
          sh2.Cells(11, j + 2).Resize(lr).Value = sh1.Cells(6, i + 2).Resize(lr).Value
         sh2.Activate
        Exit For
    
      End If
    Next
  Next
  Else
  MsgBox "No Data Found"
     End If
End Sub

...

corrected..

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

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

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!!

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AF10")
Sheet1.Range("3:3") = Sheet2.Range("AF11")

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AG10")
Sheet1.Range("3:3") = Sheet2.Range("AF11")



Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AI10")
Sheet1.Range("3:3") = Sheet2.Range("AI11")

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AJ10")
Sheet1.Range("3:3") = Sheet2.Range("AI11")



Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AL10")
Sheet1.Range("3:3") = Sheet2.Range("AL11")

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AM10")
Sheet1.Range("3:3") = Sheet2.Range("AL11")

'If above criteria met then copy entire column from sheet1 and paste to sheet3

Quote from bosco_yip

=INDIRECT("JCB!B"&MATCH(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)),,,60000)))

Doing well.....Thanks

If the Date column is dragged down, this formula will extract the last cell content. In fact, I want the date value in the same row.

Quote from bosco_yip

=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

Great.....prepared with few argument's..

Very Perfect ...Thnx

one more quick question ......How can I get offset value....

e.g. This formula is extracting value from the cell number AP1134... how can i get it from the third offset column

that is cell number AR1134

Quote from bosco_yip

=LOOKUP(9^9,JCB!$C$5:$CB$5/(Display!$I$3&Summary!$M$8&Summary!$M$6=JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3))

Still Not.....

Please check the attached file for the reference.

This formula also gives the desire result, but I want to do it without fixing any range in the column...... like.... JCB! B:B

=OFFSET(JCB!$B$5,MATCH(9.99999999999999E+307,JCB!B:B)-5,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))

any other solution please..

Quote from royUK

=LOOKUP(2,1/(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)<>""),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))

Hi royUK,

This is also returning column number only. i am looking for the value of the last non-empty cell in this column

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.

Any Help Please....

these references need to be made as As long (for all formula)

1. 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))")

Really noob in coding ...........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))

these references need to be made as As long (for all formula)
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))")

Thanks a lot Carim...

Once run in original file... will let you know