Calculate contribution of Salesperson

[AhmedAbdelhai]

Dear,

Every month I receive a sales sheet similar to the one attached below. To do a year to date sales sheet I manually copy the new sales sheet and paste it under the old sales sheet. Then I do a pivot table and some interactive charts I learnt recently. The challenge I am facing is calculating the contribution of every salesperson as the accounts are distributed among them randomly.

I added a column to the sales sheet named "Salesperson " and I created a reference sheet and I was wondering if there is a way excel would check the reference sheet and add the sales person to the corresponding account in column D instead of me doing it manually. This will save me a lot of time and will help me avoid mistakes.

If anyone can help me with that or put me in the right direction to learn how to do it I'd really appreciate it.

Thank you

Download sample sheet here

 

[sandy666]

if you are able to use PowerQuery ...

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Account[/td][td=bgcolor:#70AD47]Salesperson[/td][td=bgcolor:#70AD47]Sales[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

01/01/2019​

[/td][td=bgcolor:#E2EFDA]aaa[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]David[/td][td=bgcolor:#E2EFDA]

2000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

07/01/2019​

[/td][td]ccc[/td][td]A[/td][td]David[/td][td]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

02/01/2019​

[/td][td=bgcolor:#E2EFDA]bbb[/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]David[/td][td=bgcolor:#E2EFDA]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

08/01/2019​

[/td][td]ddd[/td][td]D[/td][td]David[/td][td]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

09/01/2019​

[/td][td=bgcolor:#E2EFDA]aaa[/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]David[/td][td=bgcolor:#E2EFDA]

2000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/01/2019​

[/td][td]ccc[/td][td]F[/td][td]David[/td][td]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

30/01/2019​

[/td][td=bgcolor:#E2EFDA]bbb[/td][td=bgcolor:#E2EFDA]F[/td][td=bgcolor:#E2EFDA]David[/td][td=bgcolor:#E2EFDA]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

06/03/2019​

[/td][td]ccc[/td][td]F[/td][td]David[/td][td]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

04/01/2019​

[/td][td=bgcolor:#E2EFDA]ddd[/td][td=bgcolor:#E2EFDA]H[/td][td=bgcolor:#E2EFDA]David[/td][td=bgcolor:#E2EFDA]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

05/01/2019​

[/td][td]aaa[/td][td]N[/td][td]Ashely[/td][td]

2000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

01/02/2019​

[/td][td=bgcolor:#E2EFDA]ccc[/td][td=bgcolor:#E2EFDA]N[/td][td=bgcolor:#E2EFDA]Ashely[/td][td=bgcolor:#E2EFDA]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

06/01/2019​

[/td][td]bbb[/td][td]R[/td][td]Randy[/td][td]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

02/02/2019​

[/td][td=bgcolor:#E2EFDA]ddd[/td][td=bgcolor:#E2EFDA]R[/td][td=bgcolor:#E2EFDA]Randy[/td][td=bgcolor:#E2EFDA]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/03/2019​

[/td][td]ccc[/td][td]R[/td][td]Randy[/td][td]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

03/02/2019​

[/td][td=bgcolor:#E2EFDA]aaa[/td][td=bgcolor:#E2EFDA]Q[/td][td=bgcolor:#E2EFDA]Randy[/td][td=bgcolor:#E2EFDA]

2000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

04/03/2019​

[/td][td]aaa[/td][td]Q[/td][td]Randy[/td][td]

2000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

04/02/2019​

[/td][td=bgcolor:#E2EFDA]bbb[/td][td=bgcolor:#E2EFDA]W[/td][td=bgcolor:#E2EFDA]Cindy[/td][td=bgcolor:#E2EFDA]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/03/2019​

[/td][td]ddd[/td][td]W[/td][td]Cindy[/td][td]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

06/02/2019​

[/td][td=bgcolor:#E2EFDA]ddd[/td][td=bgcolor:#E2EFDA]X[/td][td=bgcolor:#E2EFDA]Cindy[/td][td=bgcolor:#E2EFDA]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]aaa[/td][td]T[/td][td]Randy[/td][td]

2000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

01/03/2019​

[/td][td=bgcolor:#E2EFDA]bbb[/td][td=bgcolor:#E2EFDA]T[/td][td=bgcolor:#E2EFDA]Randy[/td][td=bgcolor:#E2EFDA]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

31/03/2019​

[/td][td]ddd[/td][td]S[/td][td]Randy[/td][td]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

05/02/2019​

[/td][td=bgcolor:#E2EFDA]ccc[/td][td=bgcolor:#E2EFDA]Z[/td][td=bgcolor:#E2EFDA]Cindy[/td][td=bgcolor:#E2EFDA]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

05/03/2019​

[/td][td]bbb[/td][td]Z[/td][td]Cindy[/td][td]

5000​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Table.NestedJoin(Table2,{"Account"},Table1,{"account"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Salesperson"}, {"Salesperson"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table1",{"Date", "Item", "Account", "Salesperson", "Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}})
in
    #"Changed Type"[/SIZE]

btw. on you sales sheet exist date which I've never seen: 2/38/2019 . Here the cell with this date is blank

 

[AhmedAbdelhai]

lol my bad
Thank you very much
I have no idea what powerQuery is but thank for putting me in the right direction. I will try to learn how to do it using it.

 

[sandy666]

what is your Excel version ? if 2007 forget about PowerQuery

if 2010/2013 you'll need free add-in from MS site
if 2016 and above - it has PowerQuery built-in - Get&Transform

what is PowerQuery

 

[AhmedAbdelhai]

it's 2016 and I found it under the DATA tab
I'm now watching a tutorial to learn how you did this magnificent work :D

 

[sandy666]

in short...

in your sales sheet table salesperson column is not necessary, you can delete it
then
select your sales sheet table - From Table - Close&Load as Connection only
the same with reference table - From Table - not close&load but...

from PQ editor menu select Merge Queries as New
as first: select salesperson table - select account column
as second: select reference table - select account column
merge LeftOuter
ok
select expand table, uncheck account
ok
reorder columns as you need
close&load

edit: I need new keyboard I think

 

[AhmedAbdelhai]

Wooohooo
That did it!
Thank you a million times!!
You saved me a lot of time

 

[sandy666]

You are welcome
I made an animated procedure but as I see it completely unnecessarily because you managed it

have a nice day