Hello. What I am attempting to do is link a person's name in the Sales Allocation2 COL to the CompletedJobsByMonth.
The Customer Name is the lookup on both tables.
CompletedJobsByMonth has 2741 rows of data
I was expecting 2741 rows of data after adding the BDM Name after the merge.
I am am getting 4580 rows of data after merging using TEXT as lookup.
Please can someone tell me what the issue is.
If I remove duplicates from the Job Ref column then I get back to 2741
Thanks very much
You have duplicate rows in the Sales Allocation2 COL sheet.
Oooops! Velouria was a bit quicker than me, but I'll give you my answer anyway.
First you need to get rid of duplicates in the Allocations table, where both the Customer and BDM are the same. That will reduce the number of rows for that table to 2,486.
When you then run the merge you'll end up with 2,750 rows. I.e. 9 more than what you expect and that's because there are two Customers who have two BDM's each. In total, these two customers have nine jobs that are, thus, double counted.
The customers concerned are:
GUINNESS NC - NORTH RENTAL (Gary and Maddy)
WORKMAN AND PARTNERS - BIRMINGHAM (David and Jordan)
I can't tell why this is this but you probably can. Are these customers sharing BDM's or did one succeed the other during the year?
Thanks very much Velouria and Riny,
I have done as you suggested and it is sorted. The two records - GUINNESS NC - NORTH RENTAL (Gary and Maddy)
WORKMAN AND PARTNERS - BIRMINGHAM (David and Jordan), I will hear back from the person who supplied me the data, which needs to be deleted.
Regards Tim
