This is probably getting away from Excel functions, but I'll give it a try. In the attached workbook, each row has about 25 words (one word per column). And there are some "junk" cells with useless symbols here and there.
Is there a way to find the most common word in each column? For example, in column C that "word" would be eur.
Would this be more suited to a macro than a formula?
Thanks.
maybe COUNTIF()
This is another way. With output in row 33 enter this formula in B33 and fill across.
Formula:
=INDEX(B2:B32,MATCH(MAX(FREQUENCY(MATCH(B2:B32,B2:B32,0),ROW(B2:B32)-MIN(ROW(B2:B32))+1)),
FREQUENCY(MATCH(B2:B32,B2:B32,0),ROW(B2:B32)-MIN(ROW(B2:B32))+1),0))
Dave
Going with sandy's initial suggestion this is shorter ... also must be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:=INDEX(B2:B32,MATCH(MAX(COUNTIF(B2:B32,B2:B32)),COUNTIF(B2:B32,B2:B32),0))
Upon further review there are multiple "max" occurrences in columns M, N and U. I suspect you want all of them listed.
This returns all of them. In B33 fill down and across until all columns start to return blanks.Formula:=IFERROR(INDEX(B$2:B$32,AGGREGATE(15,6,(ROW(B$2:B$32)-MIN(ROW(B$2:B$32))+1)/
(MAX(FREQUENCY(MATCH(B$2:B$32,B$2:B$32,0),ROW(B$2:B$32)-MIN(ROW(B$2:B$32))+1))=
FREQUENCY(MATCH(B$2:B$32,B$2:B$32,0),ROW(B$2:B$32)-MIN(ROW(B$2:B$32))+1)),ROWS(B$33:B33))),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks