I have two tabs with about 3000 rows, each row has a primary key. I want to search by primary key from one tab to another and make sure all the fields match.(Each primary key has its own price,quantity,type......) What is the easiest approach? I don't want to loop through each fields since there are 10 fields that I need to compare, loops be relatively slow.
- 1Have you considered using ADO and writing a SQL statement, e.g. a join? Should be pretty quick.Ryan Wildry– Ryan Wildry2016-07-28 20:21:14 +00:00Commented Jul 28, 2016 at 20:21
- No, I actually have no idea about that... any other ways?mdmlll– mdmlll2016-07-28 20:32:30 +00:00Commented Jul 28, 2016 at 20:32
- Loops can be fast enough - you don't have that much data.Tim Williams– Tim Williams2016-07-28 23:29:55 +00:00Commented Jul 28, 2016 at 23:29
- Possible duplicate of What is the most efficient/quickest way to loop through rows in VBA (excel)?Tom K.– Tom K.2016-07-29 07:51:14 +00:00Commented Jul 29, 2016 at 7:51
- What do you mean by "make sure all fields match"? Do you have columns in both tables that must have the same value (if same PK)? Are those fields in the same column in both tables? If so you could merge the tables in a new sheet and remove duplicates and then there should only be unique PKs left.Hubisan– Hubisan2016-07-29 10:26:03 +00:00Commented Jul 29, 2016 at 10:26
Add a comment |
1 Answer
You may do it using built-in excel functions.
On both sheets, for each row, you may join all field content's on a new column using CONCATENATE function (or & operator).
Then use VLOOKUP or COUNTIFS functions for matching entries between sheets using the primary key and the contents of this new column.
