counting duplicates and displaying the value

[pico]

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]

Take a look at the code in this attachment pico. Removed duplicates in column A as set up and reports in column C.....

[pico]

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]

just put an apostrophe in front of this line as shown here:
[VBA]
'Rows(Row).Delete
[/VBA]
It still needs to sort them though, will that cause a problem?

[pico]

Is there a solution using excel formulas? maybe exact and count?...No vba please

[lucas]

give me a minute. I think so

[lucas]

Try this pico, you have to autofilter first....then use formula's for the count.

[pico]

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]

I will revisit this later but its a holiday here and family duties call so I must abandon you for now....sorry.

[pico]

HAPPY THANKSGIVING!

[pico]

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]

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]

Try this

[pico]

Shazam i tried using your solution. But i cant seem to replicate the formula properly. I have attached my file.

[Shazam]

Look at the attachment below.

[pico]

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]

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]

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]

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]

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.