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.
