Sorting duplicates

[nadineg207]

Hello -

I have a spreadsheet that has timestamps for about 4,500 phone calls received in a given day, 3 columns: incoming telephone number - queue time - talk time.

I need to sort any calls that came in more than once, and if they have a value in the "queue" column (some are blank, some have time stamps), see how many of those had a time stamps of 0:00:00 in the "talk" column.

Thanks so much for any help,

Nadine

[#2 · Accepted solution]

@nadineg207 

One approach is to use a pivot table. 

Select any cell in your data range and use Insert>Pivot Table

Drag "Phone" to the Rows area, "talk time" and "queue time" to the Filters area and "Phone" to the Values area.

Now right-click anywhere in the "Sum of phone" column and select "Summarize values by" and "Count":

Now filter the "talk time" filter by "00:00:00" (or whatever is appropriate in your data):

Now apply a filter to "Queue time" to select all non-blank items. You should check "Select multiple items" then un-check the blank item in the filter. Please also scroll to the bottom of the list of items to check there isn't anything else you want to exclude.

Now click the filter drop-down next to the text "Row Labels" and select "Values filters", then "Greater than":

Filter where Count of Phone is greater than 1. This effectively returns those phone numbers who appear more than once in the source data:

These steps should give you what you want. 

There are of course other formula-based methods, but this is probably easiest. 

Let me know how you get on!

Cheers

Owen

[#3]

Thank you so much, Owen! Easiest is definitely best in my situation, I really appreciate the help!