Manual Sales Report Help

[chris32094]

Hi All,

I have a bit of a challenge...
I use this retail software that spits out these awful csv reports that don't provide the information I need -- unfortunately it lumps all sales together, whether they are actual sales or comps.
I want to create a formula or formulas to isolate all items sold on transactions that are not comped, so I can do a proper sales analysis.

For example,

Where E129 is the transaction number and F129 is the payment method;
If the payment method is anything other than blank (which would indicate a comp), I'd like to return the entire row of values under the actual transaction located in A178 onto another sheet.

The tricky part about this is that there are of course transactions that have multiple items, such as A192.

If you can help, PLEASE do, because I'm stumped.

https://docs.google.com/spreadsheets/d/1oeppy9eaNGgo_DR8u4Gx4tJCHx1FaCWdgEOKTC0nW3U/edit?usp=sharing

 

[RasGhul]

Hi Chris,

Hopefully I understood correctly that you want to extract all product items detail rows to another sheet.

In this mock up I've made a convert sheet that isolates the rows that are items. Copy all columns of your dayreport data into the paste area on the convert sheet.


See if this suits;

https://www.dropbox.com/s/4nvsregjjy3jmu4/DayReport Converter.xlsx?dl=0

 

[chris32094]

This is absolutely amazing and extremely comprehensive, thank you SO much!

Is there a way to record the Lite Server ID rather than the product ID on the output sheet? Is there also a way to record the Payment Type in another column as well for each transaction (this can be for each item if that's easier)?

ALSO, is it possible to ignore entire Lite Server ID tickets that have $0.00 values -- for example, E134 (Lite Server ID 36590) shows there was no payment in F134.
The lack of payment indicates a comped ticket, which has already been recorded by the Managers on duty. I want to use this report to document actual sales (excluding comps), and be able to analyze best and lowest selling items within a given time frame.

Thank you again -- I've been trying and trying to figure out how to reconcile this myself, but I think this level of Excel is simply beyond my skill set at this time.

 

[RasGhul]

You're welcome, thanks for the feedback

I'll check it out in the morning for you.

 

[RasGhul]

Hi Chris,

We're now returning Lite Server ID Numbers and ignoring rows that have blanks in the payment types/receipts overview.

I would recommend using the Convert & Output tabs to extract the data, then copy this data as values only into the Data tab table.

Then run your sales pivots and formulas from your Data table sheet, so it doesn't slow down the workbook.


https://docs.google.com/spreadsheets/d/1ozvE0CAxzm0KTybfc7a67HN9NGb9GTFDIfNqZf9IZGc/edit?usp=sharing

 

[chris32094]

Hi RasGhul,

Again THANK YOU SO MUCH!

And my apologies for the rather delayed response -- I've been away for the holiday and am not just catching up on my e-mail.
I tried using the sheet you created to start compiling sales data for other days (aside from 6/27) and it's returning a bunch of errors. Am I doing something wrong?
I've pasted the day reports' columns A:I (not the entire columns, just up until the data ends of course) as text and only columns L and M are populating.

Here are three more day reports I've uploaded so you can see:

6/28 https://drive.google.com/open?id=1NUsmyCzg8wraMwh_D6VE5XyCBsnGkLP-

6/29 https://drive.google.com/open?id=1MF3frozj9o7HjjrpPGnPwcuP6Tid45_4

6/30 https://drive.google.com/open?id=1xoQnJkyHjdjiw_9Hgi-I2Bkz7d1r9kLD

 

[RasGhul]

I've freed up the formulas on the convert tab, and I also needed to have my PC on US Date Region, date format mm/dd/yy. When I reverted my PC back to my region & format the date formulas result in an error.

I also test just copying only from the receipts overview line from your csv's and it still extracts as required. cvs reports 27th, 28th, 29th & 30th was extracted one at a time and copied as values in the data tab.

Note when copying & pasting from your csv make sure you paste as values on the Convert tab cell A2 - using the 123 paste option.


https://www.dropbox.com/s/555zd6l5we5xfnf/DayReport Converter_0710.xlsx?dl=0