Finding Duplicate In A Column and Match the Values

[srizki]

Hi all,

I have duplicated in a column, how do I match the two values that duplicate.

Thanks

 

[Joe4]

What do you mean by "match"? Exactly what do you want to happen?
It may help if you can post a sample of what your data looks like, along with your expected results.

 

[srizki]

Thanks Joe,

Here is a sample,

00920170080210373
00920170080210730
00920170080210731
01020170080210373
01020170080210730
01020170080210731
01120170080210373
01120170080210730
01120170080210731
01220170080210373
01220170080210730
01220170080210731
00120180080210373
00120180080210730 <tbody> </tbody>
00120180080210731
00220180080210373
00220180080210730
00220180080210731 There are duplicates but I want to find out where is the duplicate value?

<tbody>
</tbody><colgroup><col></colgroup>

 

[Joe4]

Since it appears that you have multiple duplicate values, how exactly do you want it to show you the duplicates?
We could use Conditional Formatting to highlight all the values that are duplicated, or even create a formula that says "Duplicate", but that won't distinguish one duplicate from another.
So based on the example you show above, please show us exactly how you want it to indicate the duplicate.
Do you want it to return the row number or something of the next duplicate or something? What if a particular value shows up more than once?

 

[srizki]

I got duplicates as you said, by formula and by conditional formatting. Now I know that there are duplicates, how do I know what two are matching? I do not know how to find out. May be it will be match 1,1 ; 2,2. I do not think it works like that. I guess I will sort the table to find out the duplicate values.

Thanks Joe, I appreciate your time.

 

[Joe4]

how do I know what two are matching?

I was trying to find out from you how exactly you want that displayed. For example, do you want different duplicates different colors? Or do you want the row number of its match returned, etc?
It could get a little complicated.

If you are have the ability to sort it, so all duplicates are in order, I think that would be the easiest way.

 

[srizki]

Thank you Joe,
I am getting all sorts of problem in this small worksheet, I am trying to match two columns, but some number such as

01020170080210370

<tbody>
</tbody><colgroup><col></colgroup>

Should be

01020170080210371 and not "0", but every time I change it manually, I goes back to 0 when I press enter. I don't know what is going on. the files are downloaded from Oracle and another software called EnergyLink.

<tbody>
</tbody><colgroup><col></colgroup>

 

[Joe4]

Are they coming in as numbers? I believe that Excel can only store 15 significant digits in a number.
You will want to bring it into Excel as text, and not numbers.