Analysis using different criteria of different lines and columns.

Hello everybody,

First of all, sry for my English, as German is my mother tongue.

I have to make an analysis of Data, which need to meet certain criterias in lines and columns. I added a screenshot of the table..

Criterias to be met:

1. per day
2. per name
3. an entry "Voucher in column "Produkt" exists.

Furthermore I need to analyse those entries. So I need to know, which person consumed how much with their voucher. (on weekly, monthly... basis), what is the average consumption with a voucher and so on... Maybe some products need to be excluded, as they can`t be ordered with a voucher.

How could I solve that?

Thanks and regards
Paul

the first step to solving your problem is to replace the screenshot with an actual excel file with the data (make sure it has no personal or company confidentia information)

Telling you how to do it will take many words of which half you might not understand.
Building the answer in an good example file is often easy and will help you understand the solution given

Originally Posted by Roel Jongman

the first step to solving your problem is to replace the screenshot with an actual excel file with the data (make sure it has no personal or company confidentia information)

Telling you how to do it will take many words of which half you might not understand.
Building the answer in an good example file is often easy and will help you understand the solution given

Thanks Roel for the answer! I attached an example file!

Thanks
Paul

Pivottables will be a good solution for your problem, they can be very flexible but you need some improvements of your input date.

- remove the subtotals (lines with "Ergebenis") from your data
- Make sure every row has a date (and time) ad name Best solution is to have it arranged in the source so the output gives that info on all lines
If you cannot get your source file to deliver it like that then there is a trick to do it.

HOw to fill empty field with the value
1. Select Column A and B
2. Press F5 (Go to screen pops up)
3. Press Special button
4. Select "Blanks" (will select only empty cells)
5. Press OK
6. IN the active cell type "=" and point to cell right above
7. Press CTRL + Enter at the same time

Now all Blank cells will get a formula pointing to the cell just above it. and your data is complete.

When that is done it is easy to make a pivottable, as I have done in the attachment.
For The date and time you can eighter use grouping or make help columns in your data (Tabelle1)
I choose grouping this time..
With slicers you can easy filter on the for you important fields

Originally Posted by Roel Jongman

Pivottables will be a good solution for your problem, they can be very flexible but you need some improvements of your input date.

- remove the subtotals (lines with "Ergebenis") from your data
- Make sure every row has a date (and time) ad name Best solution is to have it arranged in the source so the output gives that info on all lines
If you cannot get your source file to deliver it like that then there is a trick to do it.

HOw to fill empty field with the value
1. Select Column A and B
2. Press F5 (Go to screen pops up)
3. Press Special button
4. Select "Blanks" (will select only empty cells)
5. Press OK
6. IN the active cell type "=" and point to cell right above
7. Press CTRL + Enter at the same time

Now all Blank cells will get a formula pointing to the cell just above it. and your data is complete.

When that is done it is easy to make a pivottable, as I have done in the attachment.
For The date and time you can eighter use grouping or make help columns in your data (Tabelle1)
I choose grouping this time..
With slicers you can easy filter on the for you important fields

Dear Roel!

Thanks! Looks good! Thank you very much for your efforts.

Only point, for now there is no criteria "Voucher" in the game... so let's say I have a big list starting in January. Some guests will have purchased a Voucher, some not. So only the ones who have got a Voucher should be added to the pivot. And to be perfectly correct, the price of the Voucher shold be excluded... hmmm.

note: please use the normal reply button instead of quoting previous, it keeps the thread better readable

I was wondering about the voucher. The solotion for that depends on how vouchers come into the data. To indicate if a spend was on a voucher or not you probably need a column to indicate that,
excluding voucher line with the (Start) amount should be possible thru filtering

note: sry for that!

Well, basically the data comes like the copy of the XLS...

So I would need to do it manually then, but better then nothing, and doesn't take to long I guess..

So, would it be a solution, to ad a column and enter a formula, that checks: if for the same name and same date an entry "Voucher" exists in the products column. If so, that write Voucher f.e. (just as a flag) Then I could sort the entries and easily delete all entries without voucher flag, and the voucher entries itself. Finally I could create the Pivot? Maybe a VLOOKUP helps?

Thanks
Paul