Not sure what formula to use (SUMPRODUCT??)

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
310
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 ExpectedRouteDrop:VehicleSales OrderDriver
01/06/2020TRIP 11DK17LHE214108Mark
01/06/2020TRIP-21DK17LHE214425Colin
01/06/2020TRIP-21DK17LHE214425Colin
01/06/2020TRIP-21DK17LHE214425Andy
01/06/2020TRIP-21DK17LHE214425Robert
02/06/2020TRIP-21DK17LHE214425Tony
02/06/2020TRIP-21DK17LHE214425Tony
02/06/2020TRIP-21DK17LHE214425Tony
03/06/2020TRIP 12DK17LHE214427Colin
03/06/2020TRIP 12DK17LHE214427Colin
03/06/2020TRIP 12DK17LHE214427Colin
03/06/2020TRIP 13DK17LHE214439Mark
03/06/2020TRIP 13DK17LHE214439Mark
03/06/2020TRIP 12DG16DVX213054Andy
04/06/2020TRIP-21DG16DVX213152Andy
04/06/2020TRIP-21DG16DVX213152Andy
04/06/2020TRIP-21DG16DVX213152Colin
04/06/2020TRIP-21DG16DVX213152Andy
05/06/2020TRIP-31DG16DVX213943Robert
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 20, 2008
Messages
310
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe select your whole table then remove duplicates
then show expected result here
 

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
310
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

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Dear,

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


Regards
M. Yusuf
 

Watch MrExcel Video

Forum statistics

Threads
1,128,206
Messages
5,629,290
Members
416,384
Latest member
frsamiee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top