MODE MATCH Formula (for most common occurance) not considering entire range

[ATTACH=CONFIG]72417[/ATTACH]

[TABLE="width: 497"]

[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"] L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]

[/TABLE]
[TABLE="width: 497"]

Early

Brownwood

Brownwood

Brownwood

Early

Early

[/TABLE]

This formula is in column N with an intended range of I1:M1 and should write the most common occurrence of text in those cells to column N.

=INDEX(I1:M1,MODE(MATCH(I1:M1,I1:M1,0)))

From Extended Office.

I've tested this on quite a few rows and it appears, like seen above that it's NOT considering ALL cells in the range: I1:M1

At first I thought it was just considering to two outside sells, I1 and M1, but it's considering more than just those two outside cells but, as you can see above, it's certainly not looking at all 5 cells in the range (I1:M1) or the answer would be Brownwood, not Early.

-------------

Also, does anyone know how to adapt this formula to consider text strings separately, as well, not just entire cell contents, so that the answer to the following would be the text string portion 'La Grange'?
[TABLE="width: 497"]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[/TABLE]
[TABLE="width: 452"]

[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"] L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N
[/TD]

Brookfield

Brookfield

La Grange

La Grange Park

Aurora

La Grange

[/TABLE]

[TABLE="width: 452"]

[/TABLE]

Re: MODE MATCH Formula (for most common occurance) not considering entire range

That's SO ironic! I was just solving this issue in another area and was making multiple macros to trim several groups of columns using lastrow so the darn thing won't want to run run until I break it or wait it out and sure enough I looked and it had already changed!

I don't suppose you know how to consider partial string matches using formulas like this?

I have a hard enough time with vba, but I really struggle with formulas!

Re: MODE MATCH Formula (for most common occurance) not considering entire range

Quote from NBVC;791932

I'm not sure how possible that would be, there could be so many false positives, like perhaps multiple entries having a common word like "The" or "a" or "La".

Right, I just thought it might be possible within the function using LEFT X or RIGHT X and other parameters just like when manipulating and comparing text strings. I know it can be done in vba. I just thought I'd ask.

Re: MODE MATCH Formula (for most common occurance) not considering entire range

Thank you that will be helpful!

There's one more issue that this formula has, well two things.

1) It returns N/A when all columns each have a different value. Is there a way to designate that if all are different, choose the first one?

2) There is one common column entry "unknown." How can I get it to ignore this completely, then process normally with respect to #1 and if the first column is "unknown" just pick the first available?

I'm going to have to do a macro, I just know it. BUT maybe if I can get close with these two I can get by. It's not processing too many records each day.

Re: MODE MATCH Formula (for most common occurance) not considering entire range

SO sorry for the delay! omg! humm...yeah perhaps, maybe something like this... lol

THIS IS ABSOLUTELY PERFECT NBVC!

You picked up on every little nuance! Dealing with unknown and not allowing that as the choice, blank cells, even picks the first column when there's not at least two the same! Amazing!

If you get a chance, can you let me know which section of your code chooses the 1st column if no 2 or more values?

The only thing I could come up with, after trying a while too!, is if column A is blank or it contains "unknown" BUT this is only if there is not at least two common entries in the range! I hate to even ask, but if you can consider this, if column A is "unknown" or blank that it chooses the next first value that's not"unknown".

I seriously cannot believe that this single line of code can do what it does! Again, thank you NBVC!

Quote from NBVC;792089

Perhaps something like this array formula*

=IFERROR(INDEX(A1:E1,MODE(IF(A1:E1<>"unknown",MATCH(A1:E1,A1:E1,0)))),INDEX(A1:E1,MATCH(TRUE,A1:E1<>"unknown",0)))

[arf]*[/arf]

Re: MODE MATCH Formula (for most common occurance) not considering entire range

Your coded answer in last column, shown below, allows has brackets e.g. (answer)

Regarding this question...
"can you let me know which section of your code chooses the 1st column if no 2 or more values?"
E.g. 'Graysville' 'Dunlap' 'Collegesta' 'Pikeville' (Graysville)<<< Where's the code that makes column A kind of the KEY, when no other 2 or more)

An issue DID appear to come up with exactly HOW much weight column A has though.
E.g. 'Oxon Hill' 'Fort Washington' 'Fort Washington' 'Fort Washington' (Oxon Hill) <<< Obviously this example should be (Fort Washington)
E.g. 'Oxon Hill' 'Fort Washington' 'Fort Washington' 'Mckinney' 'blank' (Oxon Hill) <<<even this should be (Fort Washington)

This should be (Elkridge)
E.g. 'Ray' 'Halethorpe' 'Baltimore' 'Elkridge' 'Elkridge' 'blank' (Ray)

This should be (Lanham)
E.g. 'Bowie' 'Lanham' 'Washington' 'Lanham' 'blank' (Bowie)

THIS would be ok though, because I needed a KEY or primary if not 2 or more.
E.g. 'Ray' 'Halethorpe' 'Baltimore' 'blank' 'Elkridge' 'blank' (Ray)

Not sure if on purpose but this is EXACTLY what I need. When 'primary' column A is 'unknown' and no 2 or more values, select the first non-unknown. BUT if column A is blank then answer is (0)
E.g. 'Unknown' 'San Diego' 'Mormos' 'Unknown' 'New Braunfels' (San Diego)

--------------------------------------------------------------------------------------------------------------------

The other issue is this...
E.g. 'blank or unknown' 'blank or unknown' 'blank or unknown' 'Cellphone' 'Cellphone' 'blank' THEN (0)

Ironically this is fine:
'unknown' 'unknown' 'unknown' 'Cellphone' 'Cellphone' 'blank' THEN (Cellphone)

I say ironically, because normally 'blanks or unknown' are fine, but in the above example removing ANY 'unknown's leaving a 'blank' from column A, B, C or D gives (0).

Regardless what is in columns E and F it doesn't seem to matter, either column with just ONE Cellphone gives (Cellphone) as long as ALL 1st 4 columns contain at least 'unknown'

Even this is FINE!

'unknown' 'unknown' 'unknown' 'unknown' 'Cellphone' 'blank' STILL (Cellphone)

Re: MODE MATCH Formula (for most common occurance) not considering entire range

RE: =IFERROR(INDEX(A1:E1,MODE(IF(A1:E1<>"unknown",MATCH(A1:E1,A1:E1,0)))),INDEX(A1:E1,MATCH(TRUE,A1:E1<>"unknown",0)))

It's near perfect. Even 'unknown' in a cell are properly ignored when getting most common.

I just need to account for (ignore) BLANK cells too. Maybe A1:E1<>""

Code and answer is shown within brackets e.g. (answer) in the last column.

2 issues relating to BLANK cells. If column A is BLANK, regardless, the last column with the formula is (0).
E.g. BLANK 'Cellphone' 'unknown' BLANK 'Cellphone' THEN (0)

If column A has 'unknown' or any value it works, unless ANY other cell in the range is BLANK it's fine.
E.g. 'unknown' 'unknown' 'unknown' 'Cellphone' 'Cellphone' BLANK THEN (Cellphone)

But if ANY other column in the range has a BLANK, regardless how many instances of a value it chooses the first NON-Unknown value from the left.
E.g. Unknown Unknown Unknown Finksburg Lawton Lawton Lawton (Finksburg)
E.g. Unknown Finksburg Lawton Lawton Unknown BLANK Lawton (Finksburg)
E.g. 'Bowie' 'Lanham' 'Washington' 'Lanham' BLANK (Bowie) <<< should be (Lanham) most common.

Re: MODE MATCH Formula (for most common occurance) not considering entire range

B-e-a-utiful!

So far so good!

Thank you!

Quote from NBVC;792815

The MATCH() functions finds first match (when using the 0 last parameter). So this; ATCH(TRUE,A1:E1<>"unknown",0)) simply finds first time that A1:E1 is not equal to "unknown".

Try this array formula for your latest additions...

=IFERROR(INDEX(A1:E1,MODE(IF((A1:E1<>"unknown")*(A1:E1<>""),MATCH(A1:E1,A1:E1,0)))),INDEX(A1:E1,MATCH(1,(A1:E1<>"unknown")*(A1:E1<>""),0)))

[arf]/[/arf]

Display More