Question description:
This user has given permission to use the problem statement for this
blog.
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/04/2018 - 11:44
Hello! Good day! How are you?
User
07/04/2018 - 11:44
good, you>
Excelchat Expert
07/04/2018 - 11:45
I'm good thank you. Are you able to provide a sample data that we could work on please?
User
07/04/2018 - 11:45
yes
User
07/04/2018 - 11:45
one min
Excelchat Expert
07/04/2018 - 11:47
sure. :)
User
07/04/2018 - 11:47
hold on
User
07/04/2018 - 11:49
u know what the problem is
User
07/04/2018 - 11:49
it is sensitive company information
User
07/04/2018 - 11:49
I dont know what to do
Excelchat Expert
07/04/2018 - 11:50
I have a general idea - its INDEX MATCH (), very similar to vlookup
User
07/04/2018 - 11:50
yes
Excelchat Expert
07/04/2018 - 11:50
it's okay, you're information will be kept confidential here
Excelchat Expert
07/04/2018 - 11:50
you don't have to give all the data. just a sample would do.
User
07/04/2018 - 11:52
hi
Excelchat Expert
07/04/2018 - 11:52
hello
User
07/04/2018 - 11:52
I uploaded a sheet
Excelchat Expert
07/04/2018 - 11:52
so we have 3 columns, correct? Call date, Duration, and Phone Number
Excelchat Expert
07/04/2018 - 11:53
oh, you added another one. Agent ID
User
07/04/2018 - 11:53
yes
User
07/04/2018 - 11:53
1 sec
Excelchat Expert
07/04/2018 - 11:53
sure
User
07/04/2018 - 11:56
ok
User
07/04/2018 - 11:56
to save some time
Excelchat Expert
07/04/2018 - 11:56
okay, where are we again?
User
07/04/2018 - 11:56
I have a formula already that when you populate the phone number it returns the duration
Excelchat Expert
07/04/2018 - 11:57
got it
User
07/04/2018 - 11:57
I need to do vlookup or index and match, but there are duplicates
Excelchat Expert
07/04/2018 - 11:58
it's cool. are you okay with adding another column for unique indexing of duplicates? (you could just hide it if it gets in the way of your presentation)
User
07/04/2018 - 11:58
its ok
Excelchat Expert
07/04/2018 - 11:58
okay, your source data is Copy of Sheet 1, right?
User
07/04/2018 - 11:58
after the 6 minutes are over how much is it?
User
07/04/2018 - 11:58
yes
Excelchat Expert
07/04/2018 - 11:59
I have no idea, what's visible to our end is just the points we earn for each session
User
08/04/2018 - 12:01
hmm
User
08/04/2018 - 12:01
well
User
08/04/2018 - 12:01
....
Excelchat Expert
08/04/2018 - 12:02
for a while, please
Excelchat Expert
08/04/2018 - 12:03
Hi. I don't seem to see any duplicates from column D
User
08/04/2018 - 12:03
wait one sec
User
08/04/2018 - 12:04
now
Excelchat Expert
08/04/2018 - 12:04
yes?
User
08/04/2018 - 12:05
now there are duplicates in sheet 1
Excelchat Expert
08/04/2018 - 12:06
Oh, I what i meant was there are no duplicates in Copy of Sheet 1 where the data will be pulled from
Excelchat Expert
08/04/2018 - 12:06
it's based on PhoneNumber, correct?
User
08/04/2018 - 12:07
yes
User
08/04/2018 - 12:07
as you type the phone number it fills the duration
User
08/04/2018 - 12:07
and date
User
08/04/2018 - 12:08
but it has to only return the duration and date if the agent id's match
User
08/04/2018 - 12:09
that is the formula I currently have on my desktop, one spreadsheet has 1500 phone numbers and the other 160000 phone calls I need for it to return the duration, because only those 1500 , the issue is that among those 1500 , one person called more than one time
Excelchat Expert
08/04/2018 - 12:11
Got it. So what I am trying to say is Copy of Sheet 1 is representative of the 160,000 phone calls and Sheet1 is representative of the 1500 phone numbers
Excelchat Expert
08/04/2018 - 12:11
correct?
Excelchat Expert
08/04/2018 - 12:15
To explain what i was doing, I added a column to identify the duplicate data under PhoneNumber in both sheets to display under the PhoneNumber_Index column
User
08/04/2018 - 12:15
there
User
08/04/2018 - 12:16
you see I am on line D407 in source data
User
08/04/2018 - 12:16
as I type in the number it has to return the duration and date,
Excelchat Expert
08/04/2018 - 12:16
alright
User
08/04/2018 - 12:16
however from this point and beyond I recognised the duration is not right because they contain duplicates
User
08/04/2018 - 12:17
in all calls month because the caller called more than once
User
08/04/2018 - 12:17
got it?
Excelchat Expert
08/04/2018 - 12:17
Yes,
Excelchat Expert
08/04/2018 - 12:17
so you match it using PhoneNumber
User
08/04/2018 - 12:17
yes....
Excelchat Expert
08/04/2018 - 12:17
from All calls month
User
08/04/2018 - 12:17
yes
User
08/04/2018 - 12:18
but they have duplicates
User
08/04/2018 - 12:18
vlookup would work too
User
08/04/2018 - 12:18
but there are duplicates
Excelchat Expert
08/04/2018 - 12:18
uhm, I don't seem to see phone number column in "all calls month"
Excelchat Expert
08/04/2018 - 12:18
okay, so column D
User
08/04/2018 - 12:18
yepp
Excelchat Expert
08/04/2018 - 12:19
i'll add a column to the right of column D okay?
User
08/04/2018 - 12:20
ok
Excelchat Expert
08/04/2018 - 12:21
okay, i highlighted 8883507270 as our sample duplicates
User
08/04/2018 - 12:21
how do I do that in excel?
Excelchat Expert
08/04/2018 - 12:21
just follow these steps
User
08/04/2018 - 12:22
ok
Excelchat Expert
08/04/2018 - 12:22
1. Insert a column to the right of column D
Excelchat Expert
08/04/2018 - 12:23
2. type the formula = D2&"_"&countif(D$2:D2,D2) to cell E2
Excelchat Expert
08/04/2018 - 12:23
3. Copy formula from cell E2 down to the last applicable row
User
08/04/2018 - 12:23
ok
User
08/04/2018 - 12:23
ok
Excelchat Expert
08/04/2018 - 12:23
can you follow so far?
User
08/04/2018 - 12:23
yes
Excelchat Expert
08/04/2018 - 12:24
Then an extra, you could use conditional formatting to highlight duplicate items
User
08/04/2018 - 12:24
ok
Excelchat Expert
08/04/2018 - 12:25
do you need further guidance on conditional formatting?
User
08/04/2018 - 12:25
its in the home tab in excel right
User
08/04/2018 - 12:26
ribbon/tab
Excelchat Expert
08/04/2018 - 12:26
yup highlight all of column D
User
08/04/2018 - 12:27
ok so just highlight cell rules, and all duplicate values
User
08/04/2018 - 12:27
got it
User
08/04/2018 - 12:27
where are u from?
Excelchat Expert
08/04/2018 - 12:27
then click, Home>Conditional Formatting>New Rule>Format Only Unique or Duplicate Values
Excelchat Expert
08/04/2018 - 12:27
yup
Excelchat Expert
08/04/2018 - 12:28
I am from Asia :)
User
08/04/2018 - 12:28
niceeee
User
08/04/2018 - 12:28
:)
Excelchat Expert
08/04/2018 - 12:28
anyway, let's proceed okay?
User
08/04/2018 - 12:28
yes
Excelchat Expert
08/04/2018 - 12:28
say we go back to source data
User
08/04/2018 - 12:28
ok
User
08/04/2018 - 12:29
how are we gonna know if it is a duplicate or not?
Excelchat Expert
08/04/2018 - 12:29
i also made the same column to the right of Phone Number
User
08/04/2018 - 12:30
ok using the same formula right
Excelchat Expert
08/04/2018 - 12:30
yup
User
08/04/2018 - 12:30
ok
Excelchat Expert
08/04/2018 - 12:30
now, for example we want to include in our list the phone number 8883507270
User
08/04/2018 - 12:31
yea
User
08/04/2018 - 12:31
and it is a duplicate
Excelchat Expert
08/04/2018 - 12:31
is it okay if I delete all data and start clean? i'll just retain the column headers
User
08/04/2018 - 12:31
yes
User
08/04/2018 - 12:32
now what?
Excelchat Expert
08/04/2018 - 12:32
for a while, let me build the formula
User
08/04/2018 - 12:33
ok
User
08/04/2018 - 12:33
thank you
Excelchat Expert
08/04/2018 - 12:34
hi i'm back
User
08/04/2018 - 12:34
ok
Excelchat Expert
08/04/2018 - 12:34
now, we just type the phone number 8883507270 in column D
Excelchat Expert
08/04/2018 - 12:36
so we just keep on doing typing the same number down until it stops showing values
Excelchat Expert
08/04/2018 - 12:36
wow there are lot of duplicates for this number
Excelchat Expert
08/04/2018 - 12:37
this person really calls a lot
User
08/04/2018 - 12:37
yes
Excelchat Expert
08/04/2018 - 12:37
does this solve your problem?
User
08/04/2018 - 12:37
that is why it needs to return only if the agent id matches
User
08/04/2018 - 12:37
so therotically
User
08/04/2018 - 12:37
I already have the agent ID and the PHOne number in the source data
User
08/04/2018 - 12:38
all 1500 calls
User
08/04/2018 - 12:38
just not now...
Excelchat Expert
08/04/2018 - 12:38
could you confirm if this solves your problem?
User
08/04/2018 - 12:38
so repeating in source data I already have phone number and agent ID, it just needs to return the duration and the date, for the current agent IS
User
08/04/2018 - 12:38
ID*
User
08/04/2018 - 12:38
it doesn't;r
User
08/04/2018 - 12:39
it doesn't
Excelchat Expert
08/04/2018 - 12:39
i see, so your other than phone number, it also has to match the agent ID?
User
08/04/2018 - 12:39
yes!!!
User
08/04/2018 - 12:39
exactly !!!
Excelchat Expert
08/04/2018 - 12:40
okay
Excelchat Expert
08/04/2018 - 12:40
for a while, i have to do some edits
User
08/04/2018 - 12:40
and if that same agent called still highight it
User
08/04/2018 - 12:40
ok thank you!
Excelchat Expert
08/04/2018 - 12:40
okay, let's take agent 956053
Excelchat Expert
08/04/2018 - 12:41
as example
Excelchat Expert
08/04/2018 - 12:41
i'll get back to you okay?
User
08/04/2018 - 12:41
ok
User
08/04/2018 - 12:42
in minimal cases the same agent took 2 calls so it will have two durations
Excelchat Expert
08/04/2018 - 12:42
okay
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.
