Not sure what formula to use (SUMPRODUCT??)

[monkeyharris]

I need to populate a table from the below data. Firstly i need to show how many deliveries a person has made on each day but only by unique sales order number. IE: On 1st June Colin did 1 delivery. His name is mentioned twice on 1/6 but sales order is duplicated. Any ideas?

Date Expected	Route	Drop:	Vehicle	Sales Order	Driver
01/06/2020	TRIP 1	1	DK17LHE	214108	Mark
01/06/2020	TRIP-2	1	DK17LHE	214425	Colin
01/06/2020	TRIP-2	1	DK17LHE	214425	Colin
01/06/2020	TRIP-2	1	DK17LHE	214425	Andy
01/06/2020	TRIP-2	1	DK17LHE	214425	Robert
02/06/2020	TRIP-2	1	DK17LHE	214425	Tony
02/06/2020	TRIP-2	1	DK17LHE	214425	Tony
02/06/2020	TRIP-2	1	DK17LHE	214425	Tony
03/06/2020	TRIP 1	2	DK17LHE	214427	Colin
03/06/2020	TRIP 1	2	DK17LHE	214427	Colin
03/06/2020	TRIP 1	2	DK17LHE	214427	Colin
03/06/2020	TRIP 1	3	DK17LHE	214439	Mark
03/06/2020	TRIP 1	3	DK17LHE	214439	Mark
03/06/2020	TRIP 1	2	DG16DVX	213054	Andy
04/06/2020	TRIP-2	1	DG16DVX	213152	Andy
04/06/2020	TRIP-2	1	DG16DVX	213152	Andy
04/06/2020	TRIP-2	1	DG16DVX	213152	Colin
04/06/2020	TRIP-2	1	DG16DVX	213152	Andy
05/06/2020	TRIP-3	1	DG16DVX	213943	Robert

 

[mamady]

You can use pivot in such case, to identify only the unique set of data points

• Select the range --> Insert PivotTable --> tick "Add this data to the Data Model" --> press OK
• Add all the fields to the Rows section
• From the design tab, select Report Layout --> Repeat All Item Labels
• From the design tab, select Report Layout --> Show in Tabular Form
• From the design tab, select Subtotal --> Do not Show Subtotals

Best Regards
M. Yusuf

 

[monkeyharris]

Hi Mamady,
The problem with this is it seems to count the total number of times the person is listed rather than the number of unique jobs they've done. Using the table i sent, it gives the result of 2 for Colin but should only say 1. IE. He did 2 jobs but only 1 sales order: 214425

 

[sandy666]

maybe select your whole table then remove duplicates
then show expected result here

 

[monkeyharris]

Found it using the Pivot Table option. Instead of Count or Sum, it works if i use "Distinct Count". Many thanks for your help.

 

[mamady]

Hi Dear,

I tried it, it does not show two jobs. please check the below screen-shot

Regards
M. Yusuf