SOLVED

Sorting duplicates

Occasional Contributor

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 Replies
best response confirmed by nadineg207 (Occasional Contributor)
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":

 

flexyourdata_1-1664834472402.png

 

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

 

flexyourdata_2-1664834522495.png

 

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.

 

flexyourdata_0-1664834765801.png

 

 

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

 

flexyourdata_3-1664834569819.png

 

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

 

flexyourdata_4-1664834608034.png

 

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

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