Comparing data in multiple spread sheets...Closed

Question

Hello, 

Kindly help me with this. Your help is appreciative. 

I have one column in worksheet1 and two columns in worksheet2, and two columns in worksheet3 and I should get the data of entire rows of sheet2 and sheet3  in worksheet4. 

Explanation..

Sheet1 would be the main data sheet with only one column. 
First sheet1 column should compare with sheet2 and if it find any matchings it should present the entire row of the sheet2 in output sheet which is sheet4. Then it should go sheet3 again if any matchings are found(with respect to sheet1) it should pull the data of entire row of sheet3 to place the data in sheet4. 

For example 

worksheet1 

x 
abc 
xyz 
zyx 
rst 
tyu 

worksheet2 

x 
abc 10 
pqr 20 
zyx 30 

worksheet3 

x 
abc 10 
pqr 20 
rst 40 

output : 

Worksheet 4 

x 
abc 10 
zyx 30 
rst 40 

Requesting to sort out the problem ASAP 



Configuration: Windows XP / Safari 534.3

venkat1926 · Answer

try this macro 

Sub test() 
Dim r As Range, c As Range, cfind As Range 
Dim j As Long, dest As Range 
Worksheets("sheet4").Cells.Clear 
With Worksheets("sheet1") 
Set r = Range(.Range("A1"), .Range("A1").End(xlDown)) 
For Each c In r 
For j = 2 To 3 
With Worksheets("sheet" & j).Columns("A:A") 
Set cfind = .Cells.Find(what:=c.Value, lookat:=xlWhole) 
If Not cfind Is Nothing Then 
cfind.EntireRow.Copy 
Set dest = Worksheets("sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
dest.PasteSpecial 
End If 
End With 
Next j 
Next c 
End With 
With Worksheets("sheet4") 
.Range("A1") = "hdng1" 
.Range("B1") = "hdng2" 
Set r = .Range("A1").CurrentRegion 
r.AdvancedFilter action:=xlFilterInPlace, unique:=True 
End With 
End Sub

raod720 · Answer

Hi,

Your help is really appreciative. Thank you.The logic is super good, really amazing. 

But for the obvious reason of unique purpose i have. I feel its better if I copy the data of sheet3 to sheet2 and then apply the logic of copying the entire row.

I could able to do the copy and paste, here is the code


Worksheets("sheet3").Cells(1, 1).EntireRow.Clear

Worksheets("sheet3").UsedRange.Copy

Worksheets("sheet2").Range("a600").PasteSpecial

but the problem is if i use a2 instead of a600


Worksheets("sheet2").Range("a2").PasteSpecial

the data is getting replaced. which is leading to a bug...

Can you please help me to avoid this. please give a hint on this.


If i simply put it...

I want ot copy complete sheet3 to sheet2. Where in sheet 2 contains already some data. 

Plz help me to resolve this ASAP.

venkat1926 · Answer

the full implication of your change is not clear to me 

as far as I understand, for debugging purposes use another macro.  I presume you do not want to clear sheet2 but only add sheet3 to sheeet2 

Sub copying() 
Dim r As Range 
With Worksheets("sheet3") 
Set r = Range(.Range("A2"), .Range("A2").End(xlDown).End(xlToRight)) 
r.Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
End With 
End Sub

than what do you want to do?

raod720 · Answer

Hey Venkat, 

You made my day. Cool...!! Buddy 

r.Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)  

this line is very helpful. with little modification its exactly fitting in my code with out separate macro . this is how i modified it... 

Worksheets("sheet3").Cells(1, 1).EntireRow.Clear 

Worksheets("sheet3l").UsedRange.Copy 

Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial 

this lines are perfect fit to my code.. 

Appreciative you got amazing knowledge in VBA. Lots of learning happening to me as well... keep in touch krishnad.12@gmail.com..pls send a test mail.

Comparing data in multiple spread sheets...

4 responses

Similar discussions

Subscribe To Our Newsletter!