I have a report that has 4 columns of numbers 0 - 9, about 1500 rows deep, what I would like to be able to do is simple, but I can't seem to get my brain wrapped around it tonight. I need to find the most frequent occurring number in each column (got that part), then compare the top three from column A to the top 3 from col. B, etc.

Not to give away any information I'm not allowed to, think of it like this"



0 - Apples A - Northeast
1 - Oranges B - Southeast
2 - Grapes C - Northwest
3 - Plums D - Southwest

-------- data --------- -------------- Results ---------------
A B C D A B C D
1 0 1 3 6 0 1 0 0 0 0 0 0
2 2 4 3 2 1 0 1 2 1 0 1 0
3 2 1 7 5 2 2 2 1 2 0 2 1
4 4 2 5 9 3 0 3 0 3 2 3 0
4 1 4 1 4 0 4 0

What I cant seem to get is:
A B C D
2 1 3 2


I can't seem to be able to combine 4 "Countif"s statements...


--
Live simply...Love generously...Care deeply...Speak kindly...Leave the
rest to God.


--
Live simply...Love generously...Care deeply...Speak kindly...Leave the
rest to God.

Assume your source data below is in A1:D5

A B C D
0 1 3 6
2 4 3 2
2 1 7 5
4 2 5 9

and G2:G5 contains: 0,1,2,3
(H1:K1 houses the same labels: A,B,C,D)

Put in H2 and array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX($G$2:$G$5,MATCH(MAX(COUNTIF(A2:A5,$G$2:$G$5 )),COUNTIF(A2:A5,$G$2:$G$5),0))
Copy H2 across to K2 to return the desired results, viz:

A B C D
2 1 3 2

In the event of any ties in the maximums,
only the first item (the one higher up) will be returned.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"oldsquid" wrote in message
...
I have a report that has 4 columns of numbers 0 - 9, about 1500 rows deep,
what I would like to be able to do is simple, but I can't seem to get my
brain wrapped around it tonight. I need to find the most frequent occurring
number in each column (got that part), then compare the top three from
column A to the top 3 from col. B, etc.

Not to give away any information I'm not allowed to, think of it like this"

0 - Apples A - Northeast
1 - Oranges B - Southeast
2 - Grapes C - Northwest
3 - Plums D - Southwest

-------- data --------- --------------
Results ---------------
A B C D A B
C D
1 0 1 3 6 0 1 0 0
0 0 0 0
2 2 4 3 2 1 0 1 2
1 0 1 0
3 2 1 7 5 2 2 2 1
2 0 2 1
4 4 2 5 9 3 0 3 0
3 2 3 0
4 1 4 1
4 0 4 0

What I cant seem to get is:
A B C D
2 1 3 2

I can't seem to be able to combine 4 "Countif"s statements...
--
Live simply...Love generously...Care deeply...Speak kindly...Leave the
rest to God.

Since you have
.. 4 columns of numbers 0 - 9, about 1500 rows deep

Think we could just hardcode the "0 - 9" bit in the formula

Put in H2 and array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX({0;1;2;3;4;5;6;7;8;9},MATCH(MAX(COUNTIF(A2: A1500,{0;1;2;3;4;5;6;7;8;9})),COUNTIF(A2:A1500,{0; 1;2;3;4;5;6;7;8;9}),0))
Copy H2 across to K2 to return the desired results for A to D
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hummmm.... never worked with arrays before, will try this after while...

Thanks for the help.
Bill

"Max" wrote in message ...
Since you have
.. 4 columns of numbers 0 - 9, about 1500 rows deep

Think we could just hardcode the "0 - 9" bit in the formula

Put in H2 and array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX({0;1;2;3;4;5;6;7;8;9},MATCH(MAX(COUNTIF(A2: A1500,{0;1;2;3;4;5;6;7;8;9})),COUNTIF(A2:A1500,{0; 1;2;3;4;5;6;7;8;9}),0))
Copy H2 across to K2 to return the desired results for A to D
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Welcome, and thanks for posting back.

After you array-enter the formula by pressing CTRL+SHIFT+ENTER in H2, look
in the formula bar where you should see Excel wrap curly braces around the
formula: { }. If you don't see the curlies, that means it wasn't correctly
array-entered. Re-click inside the formula bar and try the CTRL+SHIFT+ENTER
confirmation again.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"oldsquid" wrote in message
...
Hummmm.... never worked with arrays before, will try this after while...

Thanks for the help.
Bill