Raw Data below:
I need to create a report that shows:
Till ID, Sales Person and Time of first sale.
I have used MID to extract the till number and then used IF(AND ..... If till number = 1629 and Column C = sales then return time in column B but this has failed miserably.
I want to show that 1629 - James Best, made his first sale at 12:00:54, whereas 1642 - Sally James, made her first sale at 13:13:56.
I have around 200 sales people to show data for and it's too time consuming to do it manually.
Would really appreciate some help ....
[TABLE="width: 289"]
<colgroup><col width="70" style="width: 53pt;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="70" style="width: 53pt;" span="3"> <tbody>[TR]
[TD="class: xl68, width: 70, bgcolor: transparent"]A[/TD]
[TD="class: xl67, width: 103, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]C[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]D[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 4"]Sales Person & Till Number: 1629 - James Best[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 3"]Run Report Interval: 19/09/2017 18:00:54[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:00:54[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:00:54[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:01:11[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:33:56[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:34:23[/TD]
[TD="class: xl68, bgcolor: transparent"]Exchange[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:46:59[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:48:26[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:02:25[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:02:25[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:05:05[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:05:05[/TD]
[TD="class: xl68, bgcolor: transparent"]Exchange[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:05:06[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 4"]Sales Person & Till Number: 1642 - Sally James[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 3"]Run Report Interval: 19/09/2017 18:00:54[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]12:00:31[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]12:00:49[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]13:01:11[/TD]
[TD="class: xl68, bgcolor: transparent"]Exchange[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]13:13:56[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]13:34:23[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:46:59[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
I need to create a report that shows:
Till ID, Sales Person and Time of first sale.
I have used MID to extract the till number and then used IF(AND ..... If till number = 1629 and Column C = sales then return time in column B but this has failed miserably.
I want to show that 1629 - James Best, made his first sale at 12:00:54, whereas 1642 - Sally James, made her first sale at 13:13:56.
I have around 200 sales people to show data for and it's too time consuming to do it manually.
Would really appreciate some help ....
[TABLE="width: 289"]
<colgroup><col width="70" style="width: 53pt;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="70" style="width: 53pt;" span="3"> <tbody>[TR]
[TD="class: xl68, width: 70, bgcolor: transparent"]A[/TD]
[TD="class: xl67, width: 103, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]C[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]D[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 4"]Sales Person & Till Number: 1629 - James Best[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 3"]Run Report Interval: 19/09/2017 18:00:54[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:00:54[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:00:54[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:01:11[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:33:56[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:34:23[/TD]
[TD="class: xl68, bgcolor: transparent"]Exchange[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:46:59[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:48:26[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:02:25[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:02:25[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:05:05[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:05:05[/TD]
[TD="class: xl68, bgcolor: transparent"]Exchange[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]13:05:06[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 4"]Sales Person & Till Number: 1642 - Sally James[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, colspan: 3"]Run Report Interval: 19/09/2017 18:00:54[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]12:00:31[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]12:00:49[/TD]
[TD="class: xl68, bgcolor: transparent"]Refund[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]13:01:11[/TD]
[TD="class: xl68, bgcolor: transparent"]Exchange[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]13:13:56[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]13:34:23[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]12:46:59[/TD]
[TD="class: xl68, bgcolor: transparent"]Sale[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]