Hi,
I need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
Any help is appreciated, thanks!
Use a pivot, don't know why you think you can't.
Put Name in row fields and Name in data area - will give count of name.
CC
EDIT
The alternative is something like =countif(A:A,A1) copied down, but this lacks the "automatically assmeble a unique list of entries which a pivot table does in seconds...
Regards Pivot Table... you could perhaps add column @ source, eg:
C1: =A1&":"&B1
copied down for all rows
Create the PT as follows:
Column A as ROW FIELD
Column C as ROW FIELD
Column B as DATA FIELD set to Max
Column C as DATA FIELD set to COUNT (using field twice)
Now use Advanced Top 10 feature on 2nd ROW Field to display Top 1 Items based on Count of Column C, the MAX value field is your MODE.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hey DO,
I think you left your sledgehammer here, you'll have to come back for it, I can't pick it up...
Not sure I follow CC.
The OP isn't looking for Count of Names as implied in your initial response rather OP is looking for MODE per Name in tabular form.
The PT approach I outlined is not expensive IMO far less so than Array based approach though if data is sorted it might not be too inefficient to setup - all depends on volume.
I agree that a pivot table is more efficient, particularly if you have a large dataset, but if you have restrictions on adding columns or tables for whatever reason, I'm just going to throw out a formula-based approach. With your table being from A1:B9, if you started listing the names individually at A11, B11 can have =MODE(IF($A$1:$A$9=A11,$B$1:$B$9)), confirmed with Ctrl+Shift+Enter. It will return an error if there is no mode.
As inferred earlier if the data is sorted you could circumvent the Array by using the MODE in conjunction with INDEX.
=MODE(INDEX($A:$A,MATCH($A11,$A:$A,0)):INDEX($A:$A,MATCH($A11,$A:$A)))
Dynamic extraction of the names themselves would however warrant a potentially expensive approach - how expensive would be determined by volume of data.
Oh...The OP isn't looking for Count of Names as implied in your initial response rather OP is looking for MODE per Name in tabular form.
um...
yeah...
you're right
...
...
sorry about that
Although... I would suggest it could be simplified:
Name and Number to rows (in that order)
Count of Number as Data Item.
Autoshow top 1, by Count of Number, of Number row field
- no need for additional source data field.
CC
True. Good suggestion.
Thanks a lot for all the replies - Problem is solved...!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks