Matching similar data in 2 columns

[Closed]
Report
Posts
2
Registration date
Monday October 29, 2012
Status
Member
Last seen
October 30, 2012
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
Can any super Excel guru help me please?

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?

Thank you.

4 replies

Posts
11
Registration date
Wednesday October 10, 2012
Status
Member
Last seen
October 29, 2012

Would you please provide a sample file so me and the others can better understand your question?
Thanks. ;-)
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
Hi lopearrabbit,

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.

Please revert for clarification.

Posts
2
Registration date
Monday October 29, 2012
Status
Member
Last seen
October 30, 2012

Thank you so much for reponding my message :)

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.

Any suggestions would be of great help :)
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
Hi lopearrabbit,

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.

Do reply with results.