Capture.PNG
Hi
I am looking for a formula that will allow me to return the value of the Name that occurs the most times in a row.
The formula need to only look at certain Cells opposed to the full row as I only want it search the Name Columns and not include what is in the Status columns.
The Name columns are actually merged over 5 columns.
Any advice is gratefully appreciated.
Is there a reason that the data is horizontal instead of listed vertically with say column A for names and column B for status? Listed vertically, each row would represent one person's record and this makes it easy to count names and gather other statistics.
EG.
Column A Column B
Name-------Status
Ben---------Employed
Thomas----Unemployed
Gary--------Student
Last edited by newdoverman; 06-30-2013 at 06:18 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
Try this array formula**:
=IFERROR(INDEX(A2:AC2,MODE(IF(MOD(COLUMN(A2:AC2),8)=1,MATCH(A2:AC2,A2:AC2,0))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
If there are no names that appear more than once the formula will return a blank.
Last edited by Tony Valko; 06-30-2013 at 07:54 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks