MS Excel question

[edmundoab]

~~~ PSA ~~~

Off Topic is NOT the Software - Apps, Programming and Games forum.

AnandTech Moderator

---

Hello guys,

I didn't get much response in the "Software-Apps" category page, thought there maybe more people reading the Off "Topic" page

Here is my question,
I need to have a formula in Excel where it should be able to detect the amount of duplicates in one data range

say S/N: abc123 found in location A3,C2, and H4, the column with the formulation should be able to give me like "abc123--3"

in the previous thread, thanks to power engineer, he suggested a line of code for me.
but that only works for one column of data to compare with one specific entry

example in column D10:-

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

how do I do it in a range? arrays needed for this?
i tried looking online but there are functions for "MAX" and "SUM", but I am not sure how to even use them to tackle this issue

any help from computer wizz out there?

 

[edmundoab]

bump

 

[imported_malcontent]

Marked for later. I am interested in this also.

 

[Hector13]

If you just want the number of occurences of a string in your range, you can use an array function like:

=sum((DATA_RANGE = VALUE)*1)

for example, if your range of values is in A1:C3 and the value you want the count for is in E1, you would use:

=sum(($A$1:$C$3=E1)*1)

make sure you press Control-Shift-Enter when entering these forumals.

 

[edmundoab]

but I dont want just a string in particular,

i want it to be able to detect more then one duplicated entries and indicate the number of occurance in that data range

should it so happen just have one duplicate so be it,
but should it have 2 or more , at least the formula should be able to show it?

 

[kranky]

See if this helps.

 

[eakers]

is there a frequency function?

 

[Hector13]

Originally posted by: edmundoab
but I dont want just a string in particular,

i want it to be able to detect more then one duplicated entries and indicate the number of occurance in that data range

should it so happen just have one duplicate so be it,
but should it have 2 or more , at least the formula should be able to show it?

I am not sure what you mean here... the formula above wil simply tell you the number of occurences in whatever range you give it. It won't matter whether there is one, none, or more than one occurence, the formula will still work.

 

[kranky]

I think he's saying he doesn't want to look for a specific string. He has a range of values, and wants a formula that will find any duplicated values in the range, and for each one which has more than one occurrence it should show the value itself and the number of occurrences.

 

[edmundoab]

Originally posted by: kranky
I think he's saying he doesn't want to look for a specific string. He has a range of values, and wants a formula that will find any duplicated values in the range, and for each one which has more than one occurrence it should show the value itself and the number of occurrences.

yes thats what I meant, thank you kranky

 

[edmundoab]

feedback greatly appreciated, we've been trying to get this up and running since the last 3 weeks I think sigh
so far only thanks to powerengineer, at least one column works, but its gonna be one hell of a spreadsheet if I use that for 1 year of data