Solved: Compare Rows between 2 worksheet - Large Data

[parttime_guy]

Hi,

I need to compare the rows of two worksheets Columns from A to V

The original has about 20,000 rows and columns from A to V
The sheet which has to be compared with the original has about 16,000 rows and columns from A to V

The Solution
I inserted an extra 1st column in both the sheets and used this formula
=B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2

In the sheet (16,000 rows) which has to compared I put TRUE in the last column

Then I used this formula in the last Column of the Original sheet (20,000 rows)
=VLOOKUP(A2,'0riginal'!A:Y,25,FALSE)

The Problem
This worked but due to the large information the VLOOKUP function is running slow and the file Hangs, the size has increased to 20MB+

Is there a different approach to the solution?

[Maui_Jim]

What are you trying to accomplish with your VLOOKUP statement? If the rows in your Original and Compared sheets match, do you want to visually highlight the matches; or perhaps copy the matching rows to another worksheet?

If you wanted to highlight the rows on the Original Sheet that match the Compare Sheet, you could use Conditional Formatting.

Go to Compare Sheet and highlight Column A
Click on Name Box drop down button (upper left of spreadsheet)
Enter Range Name of: Comp_List

Go to Original Sheet
Go to cell A2 and access the Format ? Conditional Formatting menu
Select ?Formula Is? from Drop Down list
Enter this formula: =MATCH($A2Comp_List,0)
Choose the Format Pattern of your choice
Click Ok (twice)
Copy Cell A2 and Paste Special ? Formats to all cells (columns A:V)

Now the rows will be highlighted if they match the rows in your Compare sheet.

If you wanted to do something like copy the rows that match to another worksheet, you could use the following code as an example.

[vba]
Public Sub CompareColumns()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim Nm As Range, c As Range, Mcell As Range
Dim Rng As Range
Set Sh1 = Sheets("Original")
Set Sh2 = Sheets("Compare")
Set Sh3 = Sheets("Matches")

With Sh3
Range("A1:V20000").ClearContents
'Range("A:V").Select
'Range(Selection, Selection.End(xlDown)).Select
'Selection.ClearContents
End With

With Sh1
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
With Sh2.Columns(1)
For Each Nm In Rng
Set c = .Find(Nm, LookIn:=xlValues)
If Not c Is Nothing Then
Set Mcell = Sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
c.Offset(, 1).Resize(, 70).Copy Mcell
End If
Next
End With
End With
End Sub
[/vba]
This macro will compare Column A in two sheets (named Original & Compare) and copy the matching rows into a third sheet (named Matches).

Feel free to respond with more detail if I have completely missed your intent.

Regards,
Jim

[parttime_guy]

Dear Jim,
Sorry for the long delay and Thanks for taking interest in this post .
After much search, I came across this solution
Using ?&? I combined only the most important cells in both the sheets (I then gave the new columns a range name).
Used this formula
ISNA(MATCH(E3,$I$3:$I$14,FALSE)) [ie, ISNA(MATCH(combine_cell,range_name,FALSE))]

A link to this solution

http://office.microsoft.com/en-gb/excel/HA011039151033.aspx

Thanks and Best regards