PDA

View Full Version : 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
???????????????????????????????????????????????????:banghead:

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