Matching similar data in 2 columns
Closed
lopearrabbit
Zohaib R
- Posts
- 2
- Registration date
- Monday October 29, 2012
- Status
- Member
- Last seen
- October 30, 2012
Zohaib R
- Posts
- 2368
- Registration date
- Sunday September 23, 2012
- Status
- Member
- Last seen
- December 13, 2018
Related:
- Matching similar data in 2 columns
- Data validation 2 columns - Guide
- Vlookup to find missing data in 2 columns - Forum - Excel
- Excel formula to find matching text in 2 columns - Guide
- Compare data in seperate columns to find likely matches ✓ - Forum - Excel
- Matching 2 columns in Excel ✓ - Forum - Excel
4 replies
kobe1121
Oct 29, 2012 at 04:09 AM
- 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. ;-)
Thanks. ;-)
Zohaib R
Oct 29, 2012 at 06:51 AM
- Posts
- 2368
- Registration date
- Sunday September 23, 2012
- Status
- Member
- Last seen
- December 13, 2018
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.
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.
lopearrabbit
Oct 30, 2012 at 07:19 PM
- 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 :)
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 :)
Zohaib R
Oct 31, 2012 at 08:40 AM
- Posts
- 2368
- Registration date
- Sunday September 23, 2012
- Status
- Member
- Last seen
- December 13, 2018
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.
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.
