Another Unique Values Extraction Problem with multiple criterias with use of formulas please.

[brockk]

Hello to all you Excel Gurus,

I have been trying to find the solution to my issue for some time now and just could not find the Correct Formula to provide me the desired result. Everyday I receive a daily sales report to which I add to my master worksheet. The issue that I am confronting is in the way the report is designed when downloaded. Let me explain it a bit further: in the report you have in Column A: Date of Sale, Column B: Salesperson's Name, Columns D - J: Amounts of each product that was sold, Columns K - R: Financial and Operation Costs Info and last but, not least, Column S: the number of approximate clients/public visitors that entered the establishment (or as we refer to them as daily footsteps). Here is an example of what I am refering to:

7/1/17, Triple-H, sales and other info from col. D - R, 295 <-- # of footsteps
7/1/17, Randy Orton, "" """ """" "" """ """, 295
7/1/17, John Cena, "" "" """ "" "", 295
7/2/17, Ric Flair, "" "" "" "" "", 218
7/2/17, Roddy Piper, "" "" "" "" "", 218
7/3/17, AJ Styles, "" "" "" "" "" "", 295
7/3/17, Kevin Owens, "" "" "" "" "" "", 295

The problem I have been confronting is that I would like the use of helper columns (example, column AX and AY, that can provide me just the unique date along with the unique footsteps.)

* Keep in mind that the footsteps can rarely repeat themselves and must be taken into consideration for the formula.
* Keep in mind that the worksheet is updated daily so the ranges will constantly be varying

The desired output in this case would be:

col AX Col AY

7/1/17 295
7/2/17 218
7/3/17 295

So, if I wanted to analyze the data based on the number of footsteps from the sales of 7/1 thru 7/3, I know it would be from 808 footsteps and not from 1,616 as it would show based on the example above shown.

This is the only way I have found that I can simplify the data so I may use it in a Pivot table to analyze some data for a Dashboard that I am working on. I don't that pulling the desired info straight from the pivot table is possible as it either sums the repetitve footsteps. Hope this helps! Thanks in advance for your help.

 

[oldbrewer]

date

name

footsteps

01/07/2017

ann

755

01/07/2017

sid

755

Count of date

01/07/2017

bill

755

date

Total

02/07/2017

ian

888

01/07/2017

3

755

02/07/2017

fred

888

02/07/2017

3

< < <a simple pivot table finds the unique dates

888

02/07/2017

ed

888

03/07/2017

2

456

03/07/2017

sue

456

Grand Total

8

03/07/2017

harry

456

offset match to find footsteps for each date > > >

easy to make a pretty table now with dates and footsteps

as you add more days to your left hand table

the dates and footsteps will automatically update

<colgroup><col><col span="5"><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[brockk]

Thanks for the response Old Brewer but if you see the original post, this is not the desired result. in your example, the desired result would then have been:

7/1/17 755
7/2/17 888
7/3/17 456
Grand Total 2,099

the thing is that the report repeats the # of footsteps for the whole day on every line of the report regardless of how many people the sales rep attended or not during their shifts. So if during the workday 300 footsteps were done and I had 6 reps during that day, the report will show 300 footsteps 6 times for that particular workday and I only need it to reflect it ONCE so I may do further analysis with the data. Hope this is clearer now. Thanks.

 

[mse330]

How about adding 1 more helper line & use =COUNTIF($A$2:A5,A5) (change the ranges) to count the occurrences of the dates & use 1 in your pivot table ?

 

[oldbrewer]

did you scroll to the right on my post - I think it does what you want...........

 

[brockk]

did you scroll to the right on my post - I think it does what you want...........

@ Oldbrewer, my apologies on my reply earlier. I guess I didn't scroll and see the actual result. Duhhhhhh LOL! You state an offset/match formula? Interesting, .... I am not very familiar with the offset/match combo. What would the formula look like in the columns above mentioned look like then. Sorry about my lack of knowlege on the use of this combination. I've tried the Index/match combo before for lookup situations but, I must admit, I never have used the offset/match combination. Could you kindly elaborate on this for me. Thank you for your help.

 

[brockk]

How about adding 1 more helper line & use =COUNTIF($A$2:A5,A5) (change the ranges) to count the occurrences of the dates & use 1 in your pivot table ?

Thanks for the reply mse330. I am not sure how to go about it as you state. The total occurences will definitely change from day to day. I will definitely give it a shot to see how this formula will work and post my result. Thanks for your suggestion and help.