PDA

View Full Version : counting duplicates and displaying the value



pico
11-23-2006, 07:19 AM
I have 6 values in a column. example

14236
14238
14236
14234
14238
14567

I'd like to crop this column and have unique values displayed in a different column. I'd also like the number of times the values have been duplicated beside the number.

lucas
11-23-2006, 07:31 AM
Take a look at the code in this attachment pico. Removed duplicates in column A as set up and reports in column C.....

pico
11-23-2006, 07:48 AM
Is there a way i can do this in excel without using vba?. I dont want to remove the rows. Sorry must have confused you when i said crop. I'd like to display the values and the number of times they have been duplicated.

lucas
11-23-2006, 08:02 AM
just put an apostrophe in front of this line as shown here:

'Rows(Row).Delete

It still needs to sort them though, will that cause a problem?

pico
11-23-2006, 08:03 AM
Is there a solution using excel formulas? maybe exact and count?...No vba please

lucas
11-23-2006, 08:14 AM
give me a minute. I think so

lucas
11-23-2006, 08:29 AM
Try this pico, you have to autofilter first....then use formula's for the count.

pico
11-23-2006, 08:44 AM
The autofilter method is too tedious. I'd like to automatically output the result without the user having to use excel. I have been trying to come up with a solution using exact and countif ...but the solution still eludes me. Thank you for the help anyway.

lucas
11-23-2006, 09:11 AM
I will revisit this later but its a holiday here and family duties call so I must abandon you for now....sorry.

pico
11-23-2006, 09:12 AM
HAPPY THANKSGIVING!:beerchug:

pico
11-24-2006, 08:21 AM
Can anyone give me a solution regarding this problem using excel functions? no vba please. I'd like to automatically count the number of duplicates

Shazam
11-24-2006, 10:59 AM
Can anyone give me a solution regarding this problem using excel functions? no vba please. I'd like to automatically count the number of duplicates


You can use this formula to extract unique values. Input formula in cell F2 and copy down.

The formula is an-array must hold down:

Ctrl,Shift,Enter

=INDEX($A$2:$A$30,MATCH(TRUE,ISNA(MATCH($A$2:$A$30,F$1:F1,0)),0))

I attach a sample workbook below.

Bob Phillips
11-24-2006, 11:05 AM
Try this

pico
11-24-2006, 11:37 AM
Shazam i tried using your solution. But i cant seem to replicate the formula properly. I have attached my file.

Shazam
11-24-2006, 12:17 PM
Look at the attachment below.

pico
11-24-2006, 12:36 PM
K. I Should have included the formulas needed to calculate the values earlier. Your new formula you provided does not seem to work when iam using the lookup function to calculate my table

Shazam
11-24-2006, 02:18 PM
K. I Should have included the formulas needed to calculate the values earlier. Your new formula you provided does not seem to work when iam using the lookup function to calculate my table


It works for me. Look at the samlpe workbook below.

pico
11-24-2006, 08:00 PM
Hi Shazam the code works thank you. Would you mind explaining what the 10^307 does in the formula? =LOOKUP(10^307,CHOOSE({1,2},0,INDEX($B$38:$B$42,MATCH(TRUE,ISNA(MATCH($B$38 :$B$42,B$43:B43,0)),0))))

Shazam
11-24-2006, 10:09 PM
Hi Shazam the code works thank you. Would you mind explaining what the 10^307 does in the formula? =LOOKUP(10^307,CHOOSE({1,2},0,INDEX($B$38:$B$42,MATCH(TRUE,ISNA(MATCH($B$38 :$B$42,B$43:B43,0)),0))))


CHOOSE function has 2 value arguments. The first formula is the Zero string. The second is your INDEX function. The array of numbers {1,2} is how the CHOOSE Function returns an array of values, not just one. LOOKUP(10^307 then returns the last value in this array. The standard way is the 9.99999999999999E+307 not the 10^307 .

Bob Phillips
11-25-2006, 04:43 AM
The standard way is the 9.99999999999999E+307 not the 10^307 .

But totally counter-intuitive, whereas 10^307 isn't, and very hard to type in. I personally use 1E+300, or even 99^99, it is just a very large number required, it isn't really necessary to be the biggest Excel can hold.

Shazam
11-25-2006, 10:11 AM
it isn't really necessary to be the biggest Excel can hold.


I firmily agree with you. I've been critized using diifferent methods for the last value Lookup formula. I've been told I should use the 9.99999999999999E+307 because I need to keep up it structured, Like how we write. We write right to left not left to right. Silly I cant think out side of the box. Well anyways I like the 99^99 as well for me using the 10^307 is just a habit of mine.

lucas
11-25-2006, 07:33 PM
Hi pico,
I was regretting not getting back to this thread sooner but I see you have been getting excellent help....It doesn't matter but can I ask why you don't want a vba solution for this?

pico
11-27-2006, 08:19 AM
I have problems with this formula. When i copy over the code from shazams file and use it in my workbook it works!. But, when i try to use the same method and write another formula on the sheet it does not work!. What am i doing wrong?..I use control Shift Enter after i type the formula...The field only displays a 0.

Bob Phillips
11-27-2006, 08:39 AM
Post the offending workbook.

pico
11-27-2006, 08:53 AM
I have marked the formula that does not work yellow and the one that works green. They are both the exact same formulas except for the tables used.

lucas
11-27-2006, 10:14 AM
Try this in B35
=LOOKUP(10^307,CHOOSE({1,2},0,INDEX($B$29:$B$33,MATCH(TRUE,ISNA(MATCH($B$29 :$B$33,B$34:B34,0)),0))))

it is an array...