I was wondering what is the best way to compare multiple sets of data. I have around 25 tables of data which are Food Product names. I need to see which food products are the same across all these sets, however the issue is that the food product names are slightly different across each set of the data.

For example, it could be Natural Organics 100% Aloe Vera Tea in one data set and in another it could be Natural Organics Aloe 100% Tea. So it gets very messy very easily.

One suggestion by another superuser was to use Fuzzy Lookup in Excel which I've been doing, except you can only compare 2 tables at a time. Is there any process where I can scale this do this?

Or is there a programing way to do this?

  • I would say SQL Server and tie a unique ID key to all products in all tables and join the tables by the unique ID key. So for example, Natural Organics 100% Aloe Vera Tea would be keyid 1, and Natural Organics Aloe 100% Tea would be keyid 2, next product name would be keyid 3 and so on. In each of the tables, the same product name would be assigned the same keyid so you could join data from all the tables with TSQL logic by keyid.... see here: w3schools.com/sql/sql_join.asp Oct 9, 2016 at 4:46
  • To expand on Homey's suggestion, the first step will need to be cleaning up the data to a usable form. Once you have clean, unique keys, there are plenty of ways to accomplish the result. One suggestion is to do the cleanup in multiple passes. As you find matches that are easy to identify, remove them from the raw list. The remaining items will become easier to work with and to spot examples of new rules you can apply. You can also work from both directions. In addition to finding and removing matches, you can identify and remove items that are unique.
    – fixer1234
    Oct 9, 2016 at 19:01
  • @fixer1234 Thanks for that, That's exactly what I'm doing now :)!
    – Bahtfeng
    Oct 12, 2016 at 3:46
  • @fixer1234 On the same topic, would anyone know the best way to find approximate matches in the same column? I made two identical tables of the same data and did a fuzzy lookup, however there is so many duplicates because EXACT (1.00) match. Another issue is that sometimes the EXACT (1.00) matches are false, I.E Natural Organics 100% Aloe Vera Tea 100ML & Natural Organics 100% Aloe Vera Tea - 100ML will give me EXACT (1.00) match so its hard to filter out the false positives from the real ones.
    – Bahtfeng
    Oct 12, 2016 at 3:48


You must log in to answer this question.

Browse other questions tagged .