LinkBack URL
About LinkBacks
I have a column of data. The data has a number or "UR" in every cell. There are no blank cells. I need to know the most number of times a number is represented in the cell? The data flows from top to bottom
I have a column of data. The data has a number or "UR" in every cell. There are no blank cells. I need to know the most number of times a number is represented in the cell? The data flows from top to bottom
Are you still using Excel 2010?
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I'm using Excel 2021
I am attempting to attach a sample spreadsheet.
Please try:Formula:
Please Login or Register to view this content.
Try:Formula:Please Login or Register to view this content.
For the most frequent number:Formula:Please Login or Register to view this content.
I want the highest number of times numbers are uninterrupted by "UR" in the column
THIS DID IT!!! Thank you HansDouwe. This was exactly what I was looking for
Looks like your requirement has changed.
Your new request is beyond me. I will declare myself out.I have a column of data. The data has a number or "UR" in every cell. There are no blank cells. I need to know the most number of times a number is represented in the cell? The data flows from top to bottom
Thanks for the feedback. Glad to have helped..
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
One more (compact) method that you can consider...
=MAX(LEN(TEXTSPLIT(TRIM(CONCAT(IF(ISNUMBER(A1:A99),"X"," ")))," ")))
Last edited by Rick Rothstein; 12-14-2023 at 01:51 AM.
I tried: =MAX(LEN(TEXTSPLIT(TRIM(CONCAT(IF(ISNUMBER(I:I),"X"," ")))," ")))
I got back "the formula contains unrecognized text"
Sorry, I thought xl2021 had the TEXTSPLIT function available, but in checking, I now see it doesn't. While my formula works in xl365 (what I have), it will not work in your version of Excel. I am a little surprised that Microsoft would not have included all of the older xl365 function into xl2021, but they apparently didn't.Originally Posted by hammerb
Try:
=MODE(A1:A13)
0r
=MODE.MULT(A1:A13)
See if this formula works for you (change the A1:A500 range to your actual range of values)...
Formula:Please Login or Register to view this content.
Last edited by Rick Rothstein; 12-15-2023 at 12:05 PM.
I'm not sure what this formula is doing. It returned "3" which is not right. It should be 13
I treid this formula. I changed A1:A500 to I:I and it displayed "#NAME?" "The formula contains unrecognized text"See if this formula works for you (change the A1:A500 range to your actual range of values)...
Formula:Please Login or Register to view this content.
Tommy90's is not what you asked for - it returns the value that appears most in the list.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Rick's uses TEXTSPLIT - that's not available in Excel 2021.
Last edited by AliGW; 12-15-2023 at 12:20 PM. Reason: Typo fixed.
Can you tell us why you are still going with this when, in post #9, you said "This was exactly what I was looking for"?
I originally made 2 posts for 2 separate requests for help. The 2 requests were very similar but different. A forum moderator closed my second request because they incorrectly thought that that request was the exact same thing as this request. They are in fact separate. I have no means of contacting the moderator to inform them of the error. SO instead I moved my second request to this thread in hopes of finding a solution to my second request.
And my second request for help is this: I have a column of numbers (Column I). old data at the top of the column. recent data at the bottom of the column. The numbers are interrupted by "UR" on a few random cells. I would like a formula that will show me how many consecutive cells of data, from bottom to top, have a number in them before being interrupted by the lowest "UR" cell.
If your data is in column I try something like this;
=LOOKUP(2,1/(I1:I9999="UR"),ROW(I1:I9999))-SMALL(IF(I1:I9999="UR",ROW(I1:I9999)),COUNTIF(I1:I999,"UR")-1)-1
This will give you the number of cells between the last "UR" and the 2nd last "UR". Is that what you want?
Last edited by GameChanger; 12-15-2023 at 08:03 PM.
no. You are very close though. I want the number of cells that contain a number between the last UR and the bottom of the column.
Then;
=MAX(IF(ISBLANK(I1:I9999),0,ROW(I1:I9999)))-LOOKUP(2,1/(I1:I9999="UR"),ROW(I1:I9999))
Perfect. Thank you GameChanger for answering one of my questions.
Thank you to HansDouwe for answering the other question.
You have both made my sports stats look awesome.
No problems.
Maybe this as an alternative:Formula:Please Login or Register to view this content.
Or a small adjustment in Trevor's formula if your data not start at row 1:Formula:Please Login or Register to view this content.
You can PM any moderator if you think they have closed a thread in error. Did you try to PM the moderator in question?
@Hans: thanks for the tweak, but it shouldn't matter provided there is a header in row 1 (that is, no blanks in column A).
To be fair, I still don't think this accurately describes the requirement.I have a column of data. The data has a number or "UR" in every cell. There are no blank cells. I need to know the most number of times a number is represented in the cell? The data flows from top to bottom
Nor this:That could be before the "UR".I want the highest number of times numbers are uninterrupted by "UR" in the column
This is what made sense (after reading it a few times):Given that you and others offered solutions, I guess it's just me.And my second request for help is this: I have a column of numbers (Column I). old data at the top of the column. recent data at the bottom of the column. The numbers are interrupted by "UR" on a few random cells. I would like a formula that will show me how many consecutive cells of data, from bottom to top, have a number in them before being interrupted by the lowest "UR" cell.
yes. it was kind of a difficult thing to describe. Thanks again for your help
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks