Data Merge

**parth007** · 01-15-2015, 08:36 AM

I have three Sheets

Sheet1 have some number of columns with more than 1000 data
Sheet2 have some number of columns with more than 5000 data
and
Sheet3 will have all columns from Sheet1 & Sheet2 on 4 columns data match

Attached is the sheet for reference

If 4 columns from sheet1 matches with 4 columns of sheet2 then respective data should be copied to sheet3

Below is the condition
"IF NO = SNO & Name = FullName & Price = Rate & Grade = GR " then rest all columns should be copied to sheet3

Plz Suggest

**parth007** · 01-16-2015, 01:27 AM

???????????????????????????????????????????????????

**snb** · 01-16-2015, 01:57 AM

We are not a solving forum (BTW will you being paid for using a suggestion from this forum ?; we won't), but offering assistance so you can improve your skills to solve your own questions. How many people did you hjelp this far ?

**parth007** · 01-16-2015, 03:11 AM

I am new here... and undergoing coching.... will take time to be on top

**SamT** · 01-16-2015, 05:34 PM

Parth,

In this thread use the following protocols:

NO & SNo shall be called SNo
Name & FullName shall be called FullName
Price & Rate shall be named Rate
Grade shall be Grade

Code requirements: Compare 4 Fields on sheet1 with 4 Fields on sheet2. If all Fields match, concatenate the Records on sheet3. See attached for Sheet3 Field order.

We need the following specifications:

Is it ever possible for one SNo to be paired (or matched) with two FullNames?
Is it ever possible for one FullName to be paired (or matched) with two SNo's? (An obverse)
Is it ever possible for one FullName to be paired (or matched) with two Rates?
Is the obverse possible?
Is it ever possible for one FullName to be paired (or matched) with two Grades?
Is the obverse possible?

In the attached: Sheet1 Status Field is the same as Sheet2 Salary Field. Is that an error?

**parth007** · 01-21-2015, 04:51 AM

Hi Sam, Updated information

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

**parth007** · 01-21-2015, 06:12 AM

HELLO aLL BELOW IS THE CODE.. I TRIED AND IT CLICKED...

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

**SamT** · 01-21-2015, 07:58 AM

Parth,

In this thread use the following protocols:

NO & SNo shall be called SNo
Name & FullName shall be called FullName
Price & Rate shall be named Rate
Grade shall be Grade

Code requirements: Compare 4 Fields on sheet1 with 4 Fields on sheet2. If all Fields match, concatenate the Records on sheet3. See attached for Sheet3 Field order.

We need the following specifications:

Is it ever possible for one SNo to be paired (or matched) with two FullNames?
Is it ever possible for one FullName to be paired (or matched) with two SNo's? (An obverse)
Is it ever possible for one FullName to be paired (or matched) with two Rates?
Is the obverse possible?
Is it ever possible for one FullName to be paired (or matched) with two Grades?
Is the obverse possible?

**parth007** · 01-22-2015, 02:29 AM

Hi SamT, the requirenment is solved.. i somehow tried & managed to build the code..
I am beginner so i take time to search & understand... Thanks for help..

Thread: Data Merge

Thread Tools

Display

Data Merge

Posting Permissions