3245

  • Thread starter Thread starter rossmolden
  • Start date Start date
R

rossmolden

I want to display what number appears in the same row the most with the
number 1.
So this example, would be 2 as it appears with 1 in the same row 3 times.

4 1 3 2
2 1
4 3 2
1
2 3 4 1
 
Hi,

1. This is not an application error so why post it here?
2. It is not good newsgroup manners to cross post
3. There is probably no formula approach to do this so you will need to use
one of the VBA solutions you have already recieved.
 
One formulas play below as responded in your multi-post in .misc yesterday.
Please don't multi-post. Do high-five ALL responses which help in any/some
way to answer your query by pressing the YES button below

--------------
Assume source data as posted within A1:D5
In F1: =IF(OR(A1={"",1}),"",IF(COUNTIF($A1:$D1,1),A1,""))
Copy F1 across to I1, fill down to I5. This creates the criteria matrix
which isolates only the rows containing "1" (the key association criteria),
and simultaneously cleans off blank source cells and the key 1's in the
resulting matrix.

Then in J1: =MODE(F1:I5)
nails the result. Lightly tested, J1 seems to return correct results. In the
event of ties, it'll return the num which appears "first", ie leftmost in
row, from top row down
------

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
Back
Top