• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Most common string in named range

D

DaveTurton

New Member
Hi


I have the following data. Its actually an excel list. I've named all data under the hearder row 'Week5_data'

[pre]
Code: 
Name	Time	Name	Time	Name	Time	Name	Time	Name	Time	Name	Time
Mike	09:39	Sam	09:04	Adam	12:45	John	08:35	Steven	10:06	John	08:57
Paul	09:40	Tom	10:44	James	09:41	John	10:03	Steven	09:37	John	06:12
Paul	10:43	Sam	12:55			John	11:18	John	10:53	Paul	10:39
Paul	11:03					Peter	11:36	Mark	11:38	Paul	11:00
Paul	12:14					Mike	12:42			John	12:30
John	11:54[/pre]

I am trying to find which name appears the most in the named range.


I did a search and found the below link


http://chandoo.org/wp/2011/10/31/using-array-formulas-to-find-count/


I've modified the formula to take into account the named range:


[code]=INDEX(Week5_data,MATCH(MAX(COUNTIF(Week5_data,Week5_data)), COUNTIF(Week5_data,Week5_data),0))


Have entered with Ctrl + Shift + Enter


But am getting a #N/A[/code] error.


Any help would be appriciated.


Thank you
 
Hi DaveTurton,


Is all your names are in column A and Time is in column B, if yes please do as below:


You have defined all the data under the header row 'Week5_data' however I think you need to modify the data range in the defined name.


Press Alt+IND to open the Name Manager and select the defined name to modify the range to "A1:A26" under the name 'Week5_data' , if all the names are in column A.


As I did that and your above formula giving me correct result i.e. "John"


Please let me know if it works for you.


Thanks & Regards,

Anupam Tiwari
 
Hi Anupam


thank you for your response. It is not just the first column I want the formula to look at. I wish it to look in all columns entitled 'Name' and return the most common text string from them all combined.


Unfortunately it is not an option to put them all in one column. as each column has a different meaning
 
This is a bit of a beast, but appears to work:

=INDEX(Week5_data,

MAX((MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))=

ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))*ROW(Week5_data))-ROW(Week5_data),

MATCH(MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data)),

INDEX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data),

MAX((MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))=

ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))*ROW(Week5_data))-ROW(Week5_data)),

0))


Confirm as array


Gist of how it works (or at least it should work).

1. We need to find the max count. This is needed several times, and is found with this portion:

MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data)

I'l refer to this value as M_X


Next, we need to know which row to look at. We take M_X and compare it against of a table containing all counts, then multiply by row number creating a new array. We take the max row number. This gives us the 2nd argument for INDEX function.


Then, we need the column. We first find the correct row to look at using previous step (thus why lines 6-7 are a repeat of lines 2-3), and then do a MATCH function to find M_X within that specific row of our table.
 
Hi Luke


It certainly does look a bit of a beast. I'm getting a #Value error when using it though. Would you mind at all reposting the uploaded file with your formula in so I can take a look?


Thanks for all your help
 
I'm afraid I can't access the file, but I bet I know the problem. The two "-1" bits I put in there were to adjust for starting row of Week5_data, but I'm guessing that in the actual file it doesn't start on row 2. Change each of those bits to be:

-ROW(Week5_Data)


and you should be good. At least, it better be. There's aren't any other cell references in the formula. =P

I'll edit my previous post to show the correction.
 
I'm afraid it's still not working. What is the reason you can't access the file, I think I set the sharing up correctly on it?
 
It's not you, it's me. I can't access files from my location. =(


Can you try setting up a new blank workbook with this sample, and then go from there? In new workbook, in A1:L7

[pre]
Code: 
Name	Time	Name	Time	Name	Time	Name	Time	Name	Time	Name	Time
Mike	9:39	Sam	9:04	Adam	12:45	John	8:35	Steven	10:06	John	8:57
Paul	9:40	Tom	10:44	James	9:41	John	10:03	Steven	9:37	John	6:12
Paul	10:43	Sam	12:55			John	11:18	John	10:53	Paul	10:39
Paul	11:03	Paul				Peter	11:36	Mark	11:38	Paul	11:00
Paul	12:14					Mike	12:42			John	12:30
John	11:54
[/pre]
A2:L7 is a named range called Week5_data


Formula is in A10, confirmed as an array (with the curly braces)

=INDEX(Week5_data,

MAX((MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))=

ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))*ROW(Week5_data))-ROW(Week5_data),

MATCH(MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data)),

INDEX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data),

MAX((MAX(ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))=

ISTEXT(Week5_data)*COUNTIF(Week5_data,Week5_data))*ROW(Week5_data))-ROW(Week5_data)),

0))


Should be showing an answer of John.
 
Confirmed with CSE:

=INDEX(Week5_data,MIN(IF((MAX(COUNTIF(Week5_data,Week5_data))=COUNTIF(Week5_data,Week5_data)),1,9999)*

ROW(Week5_data))-1,

MIN(IF((MAX(COUNTIF(Week5_data,Week5_data))=COUNTIF(Week5_data,Week5_data)),1,9999)*COLUMN(Week5_data)))


Edit:=Adjust the row number. The value should be 1 less than starting row.


I have entered at few places as the formula doesn't get wrapped in reply window correctly.
 
It shouldn't matter.

To debug, if you could check to see if it works in the new dummy workbook, with data starting in A1, that would let us confirm that the formula will work on your machine (and not some weird version compatibility error).


Then, still in sample book, try moving the data around to different parts of sheet. This will test robustness of formulas.


Then, try and see what's different between sample book and your actual book.
 
Yes, sorry, working now. I must have been doing something weird. Thanks a lot for your help Luke.


Shivallabha - thanks for your formula - unfortunately that returns me the most common time not name. But thank you for assisting.
 
Hello,

Here is one more formula.... slightly shorter:

=INDIRECT("R" & SUBSTITUTE(MIN(IF(IF((week5_data <>"") * NOT(ISNUMBER(week5_data+0)), COUNTIF(week5_data, week5_data))=MAX(IF((week5_data <>"") * NOT(ISNUMBER(week5_data+0)), COUNTIF(week5_data, week5_data))), ROW(week5_data)+COLUMN(week5_data)%)), ".", "C"), FALSE)

enter with Ctrl + Shift + Enter


Cheers,

Sajan.
 
Hi Sajan,


This part of the formula:

ROW(Week5_data)+COLUMN(Week5_data)%

is pure brilliance! Very nice way of getting the row and column in 1 shot.
 
Hi Luke,

Thanks! But I am afraid I cannot take credit for it, since I may have seen that technique used somewhere else! After all, our techniques are an amalgamation of things we observe ourselves and see elsewhere.


I had started down the path you took, and then got lazy and took the easy way out! :-D

Glad to see that you persisted!!


Regards,

Sajan.
 
@Sajan

Hi!

Very nice formula, I agree with Luke M, chapeau, monsieur! And it's true: we're all a mix of thieves, burglars and grabbers, we started walking around the world with a little screwdriver and after a while our tool box is overflowing of new things... many created or discovered by ourselves (a very few), many learned or studied (a few), many seen and stolen (a lot).

Regards!

PS: BTW, Luke M always take the laziest way, I discovered, ehhhmmm, learned, ehhhmmm, stoled, ... ehhhmmm!, saw I meant. From him.
 
À votre bon cœur !


I might have picked up some habits from Luke too, I am afraid! :p

(But I consider that an honor to do so!)
 
@Luke M

Hi!

Didn't I tell you that I absolutely agree with maximizing resource use specially if related to move my fingers or move my neurons faster? My tendons and axons are very thankful.

Regards!


@Sajan

Hi!

Give it a try, it's easy, I feel as acting so naturally when I do so...

Regards!
 
You must log in or register to reply here.
Back
Top