to find numbers >=3 using formula

[Shalini Upadhyay]

I have a range a1:e10 which is full of many like between randbetween 10-100..... there are some repeated numbers also....I have to find to show that numbers which are repeated >=3 times in selected range... and I want to show the answer in G4 cell....plz suggest me what formula will using in it.

[Sam Capricci]

I have something that is an alternative to what you asked for.
I tried to replicate what you have with randbetween in the A1 to E10 range. I found that often I would get more than one set of numbers that were greater than 2 by count, though sometimes none.
So I set up a matrix in G1:K10 to show which numbers meet that criteria using a countif formula.
this is the countif formula...=IF(COUNTIF($A$1:$E$10,A1)>=3,A1&" - "&COUNTIF($A$1:$E$10,A1),"")
and attached is the sample sheet. Everytime you click in and out of a cell the numbers will change.

[samba_ravi]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Shalini Upadhyay]

But in your attachment here you use g1:k10 to show the answers.....But I want to use all the answers only in one cell using comma ","....for example num1,num2,num3......etc.....then whts the formula to find it...plzz suggest

[Sam Capricci]

This one is an adjustment to what I gave you earlier with your result in G4 using an array formula which means (for mine) you need the helper columns of M, N and O and you could do away with the matrix in H1:L10 as it only shows as proof.

[Shalini Upadhyay]

ok...here is the attatchment plzz check it my question..

[samba_ravi]

you can get result in single row or in single column with formula
if you want result in single cell then you need a UDF (VBA)

[Sam Capricci]

you posted your reply (post 4) while I was posting my second example, if you need VBA that is not in my wheelhouse.

[Glenn Kennedy]

Try this out:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then use this array formula:

=ConcatAll(IF(COUNTIF(A$1:E$10,A$1:E$10)>2,A$1:E$10,""),", ",TRUE)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

and... just in case you want them in ascending order, use this array formula, instead:

=ConcatAll(IFERROR(SMALL(IF(COUNTIF($A$1:$E$10,A$1:E$10)>2,$A$1:$E$10,""),ROW(1:50)),""),", ",TRUE)

[Shalini Upadhyay]

you can get result in single row or in single column with formula
if you want result in single cell then you need a UDF (VBA)

Ok.. tell me how can I get the answer in single row or single column....plzz send me your answer sheet

[Glenn Kennedy]

Using an array formula (no VBA).

=IFERROR(SMALL(IF(COUNTIF($A$1:$E$10,$A$1:$E$10)>2,IF(COUNTIF($I$2:I2,$A$1:$E$10)=0,$A$1:$E$10)),ROWS($1:1)),"")

[samba_ravi]

Try below array formula in I3 and copy towards down

Please Login or Register  to view this content.

see the attached file