467,114 Members | 1,330 Online

# Compare two Table(Any efficient way)

 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   Nov 7 '06 #1
• viewed: 1237
Share:
3 Replies
 Expert 8TB 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) Nov 9 '06 #2
 Thank you so much Nov 15 '06 #3
 Expert 8TB Thank you so much No problem. Let us know whether it works. Nov 15 '06 #4