• 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.

Data Merge

parth007

Member
One Excel having two sheets..
Sheet1 = J
Sheet2 = G
so If
J.Column2 = G.Column1
&
J.Column7 = G.Column23
&
J.Column10 = G.Column8
&
J.Column15 = G.Column2

If above conditions match then the respective data should be copied to Resultant Sheet The resultant data should only pick records from G Sheet. along with all matched data
Please find attached format
 

Attachments

  • Updated.xlsx
    12.5 KB · Views: 4
HELLO aLL BELOW IS THE CODE.. I TRIED AND IT CLICKED...

Code:
Sub Update()
Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Dim rngNames As Range
  Dim NameCell As Range
  Dim rngFound As Range
  Dim arrResults() As Variant
  Dim ResultIndex As Long
  Dim strFirst As String
  
  Set ws1 = Sheets("Sheet1")
  Set ws2 = Sheets("Sheet2")
  Set rngNames = ws1.Range("B2", ws1.Cells(Rows.Count, "B").End(xlUp))
  
  If rngNames.Row < 2 Then Exit Sub  'No data
  
  ReDim arrResults(1 To 51, 1 To 65000)
  ResultIndex = 1
  arrResults(1, ResultIndex) = "Column1"
  arrResults(2, ResultIndex) = "Column2"
  arrResults(3, ResultIndex) = "Column3"
  arrResults(4, ResultIndex) = "Column4"
  arrResults(5, ResultIndex) = "Column5"
  arrResults(6, ResultIndex) = "Column6"
  arrResults(7, ResultIndex) = "Column7"
  arrResults(8, ResultIndex) = "Column8"
  arrResults(9, ResultIndex) = "Column9"
  arrResults(10, ResultIndex) = "Column10"
  arrResults(11, ResultIndex) = "Column11"
  arrResults(12, ResultIndex) = "Column12"
  arrResults(13, ResultIndex) = "Column13"
  arrResults(14, ResultIndex) = "Column14"
  arrResults(15, ResultIndex) = "Column15"
  arrResults(16, ResultIndex) = "Column16"
  arrResults(17, ResultIndex) = "Column17"
  arrResults(18, ResultIndex) = "Column18"
  arrResults(19, ResultIndex) = "Column19"
  arrResults(20, ResultIndex) = "Column20"
  arrResults(21, ResultIndex) = "Column21"
  arrResults(22, ResultIndex) = "Column22"
  arrResults(23, ResultIndex) = "Column23"
  arrResults(24, ResultIndex) = "Column24"
  arrResults(25, ResultIndex) = "Column25"
  arrResults(26, ResultIndex) = "Column26"
  arrResults(27, ResultIndex) = "Column27"
  arrResults(28, ResultIndex) = "Column28"
  arrResults(29, ResultIndex) = "Column29"
  arrResults(30, ResultIndex) = "Column30"
  arrResults(31, ResultIndex) = "Column31"
  arrResults(32, ResultIndex) = "Column32"
  arrResults(33, ResultIndex) = "Column33"
  arrResults(34, ResultIndex) = "Column34"
  arrResults(35, ResultIndex) = "Column35"
  arrResults(36, ResultIndex) = "Column36"
  arrResults(37, ResultIndex) = "Column37"
  arrResults(38, ResultIndex) = "Column38"
  arrResults(39, ResultIndex) = "Column39"
  arrResults(40, ResultIndex) = "Column40"
  arrResults(41, ResultIndex) = "Column41"
  arrResults(42, ResultIndex) = "Column42"
  arrResults(43, ResultIndex) = "Column43"
  arrResults(44, ResultIndex) = "Column44"
  arrResults(45, ResultIndex) = "Column45"
  arrResults(46, ResultIndex) = "Column46"
  arrResults(47, ResultIndex) = "Column47"
  arrResults(48, ResultIndex) = "Column48"
  arrResults(49, ResultIndex) = "Column49"
  arrResults(50, ResultIndex) = "Column50"
  arrResults(51, ResultIndex) = "Column51"
  
  For Each NameCell In rngNames.Cells
  Set rngFound = ws2.Columns("A").Find(NameCell.Value, ws2.Cells(Rows.Count, "A"), xlValues, xlWhole)
  If Not rngFound Is Nothing Then
  strFirst = rngFound.Address
  Do
  If ws2.Cells(rngFound.Row, "A").Value = ws1.Cells(NameCell.Row, "B").Value _
  And ws2.Cells(rngFound.Row, "B").Value = ws1.Cells(NameCell.Row, "O").Value _
  And ws2.Cells(rngFound.Row, "H").Value = ws1.Cells(NameCell.Row, "J").Value _
  And ws2.Cells(rngFound.Row, "W").Value = ws1.Cells(NameCell.Row, "G").Value Then
  ResultIndex = ResultIndex + 1
  arrResults(1, ResultIndex) = NameCell.Value
  arrResults(2, ResultIndex) = ws2.Cells(rngFound.Row, "B").Value
  arrResults(3, ResultIndex) = ws2.Cells(rngFound.Row, "C").Value
  arrResults(4, ResultIndex) = ws2.Cells(rngFound.Row, "D").Value
  arrResults(5, ResultIndex) = ws2.Cells(rngFound.Row, "E").Value
  arrResults(6, ResultIndex) = ws2.Cells(rngFound.Row, "F").Value
  arrResults(7, ResultIndex) = ws2.Cells(rngFound.Row, "G").Value
  arrResults(8, ResultIndex) = ws2.Cells(rngFound.Row, "H").Value
  arrResults(9, ResultIndex) = ws2.Cells(rngFound.Row, "I").Value
  arrResults(10, ResultIndex) = ws2.Cells(rngFound.Row, "J").Value
  arrResults(11, ResultIndex) = ws2.Cells(rngFound.Row, "K").Value
  arrResults(12, ResultIndex) = ws2.Cells(rngFound.Row, "L").Value
  arrResults(13, ResultIndex) = ws2.Cells(rngFound.Row, "M").Value
  arrResults(14, ResultIndex) = ws2.Cells(rngFound.Row, "N").Value
  arrResults(15, ResultIndex) = ws2.Cells(rngFound.Row, "O").Value
  arrResults(16, ResultIndex) = ws2.Cells(rngFound.Row, "P").Value
  arrResults(17, ResultIndex) = ws2.Cells(rngFound.Row, "Q").Value
  arrResults(18, ResultIndex) = ws2.Cells(rngFound.Row, "R").Value
  arrResults(19, ResultIndex) = ws2.Cells(rngFound.Row, "S").Value
  arrResults(20, ResultIndex) = ws2.Cells(rngFound.Row, "T").Value
  arrResults(21, ResultIndex) = ws2.Cells(rngFound.Row, "U").Value
  arrResults(22, ResultIndex) = ws2.Cells(rngFound.Row, "V").Value
  arrResults(23, ResultIndex) = ws2.Cells(rngFound.Row, "W").Value
  arrResults(24, ResultIndex) = ws2.Cells(rngFound.Row, "X").Value
  arrResults(25, ResultIndex) = ws2.Cells(rngFound.Row, "Y").Value
  arrResults(26, ResultIndex) = ws2.Cells(rngFound.Row, "Z").Value
  arrResults(27, ResultIndex) = ws2.Cells(rngFound.Row, "AA").Value
  arrResults(28, ResultIndex) = ws2.Cells(rngFound.Row, "AB").Value
  arrResults(29, ResultIndex) = ws2.Cells(rngFound.Row, "AC").Value
  arrResults(30, ResultIndex) = ws2.Cells(rngFound.Row, "AD").Value
  arrResults(31, ResultIndex) = ws2.Cells(rngFound.Row, "AE").Value
  arrResults(32, ResultIndex) = ws2.Cells(rngFound.Row, "AF").Value
  arrResults(33, ResultIndex) = ws2.Cells(rngFound.Row, "AG").Value
  arrResults(34, ResultIndex) = ws2.Cells(rngFound.Row, "AH").Value
  arrResults(35, ResultIndex) = ws2.Cells(rngFound.Row, "AI").Value
  arrResults(36, ResultIndex) = ws2.Cells(rngFound.Row, "AJ").Value
  arrResults(37, ResultIndex) = ws2.Cells(rngFound.Row, "AK").Value
  arrResults(38, ResultIndex) = ws2.Cells(rngFound.Row, "AL").Value
  arrResults(39, ResultIndex) = ws2.Cells(rngFound.Row, "AM").Value
  arrResults(40, ResultIndex) = ws2.Cells(rngFound.Row, "AN").Value
  arrResults(41, ResultIndex) = ws2.Cells(rngFound.Row, "AO").Value
  arrResults(42, ResultIndex) = ws2.Cells(rngFound.Row, "AP").Value
  arrResults(43, ResultIndex) = ws2.Cells(rngFound.Row, "AQ").Value
  arrResults(44, ResultIndex) = ws2.Cells(rngFound.Row, "AR").Value
  arrResults(45, ResultIndex) = ws2.Cells(rngFound.Row, "AS").Value
  arrResults(46, ResultIndex) = ws2.Cells(rngFound.Row, "AT").Value
  arrResults(47, ResultIndex) = ws2.Cells(rngFound.Row, "AU").Value
  arrResults(48, ResultIndex) = ws2.Cells(rngFound.Row, "AV").Value
  arrResults(49, ResultIndex) = ws2.Cells(rngFound.Row, "AW").Value
  arrResults(50, ResultIndex) = ws2.Cells(rngFound.Row, "AX").Value
  arrResults(51, ResultIndex) = ws2.Cells(rngFound.Row, "AY").Value
  
  End If
  Set rngFound = ws2.Columns("A").Find(NameCell.Value, rngFound, xlValues, xlWhole)
  Loop While rngFound.Address <> strFirst
  End If
  Next NameCell
  
  If ResultIndex > 1 Then
  ReDim Preserve arrResults(1 To 51, 1 To ResultIndex)
  
  'To override existing data
  Sheets("Sheet4").UsedRange.ClearContents
  Sheets("Sheet4").Range("A1").Resize(UBound(arrResults, 2), UBound(arrResults, 1)).Value = Application.Transpose(arrResults)
  
  'If you instead want to add data to the bottom of existing results, uncomment next line
  'Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(arrResults, 2), UBound(arrResults, 1)).Value = Application.Transpose(arrResults)
  
  Sheets("Sheet4").Select
  MsgBox ResultIndex - 1 & " matches found."
  Else
  MsgBox "No matches found"
  End If
End Sub
 
Back
Top