**Question description:**

*This user has given permission to use the problem statement for this blog.*

So what Iâ€™m trying to achieve isto find value with highest frequency in a range so for instance thereâ€™s 46 time that Annaliseâ€™s name has been displayed. I need this display in an individual sheet for the person with their name the most repeated.
I will need to filter this down by manager also so from there it will display EIN, STARTTIME, NCALLID, ouc.
If we could do a screen sharing session that would be great so I can show you what needs to be done.
Thank you!

Solved by M. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
31/01/2018 - 05:00

HI

Excelchat Expert
31/01/2018 - 05:00

How may i help you?

User
31/01/2018 - 05:01

Can you see that?

Excelchat Expert
31/01/2018 - 05:01

are you there?

User
31/01/2018 - 05:01

Hi

Excelchat Expert
31/01/2018 - 05:01

yes

Excelchat Expert
31/01/2018 - 05:01

i can see

User
31/01/2018 - 05:01

Well first thanks for your help!

Excelchat Expert
31/01/2018 - 05:01

can you also put headers into this sheet

User
31/01/2018 - 05:02

yo
REPLYREPLY ALLFORWARD
Mark as unread
Wheeler,B,Ben,HKU21E R
Wed 31/01/2018 15:11
To:
Wheeler,B,Ben,HKU21E R;
Get more appsAction Items
So what I’m trying to achieve isto find value with highest frequency in a range so for instance there’s 46 time that Annalise’s name has been displayed. I need this display in an individual sheet for the person with their name the most repeated.
I will need to filter this down by manager also so from there it will display EIN, STARTTIME, NCALLID, ouc.

User
31/01/2018 - 05:02

Sorry

User
31/01/2018 - 05:03

So where it shows on sheet 2

User
31/01/2018 - 05:03

I'd like the information pulled from sheet1 and to be organised as it is in sheet 2

User
31/01/2018 - 05:03

if possible

User
31/01/2018 - 05:03

basically trying to figure out who's name appears the most

User
31/01/2018 - 05:04

so starting with June

User
31/01/2018 - 05:04

literally just want it if possible to show who's name is popping up the most

Excelchat Expert
31/01/2018 - 05:04

you can use pivot table on this data to count which gets repeated maximum number of times

User
31/01/2018 - 05:04

can you show please

User
31/01/2018 - 05:05

but i need it as a formula

User
31/01/2018 - 05:05

not table

Excelchat Expert
31/01/2018 - 05:07

did you see the formula?

Excelchat Expert
31/01/2018 - 05:08

you can use countif formula

Excelchat Expert
31/01/2018 - 05:08

in countif first we specify the range and then criteria

Excelchat Expert
31/01/2018 - 05:08

are you there?

User
31/01/2018 - 05:08

Can it be viewed as one

User
31/01/2018 - 05:08

on on sheet 2

User
31/01/2018 - 05:09

So like a dropdown for the persons name

Excelchat Expert
31/01/2018 - 05:09

for example June smith

Excelchat Expert
31/01/2018 - 05:09

mentioning june smith in sheet 2

User
31/01/2018 - 05:09

yep

Excelchat Expert
31/01/2018 - 05:10

now use vlookup fucntion to get number of times it has come

Excelchat Expert
31/01/2018 - 05:10

did you get?

Excelchat Expert
31/01/2018 - 05:10

in vlookup we selection the name first and then select the second value against that name

User
31/01/2018 - 05:11

Anyway of doing it as a drop down, thats really good

Excelchat Expert
31/01/2018 - 05:11

i did not get you

Excelchat Expert
31/01/2018 - 05:12

can you explain me?

User
31/01/2018 - 05:12

so rather then typing the names

User
31/01/2018 - 05:12

you select and drop down

User
31/01/2018 - 05:12

and it shows the number

Excelchat Expert
31/01/2018 - 05:12

you can copy all the name and then remove duplicates from that list

Excelchat Expert
31/01/2018 - 05:12

i types here to just show you as an example

Excelchat Expert
31/01/2018 - 05:13

i wanted to explain concept of countif and vlookup which has been used here

Excelchat Expert
31/01/2018 - 05:13

countif function here only would have served your purpose

Excelchat Expert
31/01/2018 - 05:13

are you getting me?

User
31/01/2018 - 05:13

yeah i understand

Excelchat Expert
31/01/2018 - 05:13

cool

Excelchat Expert
31/01/2018 - 05:14

let me know if you need to understand anything else?

User
31/01/2018 - 05:14

so to clarify where it says Kyle Graves

User
31/01/2018 - 05:14

We can't have a drop down of names who's on sheet1 row g

Excelchat Expert
31/01/2018 - 05:15

by dropdown what do you mean..how do you want ot view it?

User
31/01/2018 - 05:15

like a drop down of names

Excelchat Expert
31/01/2018 - 05:15

to*

User
31/01/2018 - 05:15

so you don't need to manually type it

Excelchat Expert
31/01/2018 - 05:17

here you can copy and paste all the name and then remove duplicates from here

Excelchat Expert
31/01/2018 - 05:17

you don't have to type the name manually

User
31/01/2018 - 05:17

can you show

Excelchat Expert
31/01/2018 - 05:18

first select all the data and paste it whereever you require

Excelchat Expert
31/01/2018 - 05:18

do you get it?

Excelchat Expert
31/01/2018 - 05:19

then select that data and then click on data tab in your excel

Excelchat Expert
31/01/2018 - 05:19

here is an option of remove duplicates

Excelchat Expert
31/01/2018 - 05:19

did you get?

User
31/01/2018 - 05:20

2 secs

User
31/01/2018 - 05:20

can we do a screen sharing session

User
31/01/2018 - 05:20

by any change?

Excelchat Expert
31/01/2018 - 05:20

i dont have any tool to share the screen

User
31/01/2018 - 05:21

team viewer?

Excelchat Expert
31/01/2018 - 05:21

select the list of names and then select data tab on top

Excelchat Expert
31/01/2018 - 05:21

and then use remove duplicates

Excelchat Expert
31/01/2018 - 05:21

try these steps

Excelchat Expert
31/01/2018 - 05:21

you will be easily able to do it

Excelchat Expert
31/01/2018 - 05:23

do you get it?

User
31/01/2018 - 05:23

ok sorry 1 sec

User
31/01/2018 - 05:24

can we do the vlookup again

User
31/01/2018 - 05:24

on the new spreadsheet

User
31/01/2018 - 05:24

i tried to duplicate what you showed me but didn't work

Excelchat Expert
31/01/2018 - 05:24

ok

Excelchat Expert
31/01/2018 - 05:24

paste a fresh data in sheet 1

User
31/01/2018 - 05:25

I have

User
31/01/2018 - 05:25

we're just working with the rows in yellow

Excelchat Expert
31/01/2018 - 05:26

copied that is sheet 3

User
31/01/2018 - 05:26

erm

User
31/01/2018 - 05:26

but i need the correct row

User
31/01/2018 - 05:26

can we do it from sheet 1

User
31/01/2018 - 05:26

please

User
31/01/2018 - 05:26

and pull the data from AU

Excelchat Expert
31/01/2018 - 05:27

pulled au data in sheet 4

Excelchat Expert
31/01/2018 - 05:27

did you see?

User
31/01/2018 - 05:27

Yes ok

Excelchat Expert
31/01/2018 - 05:27

now apply countif on that

Excelchat Expert
31/01/2018 - 05:28

did you see

User
31/01/2018 - 05:28

yes

Excelchat Expert
31/01/2018 - 05:28

i applied countif

Excelchat Expert
31/01/2018 - 05:28

this gives the number of times name is there in the column A

User
31/01/2018 - 05:29

now the name thing

Excelchat Expert
31/01/2018 - 05:29

copy paste the names in different colum

Excelchat Expert
31/01/2018 - 05:29

in your excel on your desktop you will see that there is data tab

Excelchat Expert
31/01/2018 - 05:30

inside data tab there is remove duplicate

Excelchat Expert
31/01/2018 - 05:30

hit that

Excelchat Expert
31/01/2018 - 05:30

this will be your list

Excelchat Expert
31/01/2018 - 05:31

use vlookup on that

Excelchat Expert
31/01/2018 - 05:31

did you see?

Excelchat Expert
31/01/2018 - 05:32

kindly let me know if you don't understand anything?

Excelchat Expert
31/01/2018 - 05:35

are you there?

User
31/01/2018 - 05:37

hi

Excelchat Expert
31/01/2018 - 05:37

yes

Excelchat Expert
31/01/2018 - 05:37

did you get the explanation?

User
31/01/2018 - 05:37

sorry it froze

Excelchat Expert
31/01/2018 - 05:37

ohk

Excelchat Expert
31/01/2018 - 05:38

refer to sheet 4

User
31/01/2018 - 05:38

I don't understand why the vlookup isn't working on my PC

Excelchat Expert
31/01/2018 - 05:38

you need to freeze the cells

Excelchat Expert
31/01/2018 - 05:38

press f4 to free the cells when you select the range

Excelchat Expert
31/01/2018 - 05:38

see the formula i have used

User
31/01/2018 - 05:39

I'm really sorry, I'm 60 years old, not to good at excel

User
31/01/2018 - 05:39

Can we start again?

Excelchat Expert
31/01/2018 - 05:39

sure

User
31/01/2018 - 05:40

So sheet5

User
31/01/2018 - 05:40

is raw data

Excelchat Expert
31/01/2018 - 05:40

ok

User
31/01/2018 - 05:40

What I'm trying to achieve

User
31/01/2018 - 05:40

Where it says manager

User
31/01/2018 - 05:40

So starting from a different way now

User
31/01/2018 - 05:41

A list of the managers name in a drop down menu

User
31/01/2018 - 05:41

From there I need it to say the name of the advisor

User
31/01/2018 - 05:41

Who's name popped up the most

User
31/01/2018 - 05:41

with all the information like EIN OUC etc

User
31/01/2018 - 05:42

So not sure the best way to get this done

User
31/01/2018 - 05:42

I'll pay aswell

User
31/01/2018 - 05:42

but really need help

User
31/01/2018 - 05:42

So for instance ill show

User
31/01/2018 - 05:43

And for caller ID and time it shows a list for that advisor

User
31/01/2018 - 05:43

But it needs to be the person who's name popped up the most

Excelchat Expert
31/01/2018 - 05:45

so by countif you will know whose name popped the most

User
31/01/2018 - 05:45

Ok could you show me please

User
31/01/2018 - 05:45

So first off a drop down of the managers on the team

Excelchat Expert
31/01/2018 - 05:46

select the data in yellow and paste it in new sheet

Excelchat Expert
31/01/2018 - 05:46

remove the data which you dont require

User
31/01/2018 - 05:46

could we do it in this sheet please

User
31/01/2018 - 05:46

as i won't really understand later on what to do

Excelchat Expert
31/01/2018 - 05:46

i this sheet i am unable to use columns after AU

User
31/01/2018 - 05:47

ill minimise the other ones we don't need

User
31/01/2018 - 05:47

yeah thats fine, thats where it stops

Excelchat Expert
31/01/2018 - 05:47

in your desktop excel you will be able to do

Excelchat Expert
31/01/2018 - 05:48

this interface has limitations

User
31/01/2018 - 05:48

but the formulas will be the same

Excelchat Expert
31/01/2018 - 05:48

yes

User
31/01/2018 - 05:48

I'm not to smart at excel you see

User
31/01/2018 - 05:48

they won't be though

User
31/01/2018 - 05:48

can you minimise the tabes not in yellow?

User
31/01/2018 - 05:50

so i just want it to pull the data like i said, is it possible or do you need to do =COUNTIF($A:$A,A3)

Excelchat Expert
31/01/2018 - 05:51

you should use this formula to know whose name popped up most

User
31/01/2018 - 05:51

Is it not possible then for what I'm asking

Excelchat Expert
31/01/2018 - 05:52

sophie murall is appearing only twice in the list

Excelchat Expert
31/01/2018 - 05:52

and this formula give 2 as result

User
31/01/2018 - 05:52

Thats fine

User
31/01/2018 - 05:53

I just want the person who pops up the most on the list

User
31/01/2018 - 05:53

to be displayed on another sheet

Excelchat Expert
31/01/2018 - 05:53

ok

User
31/01/2018 - 05:53

with the quantity of how many times they pop up

User
31/01/2018 - 05:53

If possible pleas

Excelchat Expert
31/01/2018 - 05:54

you can use index match

User
31/01/2018 - 05:55

Ok :) would you mind showing me please

User
31/01/2018 - 05:56

?

Excelchat Expert
31/01/2018 - 05:56

yes showing that only

User
31/01/2018 - 05:59

47 seconds :(

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*