467,114 Members | 1,330 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,114 developers. It's quick & easy.

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
  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
  • 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
  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
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

Post your reply

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

Similar topics

12 posts views Thread by Helmut Jarausch | last post: by
1 post views Thread by Good Man | last post: by
11 posts views Thread by Russ Green | last post: by
12 posts views Thread by Martin Heuckeroth | last post: by
3 posts views Thread by Kiran B. | last post: by
Ganon11
3 posts views Thread by Ganon11 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.