Match Data From 2 Worksheet
-
-
-
Re: Match Data From 2 Worksheet
Hi,
Thanks a lot. Its pulling out the correct data now. But I forgot to mention one more thing. There are some cases whereby there are 2 same codes under different plant name. For example code, "21000024" is under the plant name "MTPDI" and "MTPDT". So I need to have both the datas because they are under the different plant names. If this is solved then it will meet the requirement fully.
Thanks for your help.- Meena
-
Re: Match Data From 2 Worksheet
OK
tryCode
Display MoreSub test() Dim a, i As Long, b(), n As Long, z As String a = Sheets("sheet 1").Range("a1").CurrentRegion.Resize(,6).Value With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare For i = 2 To UBound(a,1) z = a(i,1) & ";" & a(i,6) If Not .exists(z) Then .add z, Nothing Next a = Sheets("sheet2").Range("a1").CurrentRegion.Resize(,8).Value ReDim b(1 To UBound(a,1), 1 To 4) For i = 2 To UBound(a,1) z = a(i,5) & ";" & a(i,1) If .exists(z) Then n = n + 1 b(n,1) = a(i,5) : b(n,2) = a(i,1) : b(n,3) = a(i,6) : b(n,4) = a(i,8) .remove(z) End If Next End With With Sheets("ExpectedResults").Range("a1") .CurrentRegion.ClearContents .Resize(,4).Value = [{"Code","Plant Name","Country","Devision"}] If n > 0 Then .Offset(1).Resize(n,4).VAlue = b End If End With End Sub
-
Re: Match Data From 2 Worksheet
Hi,
Sorry 1 more thing. If the data in sheet1 MATCHES with the data in sheet2 then it should appear in the expectedresults worksheet. If NOT it should not appear. For example the first record in the expectedresults worksheet, "21009886", "BMCC", "JAPAN", "2". This data did not appear in sheet1, its only in sheet2. Therefore it should not appear in the expectedresults worksheet.
Sorry, I think I didn state the complete requirement clearly. Your help will be very much appreciated because it will lighten my burden for checking datas in the future.
Thank you.- Meena
-
Re: Match Data From 2 Worksheet
Hi,
Wow! That was fast. Thanks for your instant replies. It really helped a lot. Now its working fine. Thanks a lot.
- Meena
-
-
Re: Match Data From 2 Worksheet
The most current code compares Col.A & F (21018895;MTPDI for the 1st record of sheet 1) with Col.E & A (21000024;MTPDI for the 1st record of sheet2).
I don't find any other common field to compare with.
Can you tell me how to compare?I didn't see your last post.
Doe s the code work fine now? -
Re: Match Data From 2 Worksheet
Hi Macro Pro,
Before I sent my reply(about my problem), you sent me the solution already. Now its working nicely and perfectly. Thanks a lot. One question. In the future should I stick on to the same columns and rows like this workbook if I wanna use the same codes for the macro?
- Meena
-
Re: Match Data From 2 Worksheet
I recommend you to step through the code and find out what the code is doing for yourself.
While you are in VBE;
1) go to [View] - [LocalWindow]
2) click somewhere on the code
3) as you hit F8, the code executes line by line and you will see all the variables in the local window at the same time.If you find "+" sign on the variable in the LocalWindow, you can expand it by just clicking on it, so you can see the inside.
Study about Array and Dictionary Object as well.
-
Re: Match Data From 2 Worksheet
Thats really cool. I looked through it, I could see that its searching through each data. I will look through it in the future. Thanks for the info and also for the help:)
- Meena
-
Re: Match Data From 2 Worksheet
Hi,
I need one more help. The supplier name from sheet1 needs to be displayed in the expectedresults worksheet whereby it has to be positioned in the middle of the 'code' and the 'plant name' columns.
Thanks.- Meena
-
-
Re: Match Data From 2 Worksheet
Hi,
Just to inform. I have solved the problem. Following your suggestion.
Thank You.- Meena
-
Re: Match Data From 2 Worksheet
OOps, I missed your last post, but you seem to solve it for yourself.
Very nice!
-
Re: Match Data From 2 Worksheet
Hi,
Im assigned with another requirement which I tried to solve it by myself, but then it turned out to be wrong. I hope you can help me.
I have attached another sample excel document with a different result.
Basically its the same as the initial one, but I want the ExpectedResults worksheet to display diffferently from the old one.
It should display EVERYTHING from SHEET1, doesnt matter if it matches or not. Now for the matching part, if Sheet1 matches with Sheet2 then it should display the 'country/Area Supplier ' and 'Division Trade Policy' as highlighted in blue. If it doesn match than those fields in light blue must be blank. Is it clear? I hope that I have stated the requirements clearly.
Thanks for the help.- Meena
-
Re: Match Data From 2 Worksheet
Hi,
Sorry this is the correct document. Please refer from this excel document.
Thanks.- Meena
-
Re: Match Data From 2 Worksheet
try
Code
Display MoreSub test() Dim a, i As Long, b(), n As Long, z As String a = Sheets("sheet 1").Range("a1").CurrentRegion.Resize(,6).Value ReDim b(1 To UBound(a,1), 1 To 2) With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare For i = 2 To UBound(a,1) z = a(i,1) & ";" & a(i,6) If Not .exists(z) Then .add z, i - 1 Next With Sheets("sheet2").Range("a1").CurrentRegion a = .Offset(1).Resize(.Rows.Count - 1, 8).Value End With For i = 1 To UBound(a,1) z = a(i,5) & ";" & a(i,1) If .exists(z) Then b(.item(z), 1) = a(i,7) : b(.item(z), 2) = a(i,.8) End If Next End With With Sheets("ExpectedResults") .Cells.Clear Sheets("Sheet1").Range("a1").CurrentRegion.Copy .Range("a1") With .Range("g1") .Resize(,2).Value =[{"Country/Area Supplier","Devision Trade Policy"}] .Offset(1).Resize(UBound(b,1),UBound(b,2)).Value = b End With End With End Sub
[COLOR="Red"]Edited: Code just now 2:24[/COLOR] -
-
Re: Match Data From 2 Worksheet
Hi,
Thanks for the response. I tried but the result is wrong. Under the 'Country/Area supplier' should be the country's name eg japan, singapore. But it displays the 'supplier name'. Then the 'Division trade policy' should be in numbers eg 1, 2, 3. But it displays the 'Plant Name'.
Thanks- Meena
-
Re: Match Data From 2 Worksheet
The 'country/area supplier' column should be taken from column F and the 'Division trade policy' should be taken from column H.
-
Re: Match Data From 2 Worksheet
Can you just change these numbers to
what ever the column related to the output
eg.
if you want to output what is in col.G then 7, 8 for H, you know what I mean?
and just found that you have a hidden column (G) in Sheet2, and I took that column.was it 6 and 8?
-
Re: Match Data From 2 Worksheet
Yeah the rows are 6 and 8. I have changed them and checked through the datas, its correct. But there is one problem now, it leaves a space after the 'country/area supplier' and 'division trade policy' columns. How to remove that space in the macro itself? I have attached a document on how the result looks like.
Thanks.-Meena
-
Re: Match Data From 2 Worksheet
How about?
Code
Display MoreSub test() Dim a, i As Long, b(), n As Long, z As String With Sheets("Sheet1").Range("a1").CurrentRegion a = .Resize(.Rows.Count - 1, 6).Offset(1).Value End With ReDim b(1 To UBound(a,1), 1 To 2) With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare For i = 2 To UBound(a,1) z = a(i,1) & ";" & a(i,6) If Not .exists(z) Then .add z, i Next With Sheets("sheet2").Range("a1").CurrentRegion a = .Offset(1).Resize(.Rows.Count - 1, 8).Value End With For i = 1 To UBound(a,1) z = a(i,5) & ";" & a(i,1) If .exists(z) Then b(.item(z), 1) = a(i,6) : b(.item(z), 2) = a(i,.8) End If Next End With With Sheets("ExpectedResults") .Cells.Clear Sheets("Sheet1").Range("a1").CurrentRegion.Copy .Range("a1") With .Range("g1") .Resize(,2).Value =[{"Country/Area Supplier","Devision Trade Policy"}] .Offset(1).Resize(UBound(b,1),UBound(b,2)).Value = b End With End With End Sub
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!