I can't remember how to do it.

[dpaton05]

Hey everyone, I have not used excel for a year and a half as I lost my job, in which I needed to use it and I have only recently found I have a need for it in my personal life. I want to design a spreadsheet that allows me easy access to my sales and related information. The sales will be for an inspirational book I have written regarding my recovery journey from a severe car crash that almost killed me. The book is on my website and most people that buy it are doing so through paypal, with a few people that are local, giving me cash/cheque directly. I am using Payhip to facilitate the electronic purchase as they provide a service that accepts payment for digital downloads.

I can download a csv file from payhip that is quite detailed. This is the top row that I copied from the csv file. I have no idea why date is right at the end, that just seems very weird.

Email

First Name

Last Name

Currency

Amount Gross

Amount Net

Status

Num of Items In Cart

Items In Cart

Payment Type

PayPal/Stripe Fee

Payhip Fee

Unsubscribed From Email Updates

Unconsented From Emails (EU GDPR)

EU VAT Applied

EU VAT Amount

EU VAT Included In Price

EU VAT Country Name

Custom VAT Applied

Custom VAT Amount

Custom VAT Included In Price

Custom VAT Country Name

UK VAT Applied

UK VAT Amount

UK VAT Included In Price

Checkout Questions Enabled

Checkout Responses

Intake Form

Coupon Code Applied

Coupon Code

Coupon Percentage Off

Coupon Discount Amount

Shipping Address

Country Name

Phone Number

License Key

Date

Some of the fields I may not use yet and the first field, Email, is in A1 of the csv file. I would like to be able to periodically download the updated csv file from payhip, store it in the same folder as this spreadsheet that stores all the information (I will call this my control spreadsheet) or a folder within the folder that stores my control spreadsheet. I then want to be able to press a button in my control spreadsheet and have it copy and new transactions to this spreadsheet. The downloaded csv file, depending on the date range selected may have entries that are already in the control spreadsheet, that is why I only want it to copy new entries to the control spreadsheet.

I want the copied entries to be sent to a sheet other than sheet1 and be able to perform analysis on sheet1 of the data.

As I said, I have not used excel for a long time and cannot remember how to do all these things. I would like to use arrays and I was wondering if someone could point me in the right direction as to how I start this project please?


Thanks so much everyone,
Dave

 

[jolivanes]

You mention that you download the updated csv file. So this file has all the data from beginning to end.
You should not have to find the last copied data in your workbook, just clear the sheet and put all the data in it.
You can put all the csv data in an array and just populate your sheet with the columns you want from that array.
Which columns do you want to copy into your sheet.

An alternative would be to save your csv file as an excel file and clean it up.

 

[dpaton05]

Ok. At the moment, I have a spreadsheet called Cash_sales that has all the data in it about people that have bought my book and have used cash or cheque. I also have the csv file that I download from payhip, that is called Customer_list, and this has all the information about sales made through paypal.

Tell me if this is not a good or efficient way to analyse the data but I was thinking of having a seperate control spreadsheet with a button that would open both Cash_sales and Customer_list, put both of those spreadsheets data set into an array. There would be a range of options to allow analysis of the data on the spreadsheet, such as sales in a set time period and then display results in a combo box or something similar on the sheet.

The csv file I download from payhip is called Customer_list and the file that stores cash sales is called Cash_sales.

I would like to be able to input data about cash sales in control sheet and have those sales saved to the next row on my Cash_sales spreadsheet but there is nothing I need to update on the Customer_list spreadsheet.



I have uploaded a copy of the current Cash_sales and Customer__list spreadsheets, but without the emails for privacy reasons. The Customer_list file is in the exact format as when it is downloaded from payhip.

Customer_List without emails.csv

drive.google.com

Cash_sales without emails.xlsx

Sheet1 Date,Email,First Name,Last Name,Amount Gross,Amount Net,Type 6/17/2022,20,20,Cash 5/7/2022,100,100,Cash 4/27/2022,30,30,Cheque

docs.google.com