Good afternoon,
would you kindly assist with formula to find most common or repeated numbers in rows from "A:F"?
here is an example;
ThanksPlease Login or Register to view this content.
Sem
Good afternoon,
would you kindly assist with formula to find most common or repeated numbers in rows from "A:F"?
here is an example;
ThanksPlease Login or Register to view this content.
Sem
Last edited by JBeaucaire; 11-02-2014 at 11:16 AM.
Is each group of 5 numbers contained in the same cell?
Hi Pepe le Mokko,
Yes,thank you.Please Login or Register to view this content.
Sem
can you post sample file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I used text to columns to get each value in its own cell. This gave an array A2:AJ4. Next select AN2:AM2 and enter this array formula: (enter with Ctrl + Shift + Enter)
Formula:Please Login or Register to view this content.
Result:
B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN 1A B C D E F EXPECTED RESULT 2 1 4 5 13 16 2 6 13 16 20 3 7 9 13 16 8 10 12 13 16 11 13 15 16 17 13 14 16 18 19 13 16 3 1 2 3 12 15 1 4 9 12 19 1 5 12 18 20 1 6 12 16 17 1 7 8 12 14 1 10 11 12 13 1 12 4 1 5 9 14 17 2 3 5 9 18 4 5 6 9 12 5 7 9 16 19 5 8 9 10 11 5 9 13 15 20 5 9
I don't see how this can be done keeping the groupings in one cell for each group unless it can be done with VBA.
Last edited by newdoverman; 11-02-2014 at 01:57 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
nevdowerman,
thanks ever so much for your time and help itlooks very good solution, but i rather have a solution without to need text to columns.
Siva,
in my post#1, have sample which is exactly similar to my data.
thanks again
Sem
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
see the attached file
nflsales,
i looked at attached workbook it promising because your formula giving, one number where it should be two numbers repeated?
anyhow very much appreciated.
JBeaucare,
if its not possible with formula, then i dont mind if its vb code,thanks.
Sem
see the attached file
it is an array formula
Hi Nflsales,
thanks ever so much for your time and help very much appreciated.
i can get the first number, but i'm having problem getting the second number.how do you apply the formula?, do you copy it across and down.Because i compered both formula and they are exactly same ,i get value# error in my result???
Thanks again.
Sem
Hello Sem,
What version of excel are you using 2007 or above?
So, you only want to return MULTIPLE most common numbers, if they are occurring equal time?
eg: 13 & 16 occur 6 times in row 1, so if they occur 6 & 5 time, do you only want to return 13?
Edit: If you are on Excel 2010 or above,
G1, then copy across until get error.
=INDEX(MODE.MULT(TRIM(MID(SUBSTITUTE($A1:$F1," ",REPT(" ",20)),{0;1;2;3;4}*20+1,20))+0),COLUMNS($G1:G1))
Last edited by Haseeb Avarakkan; 11-02-2014 at 03:36 PM.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Hi Haseeb A,
thanks for your time,
every row have two numbers occur equally in all columnsand i need both numbersSo, you only want to return MULTIPLE most common numbers, if they are occurring equal time?
eg: 13 & 16 occur 6 times in row 1, so if they occur 6 & 5 time, do you only want to return 13?
Thanks again,kind regards
Sem
OK, try this
G1,
=MODE(TRIM(MID(SUBSTITUTE(A1:F1," ",REPT(" ",20)),{0;1;2;3;4}*20+1,20))+0)
H1,
=MODE(IFERROR(TEXT(TRIM(MID(SUBSTITUTE(A1:F1," ",REPT(" ",20)),{0;1;2;3;4}*20+1,20))+0,"[<>"&G1&"]0;-")+0,""))
Both are Array Formulas, must be conformed with CTRL+SHIFT+ENTER
Formula for getting 1st number and 2nd number are not same
HaseebA,
thanks very very much for perfect solution,but if i may what is the "20" and "0,1,2,3,4" stand for?
suppose my data's numser over 20 is that mean i need to change the "20" in the formula accordingly?
Thanks again
Sem
Ps.i added reputation for your excellent solution.
You are welcome. Glad it works for you :-)
0;1;2;3;4 means you have data across in 5 columns. A:F. If you have more columns change it accordingly....what is the "20" and "0,1,2,3,4" stand for...
20 is just used to separate these values by 20 spaces to get values in array.
No. Only if you have large numbers >19 character.....suppose my data's numser over 20 is that mean i need to change the "20" in the formula accordingly?...
HaseebA,
much obliged for your explaination,thanks a million.
Sem
HaseebA,
I'm very sorry to bother you but i get "#value" error when apply this formula to data with 16 columns"A:P" with numbers "1:50"
any help on this please?.Please Login or Register to view this content.
Thanks
Sem,
My bad.
{0;1;2;3;4} means you have 5 group of numbers in a cell. So just keep it as it is. No need {0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
Sorry for the confusion.
Thanks Haseeb that sorted the problem.
Kind regards
Sem
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks