brockk
Board Regular
- Joined
- Jul 1, 2006
- Messages
- 170
- Office Version
- 2013
- Platform
- Web
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.
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.