I am trying to find out the same item from 2 different database: I have 2 columns of product code, for example A1 contains A100101 and B1 contains A.1.001.01, knowing that they are the same item but I need to construct a list that will do lookup on one column and then result output referring to the other column.....
I had serached for many forums and a couple of those recommend to creat a fuzzy match - however I am not good at writing vba.
Any recommendations by applying existing Excel function?
As you described your table has two columns, first has the list of product code and the other one has another name for the same product. You want to list all unique products in the second column. You can try the below steps:
1. Copy the data from your original sheet into another sheet.
2. If you are using Microsoft Excel 2007 and above, select Column A and B, click Data from the top ribbon, click Remove Duplicates, uncheck Column B. This will remove all the unwanted repeated values from the first column and corresponding unique values will be left in second column.
3. If you are using any older versions of Microsoft Excel, select the two columns, click the Data menu, point to Filter, and then click AutoFilter. This will add filters to the column headers. Click the drop down at the top of Column A, click Sort A to Z. this will sort the values in Column A starting from the smallest value or in alphabetical order. Once sorted you can delete all the undesired duplicate values one by one manually.
4. Column B will have your desired Data.
I should clarify my scenario - please see example below:
Column A Column B
A.1.001 A1001
A.2.001.01 A200101
A.3.002.01 A30021
So the main difference between the 2 columns of data is, Column A has an older format that contains a dot in between; whereas Column B is the updated codes and if we refer to the first 2 rows, they are the same items (i.e.: A.1.001 = A1001).
So what I am trying to achieve is to somehow do the best match so to reflect if we exclude the dots in column A, then the code will match with the one in Column B.
There is a much simpler way to remove all the dots from the old product codes.
1. Select the entire column with the product codes.
2. Press Control + F key on the keyboard, this will open Find and Replace dialog box.
3. Click Replace.
4. In Find what: box, type a dot.
5. Leave the Replace with: box empty.
6. Click Replace All, all the dots will be removed from the product codes.
