Find the most common value if criteria met

[sharky12345]

Not sure if this is possible.....

I have a column of data between P2:P100, each cell has various words in it, each on it's own line. I'm trying to find the most common value in that column but only if the cell in column B = "Destination".

I thought perhaps a MAXIF but I have no idea where to start......

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

P​

[/td][td]

Q​

[/td][td]

R​

[/td][/tr][tr][td]

1​

[/td][td] [/td][td][/td][td]

2​

[/td][/tr]
[tr][td]

2​

[/td][td] london[/td][td]destination[/td][td]

2​

[/td][/tr]
[tr][td]

3​

[/td][td] istanbul[/td][td]destination[/td][td]MOST FREQ[/td][/tr]
[tr][td]

4​

[/td][td] paris[/td][td][/td][td] london[/td][/tr]
[tr][td]

5​

[/td][td] ottowa[/td][td]destination[/td][td] istanbul[/td][/tr]
[tr][td]

6​

[/td][td] den haag[/td][td]destination[/td][td] [/td][/tr]
[tr][td]

7​

[/td][td] [/td][td]destination[/td][td] [/td][/tr]
[tr][td]

8​

[/td][td] istanbul[/td][td]destination[/td][td] [/td][/tr]
[tr][td]

9​

[/td][td] london[/td][td]destination[/td][td] [/td][/tr]
[tr][td]

10​

[/td][td] paris[/td][td][/td][td] [/td][/tr]
[/table]

In R1 control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(1-($P$2:$P$10=""),IF($Q$2:$Q$10="destination",MATCH($P$2:$P$10,$P$2:$P$10,0))),ROW($P$2:$P$10)-ROW($P$2)+1))

In R2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($P$2:$P$10=""),IF($Q$2:$Q$10="destination",MATCH($P$2:$P$10,$P$2:$P$10,0))),ROW($P$2:$P$10)-ROW($P$2)+1)=$R$1,1))

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

=IF(ROWS($R$4:R4)>$R$2,"",INDEX($P$2:$P$10,SMALL(IF(FREQUENCY(IF(1-($P$2:$P$10=""),IF($Q$2:$Q$10="destination",MATCH($P$2:$P$10,$P$2:$P$10,0))),ROW($P$2:$P$10)-ROW($P$2)+1)=$R$1,ROW($P$2:$P$10)-ROW($P$2)+1),ROWS($R$4:R4))))

 

[sharky12345]

Perfect!

Thanks!!

 

[Aladin Akyurek]

Perfect!

Thanks!!

You are welcome.