Comparing two excelfile worksheets Closed

Question

Hi,

One more scenario I have . If I have three columns in worksheet1 of file1(book1) and 1 column in worksheet2 of file2(book2), I should get only required columns in worksheet3 with column names to a new file(if possible) or with in any file.
for example

file1
worksheet1

x        y      z     p     and so on columns
abc    10    a     s
pqr    20    b     t
zyx    30    c     r

file2
worksheet2

x
abc
xyz
zyx
rst
tyu

to a new file
output :

x        z       p
abc    a       s 
zyx     c       r

Note : First line are column names

Requirement :

1.  matched records in one work sheet
2.  Unmatched records in another work sheet

Requesting to sort out the problem ASAP

Request : Only macros needed

rizvisa1 · Answer

Could you provide a sample file with your expected result. I am getting confused over, how matching is going to occur. You mentioned that the book has three columns and yet data is in x, y and z column ? Could you add to some share site and with expected result. Instead of 4 workbooks, you can use 4 sheets to explain how the final product with look like
You can post it here https://authentification.site or some other shared site

kish1975 · Answer

ile1
worksheet1

x        y       z      p and so on columns (field names like salesid, salesmanname, country)
abc   10      a      s
pqr    20     b      t
zyx    30     c       r

file2
worksheet2

x
abc
xyz
zyx
rst
tyu

to a new file worksheet1 should have matched data

output :

x z p
abc a s
zyx c r

to a new file worksheet2 should have unmatched data

output :

x     y    z     p
pqr 20   b    t
xyz
rst
tyu


Note : First line are column names

Requirement :

1. matched records in one work sheet
2. Unmatched records in another work sheet

Requesting to sort out the problem ASAP

Request : Only macros needed

rizvisa1 · Answer

Assumption: 1. Headers are on Row 1 2. Data are on Sheet1 and Sheet2 in the original workbook 3. data is to be copied on sheet1 and sheet2 of the new book Sub SeparateData() Dim InputWB As Workbook Dim InputSheet1 As Worksheet Dim InputSheet2 As Worksheet Dim OutputWB As Workbook Dim OutputSheet1 As Worksheet Dim OutputSheet2 As Worksheet Dim TempSheet1 As Worksheet Dim TempSheet2 As Worksheet Dim iMaxCol As Integer Dim lMaxRow As Long Dim lTempMaxRow As Long Set InputWB = ThisWorkbook Set InputSheet1 = InputWB.Sheets("Sheet1") Set InputSheet2 = InputWB.Sheets("Sheet2") Set TempSheet1 = InputWB.Sheets.Add Set TempSheet2 = InputWB.Sheets.Add TempSheet1.Range("1:1") = InputSheet1.Range("1:1").Value TempSheet2.Range("1:1") = InputSheet1.Range("1:1").Value InputWB.Activate InputSheet1.Select If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False 'For First workbook, sheet1 ' find maximum used columns on row 1. This is based on assumption that all used columns have a header on row 1 iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column 'For First workbook, sheet1 ' find maximum used rows in Column A. This is based on assumption that all maximum used rows is in column A lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(1, iMaxCol + 1) = "Temp Col" With Range(Cells(2, iMaxCol + 1), Cells(lMaxRow, iMaxCol + 1)) .FormulaR1C1 = "=IF(ISERROR(MATCH(RC1," & InputSheet2.Name & "!C1:C1,0)), 0, MATCH(RC1," & InputSheet2.Name & "!C1:C1,0))" .Copy .PasteSpecial xlPasteValues End With iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column Cells.Select If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter End If Selection.AutoFilter Field:=iMaxCol, Criteria1:=">0", Operator:=xlAnd, Criteria2:="<>" lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row If (lMaxRow > 1) Then lTempMaxRow = TempSheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1 Range(Cells(2, 1), Cells(lMaxRow, iMaxCol - 1)).Copy TempSheet1.Range("A" & lTempMaxRow).PasteSpecial End If Selection.AutoFilter Field:=iMaxCol, Criteria1:="=0", Operator:=xlAnd, Criteria2:="<>" lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row If (lMaxRow > 1) Then lTempMaxRow = TempSheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1 Range(Cells(2, 1), Cells(lMaxRow, iMaxCol - 1)).Copy TempSheet2.Range("A" & lTempMaxRow).PasteSpecial End If If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(1, iMaxCol), Cells(lMaxRow, iMaxCol)).Clear InputWB.Activate InputSheet2.Select If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False 'For SECOND workbook, sheet2 ' find maximum used columns on row 1. This is based on assumption that all used columns have a header on row 1 iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column 'For SECOND workbook, sheet1 ' find maximum used rows in Column A. This is based on assumption that all maximum used rows is in column A lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(1, iMaxCol + 1) = "Temp Col" With Range(Cells(2, iMaxCol + 1), Cells(lMaxRow, iMaxCol + 1)) .FormulaR1C1 = "=IF(ISERROR(MATCH(RC1," & InputSheet1.Name & "!C1:C1,0)), 0, MATCH(RC1," & InputSheet1.Name & "!C1:C1,0))" .Copy .PasteSpecial xlPasteValues End With iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column Cells.Select If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter End If Selection.AutoFilter Field:=iMaxCol, Criteria1:="=0", Operator:=xlAnd, Criteria2:="<>" lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row If (lMaxRow > 1) Then lTempMaxRow = TempSheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1 Range(Cells(2, 1), Cells(lMaxRow, iMaxCol - 1)).Copy TempSheet2.Range("A" & lTempMaxRow).PasteSpecial End If If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(1, iMaxCol), Cells(lMaxRow, iMaxCol)).Clear TempSheet1.Select Columns(2).Delete TempSheet2.Select Columns(2).Delete Set OutputWB = Workbooks.Add Set InputSheet1 = OutputWB.Sheets("Sheet1") Set InputSheet2 = OutputWB.Sheets("Sheet2") InputWB.Activate TempSheet1.Select lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lMaxRow, iMaxCol)).Copy OutputWB.Activate InputSheet1.Select ActiveSheet.Paste InputWB.Activate TempSheet2.Select lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lMaxRow, iMaxCol)).Copy OutputWB.Activate InputSheet2.Select ActiveSheet.Paste TempSheet1.Delete TempSheet2.Delete Set TempSheet1 = Nothing Set TempSheet2 = Nothing Set InputSheet1 = Nothing Set InputSheet2 = Nothing Set InputWB = Nothing Set OutputSheet1 = Nothing Set OutputSheet2 = Nothing Set OutputWB = Nothing End Sub

THIRU · Answer

THE SOULUTION IS SIMPLE. COPY ENTIRE COLUMN AS U REQUIRED FROM BOOK1 THEN PASE AS SPECIALLY IN BOOK 3.

NOTE: SPECIAL PASTE MEENS IS NOT EQUEL TO NORMAL PASTE. WHILE PASTING SELECT THE CELL / RIGHT CLICK / SELCET PASTE SPECIAL / CLICK VALUES OPTION / OK

BY.
THIRUMAL

Comparing two excelfile worksheets

4 responses

Similar discussions

Subscribe To Our Newsletter!