By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,593 Members | 2,006 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,593 IT Pros & Developers. It's quick & easy.

Compare two Table(Any efficient way)

P: 9
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
  1. Sub MergeTable()
  2. '
  3. ''
  4. Dim ROW1 As Long
  5. Dim ROW2 As Long
  6. Dim C1 As String
  7. Dim C2 As String
  8. Dim C3 As String
  9. Dim C4 As String
  10. ROW1 = 2
  11. Do While Sheets("TABLE2").Cells(ROW1, 1).Value <> ""
  12.   ROW2 = 2
  13.   Do While Sheets("TABLE1").Cells(ROW2, 1).Value <> ""
  14.   C1 = Mid(Sheets("TABLE2").Cells(ROW1, 1), 1, 6)
  15.   C2 = Mid(Sheets("TABLE1").Cells(ROW2, 1), 1, 6)
  16.  
  17.   If ((Sheets("TABLE2").Cells(ROW1, 2).Value = Sheets("TABLE1").Cells(ROW2, 2).Value) And (C1 = C2)) Then
  18.  
  19.    Sheets("TABLE2").Cells(ROW1, 3).Value = Sheets("TABLE1").Cells(ROW2, 1).Value
  20.  
  21.    End If
  22.  
  23.    ROW2 = ROW2 + 1
  24.   Loop
  25.   ROW1 = ROW1 + 1
  26. Loop
  27.  
  28. End Sub
  29.  
  30.  
  31.  
TABLE1

Expand|Select|Wrap|Line Numbers
  1. LABEL          ID1    TAG
  2. ABA001_X1      1
  3. ABA001_X2      1 
  4. ABA001_X3      2
  5. ABA001_X4      1
  6. ABA001_X5      1
  7. ABA001_X6      2
  8. ABA002_X1      3
  9. ABA002_X2      4
  10.  
TABLE2
Expand|Select|Wrap|Line Numbers
  1. LABEL          ID1 
  2. ABA001_1        1
  3. ABA001_2        2 
  4. ABA002_1        3
  5. ABA002_2        4
  6.  
EXPECTED RESULT IN
TABLE1
Expand|Select|Wrap|Line Numbers
  1. LABEL          ID1    TAG
  2. ABA001_X1      1       ABA001_1 
  3. ABA001_X2      1       ABA001_1 
  4. ABA001_X3      2       ABA001_2
  5. ABA001_X4      1       ABA001_1 
  6. ABA001_X5      1       ABA001_1 
  7. ABA001_X6      2       ABA001_2
  8. ABA002_X1      3       ABA002_1
  9. ABA002_X2      4       ABA002_1
  10.  
Nov 7 '06 #1
Share this Question
Share on Google+
3 Replies


Expert 5K+
P: 8,434
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
  1. LABEL        ID1    TAG
  2. ABA001_X1     1    ABA001_1
  3. ABA001_X2     1    ABA001_1
  4. ABA001_X3     2    ABA001_2
  5. ABA001_X4     1    ABA001_1
  6. ABA001_X5     1    ABA001_1
  7. ABA001_X6     2    ABA001_2
  8. ABA002_X1     3    ABA002_1
  9. ABA002_X2     4    ABA002_2
(edited slightly for formatting here)
Nov 9 '06 #2

P: 9
Thank you so much
Nov 15 '06 #3

Expert 5K+
P: 8,434
Thank you so much
No problem. Let us know whether it works.
Nov 15 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.