Compare two Table(Any efficient way)

DONE1

I wrote a code as follows to compare 2 tables on diff worksheet.
Each work sheet has about 6000 Rows.
The code works but appears to Work very slow.
Is there a more efficient way to do it.

Expand|Select|Wrap|Line Numbers

 
Sub MergeTable()

'

''

Dim ROW1 As Long

Dim ROW2 As Long

Dim C1 As String

Dim C2 As String

Dim C3 As String

Dim C4 As String

ROW1 = 2

Do While Sheets("TABLE2").Cells(ROW1, 1).Value <> ""

  ROW2 = 2

  Do While Sheets("TABLE1").Cells(ROW2, 1).Value <> ""

  C1 = Mid(Sheets("TABLE2").Cells(ROW1, 1), 1, 6)

  C2 = Mid(Sheets("TABLE1").Cells(ROW2, 1), 1, 6)
 
  If ((Sheets("TABLE2").Cells(ROW1, 2).Value = Sheets("TABLE1").Cells(ROW2, 2).Value) And (C1 = C2)) Then
 
   Sheets("TABLE2").Cells(ROW1, 3).Value = Sheets("TABLE1").Cells(ROW2, 1).Value
 
   End If
 
   ROW2 = ROW2 + 1

  Loop

  ROW1 = ROW1 + 1

Loop
 
End Sub

TABLE1

Expand|Select|Wrap|Line Numbers

 
LABEL          ID1    TAG

ABA001_X1      1

ABA001_X2      1 

ABA001_X3      2

ABA001_X4      1

ABA001_X5      1

ABA001_X6      2

ABA002_X1      3

ABA002_X2      4

TABLE2

Expand|Select|Wrap|Line Numbers

 
LABEL          ID1 

ABA001_1        1

ABA001_2        2 

ABA002_1        3

ABA002_2        4

EXPECTED RESULT IN
TABLE1

Expand|Select|Wrap|Line Numbers

 
LABEL          ID1    TAG

ABA001_X1      1       ABA001_1 

ABA001_X2      1       ABA001_1 

ABA001_X3      2       ABA001_2

ABA001_X4      1       ABA001_1 

ABA001_X5      1       ABA001_1 

ABA001_X6      2       ABA001_2

ABA002_X1      3       ABA002_1

ABA002_X2      4       ABA002_1

Killer42

I wrote a code as follows to compare 2 tables on diff worksheet.
Each work sheet has about 6000 Rows.
The code works but appears to Work very slow.
Is there a more efficient way to do it.
...

You might be better off using one of Excel's built-in lookup functions to populate the Tag column. For example, I recreated your two tables from the data shown in your post, but in TABLE2 I reversed the order of the two columns. Then I used this formula in cell C2 of TABLE1, I placed this formula =VLOOKUP(B2,TABLE2!$A$2:$B$5,2,FALSE). I copied this cell and pasted it down the rest of the column, and this is the result...

Expand|Select|Wrap|Line Numbers

 
LABEL        ID1    TAG

ABA001_X1     1    ABA001_1

ABA001_X2     1    ABA001_1

ABA001_X3     2    ABA001_2

ABA001_X4     1    ABA001_1

ABA001_X5     1    ABA001_1

ABA001_X6     2    ABA001_2

ABA002_X1     3    ABA002_1

ABA002_X2     4    ABA002_2

(edited slightly for formatting here)

DONE1

Thank you so much

Killer42

Thank you so much

No problem. Let us know whether it works.

Compare two Table(Any efficient way)

Post your reply

Similar topics