Question about MS Excel

[edmundoab]

Hi there tech experts, a colleague of mine asked me if there is a function in MS EXCEL to actually Identify Duplicate Entry numbers automatically

rather then she has to manually search/Find for the same number in the entire spread sheet over and over again.

I could not answer her that question, and thought if u guys maybe have a clue if such function may or may not exist?

 

[edmundoab]

something very similar to the Pivot Function

however, when she tried to use it,
the application does not return the amount of times a particular data has been repeated

say RTP456 (1)

and should it have a duplicate

RTP890(2)

does that give a rough idea of what my question would be?

 

[PowerEngineer]

I have only a very rough idea of what your question is, but here goes:

I'm going to assume that your friend is making entries into a column, perhaps from C5 through C104. One way to spot duplicate entries would be to add the following formula adjacent to the entries in D5 through D104. For D10:

=IF(LEN(C10)>0,IF(COUNTIF(C$1:C9,C10)>0,"DUPLICATE",""),"")

Assuming there's actually an entry, this will count the number of matches above it. Obviously if the count is greater than zero, then this entry is a duplicate.

Good luck!

 

[edmundoab]

wow, interesting formula

are you saying that I should do one of those formulas and drag it down the column right?

yeah I think you got my question answered

so, in your command line, does it say "DUPLICATE" or does it show an actual counter of duplicate entries? ie. (1)--> one duplicate entries
(2) --> meaning 2 duplicate entries

 

[edmundoab]

In anycase, I tested the code in office today

and yes it says Duplicate,

if I want to use a counter instead that could detect the number of time an entry has been used,
what else should I add to it?

i++ increment?

 

[PowerEngineer]

Assuming these "numbers" are essentially whole numbers:

=IF(LEN(C10)>0,IF(COUNTIF(C$1:C9,C10)>0,TEXT(C10,"0") & " -- " & TEXT(COUNTIF(C$1:C9,C10)+1,"0"),""),"")

If the "numbers" aren't actually numbers, but text strings:

=IF(LEN(C10)>0,IF(COUNTIF(C$1:C9,C10)>0,C10 & " -- " & TEXT(COUNTIF(C$1:C9,C10)+1,"0"),""),"")

 

[edmundoab]

OK, speaking of which, it could be text strings that have a combo of number and letters

so, I'll try that code and give some feedback later on.

 

[edmundoab]

IT WORKS EXACTLY AS I WAS HOPING TO GET.
THANK YOU SO MUCH POWER ENGINEER

 

[edmundoab]

Hi there again,

does your code works for more then one column?

I tried:-


IF(LEN(I13)>0,IF(COUNTIF(H$1:I13,I13)>0, I13&" -- "& TEXT(COUNTIF(H$1:I13,I13),"0"),""),"")


But that still needs to compared with column "I13"
Assuming if the repetition is within the range but not comparing with I13, then I will not be able to see a repeat

 

[edmundoab]

bump for help

 

[PowerEngineer]

Yes, it should, but you'll need to subtract one since you've included I13 in the range and it will always match itself.

IF(LEN(I13)>0,IF(COUNTIF(H$1:I13,I13)>1, I13&" -- "& TEXT(COUNTIF(H$1:I13,I13)-1,"0"),""),"")

 

[edmundoab]

makes sense, coz I removed the +1 and still did not get a correct return
will do that

 

[edmundoab]

the code still makes a comparison to the particular cell in "I13" for instance

say I have

A1 B1
abc123 abc123

A2 B2
hjk879 hgt786

then say if I use the code in reference to "B2 cell", the code will not detect abc123 as having 2 duplicates

 

[edmundoab]

how about Array functions to be used?
will it make it easy?

But I went on google, and it i saw functions like "SUM" and "MAX", how do u compare in an array?

 

[edmundoab]

bump

 

[PowerEngineer]

Originally posted by: edmundoab
the code still makes a comparison to the particular cell in "I13" for instance

say I have

A1 B1
abc123 abc123

A2 B2
hjk879 hgt786

then say if I use the code in reference to "B2 cell", the code will not detect abc123 as having 2 duplicates

That's certainly true.

The starting premise for this calculation is that you have it duplicated in a column adjacent to the data columns and copied down through the same number of rows. As you say, the calculation in row 2 will not notice that the two entries in row 1 are identical -- but the calculation in row 1 will.

 

[edmundoab]

so is it possible to implement an array type of range to detect duplicates in the range?

otherwise, should it only do in one row, the file is going to be a very very long one

 

[edmundoab]

bump, oh btw, if there isn't a way i can solve this ,
then I guess I gotta stick to the single line one

a little inefficient, but I guess I'll make do with it rather then nothing at all. :-(

 

[edmundoab]

what does the curly brackets do? with ctrl+shift+enter?

doesn't seem to compare the entire range for duplicates from the looks of it

 

[edmundoab]

bump, still not able to get a formula that is able to detect one or more duplicated entries and display the amount of entries found in a range of data selected,

 

[MrBond]

If there's not a formula that can do it, you may have to resort to a VB Macro.

I'm not very good at macro making myself, but maybe someone else will come along with one that will work.

 

[edmundoab]

yeah I am hoping for it,
thanks for the bump!