ignoring 1 word when looking for the most common word

[Patcheen]

Hi all as i want to ignore the word "undelivered" when searching for the most common word

what i have is - =MODE(IF(A6:A1000<>unknown,A6:A1000)) (array formula)

it says #Name as a result

TIA

 

[Aladin Akyurek]

I've uploaded an example workbook to here - hope it helps

https://www.sendspace.com/file/p518kq

My system refuses to download, saying that that site is unsafe.


By the way, you can just copy 10 rows and paste it here.

 

[Patcheen]

how? as an image?

 

[Aladin Akyurek]

how? as an image?

No, not as image or picture. The sample must be Excel readable.

Is this something like what you have?

Book1
	E
8	ITEMS
9	London
10	undelivered
11	Cambridge
12	Nottingham
13	Dublin
14
15	London
16	Cambridge
17	Bristol
18
19	Oxford
Sheet1

If it's what is the output you expect, that is, which cities should be in the output/the results list?

 

[Patcheen]

Where do i find the option to do that ?

 

[Aladin Akyurek]

Where do i find the option to do that ?

Just type the items...

 

[Patcheen]

Unknown

Unknown

Hackney

C Woan

Unknown

Unknown

RE Smith

RJ Leafe

R Chillis

G Owen

Unknown

Kevin smith

KG Salmon

M Scott

M Robinson

KG Salmon

Graham english

Kevin smith

Formula from other sheets

Formula from other sheets

Formula from other sheets

Formula from other sheets

Formula from other sheets

<colgroup><col></colgroup><tbody>
</tbody>

I want to ignore the "Unknown and formula from other sheets - Result kevin smith

Hope this helps but not sure if it will.

 

[Aladin Akyurek]

What does "Formula from other sheets" deliver - a blank?

 

[Patcheen]

What does "Formula from other sheets" deliver - a blank?

yes

 

[Aladin Akyurek]

yes

Book1
	E	F
8	ITEMS	2
9	Unknown	Kevin smith
10	Unknown	KG Salmon
11	Hackney
12	C Woan
13	Unknown
14	Unknown
15	RE Smith
16	RJ Leafe
17	R Chillis
18	G Owen
19	Unknown
20	Kevin smith
21	KG Salmon
22	M Scott
23	M Robinson
24	KG Salmon
25	Graham english
26	Kevin smith
27
28
29
30
31
Sheet1

In F8 control+shif+enter, not just enter:

=MAX(FREQUENCY(IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)),ROW($E$9:$E$31)-ROW($E$9)+1))

In F9 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$9:$E$31,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)),ROW($E$9:$E$31)-ROW($E$9)+1)=$F$8,ROW($E$9:$E$31)-ROW($E$9)+1),ROWS($F$9:F9))),"")

 

[Aladin Akyurek]

Using MODE.MULT...

In F9 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($P$2:$P2))),"")