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
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Oct 31, 2012 at 08:40 AM
Related:
- Matching similar data in 2 columns
- Tentacle locker 2 - Download - Adult games
- Five nights in anime 2 - Download - Adult games
- Feeding frenzy 2 download - Download - Arcade
- Euro truck simulator 2 download free full version pc - Download - Simulation
- Display two columns in data validation list but return only one - Guide
4 responses
kobe1121
Posts
11
Registration date
Wednesday October 10, 2012
Status
Member
Last seen
October 29, 2012
Oct 29, 2012 at 04:09 AM
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Oct 29, 2012 at 06:51 AM
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
Posts
2
Registration date
Monday October 29, 2012
Status
Member
Last seen
October 30, 2012
Oct 30, 2012 at 07:19 PM
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Oct 31, 2012 at 08:40 AM
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.