Previous Topic Next Topic

Matching similar data in 2 columns

Closed
lopearrabbit Posts 2 Registration date Monday October 29, 2012 Status Member Last seen October 30, 2012 - Oct 29, 2012 at 02:39 AM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Oct 31, 2012 at 08:40 AM
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.
Related:

4 responses

Answer 1 / 4
kobe1121 Posts 11 Registration date Wednesday October 10, 2012 Status Member Last seen October 29, 2012
Oct 29, 2012 at 04:09 AM
Would you please provide a sample file so me and the others can better understand your question?
Thanks. ;-)
0
Answer 2 / 4
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Oct 29, 2012 at 06:51 AM
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.

0
Answer 3 / 4
lopearrabbit Posts 2 Registration date Monday October 29, 2012 Status Member Last seen October 30, 2012
Oct 30, 2012 at 07:19 PM
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 :)
0
Answer 4 / 4
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Oct 31, 2012 at 08:40 AM
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.

0