How to quickly find partial duplicates in an Excel column？
Find partial duplicates with formulas
Find partial duplicates with Kutools for Excel
To find partial duplicates from a column, you can do as below:
1. Select a blank cell next to the IP, B2 for instance, and enter this formula =LEFT(A2,9), drag auto fill handle down to apply this formula to the cells you need. See screenshot:
2. Then go to next cell beside the formula1, C2 for instance, enter this formula =COUNTIF(B:B,B2), and drag fill handle over the cells you want. Then the result 2 (or other number bigger than 1) indicates the relative IP address are duplicates, 1 indicates unique. See screenshot:
Tip: in above formulas, A2 is the first IP address, and 9 is the first 9 characters you want to compare, you can change as you want.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
If you have Kutools for Excel, you can combine its Split Cells and Select Duplicate & Unique Cells utilities.
After free installing Kutools for Excel, please do as below:
1. Select the IP addresses and click Kutools > Merge & Split > Split Cells. See screenshot:
2. In the Split Cells dialog, check Split to Columns option in Type section, and check Specify width option and enter 9 into the next textbox. See screenshot:
Tip: 9 indicates to split cells by every 9 characters.
3. Click Ok, and a dialog pops out to remind you select a cell to place the split value, here select H2 for instance. See screenshot:
4. Then the IP addresses have been split to two parts, one part includes first 9 character, the other includes last two characters.
5. And select the list of first part with includes first 9 characters, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:
6. And in the Select Duplicate & Unique Cells dialog, check All duplicates (Including 1st one) option, and you also can format a different background or font color for the duplicates. See screenshot:
7. Click Ok, a dialog pops out to tell you the number of duplicates. Now the duplicate IP addresses have been selected and format with colors. See screenshot: