Statistic layout with Drop-Down list with multi selections

[petmak]

Hi Expert I need help with the best way to show comparison between two tables.
The company where I am working have 20 different stores, more than 15 bank account. marketing almost world wide ( Online ) they want to compare Credit Cards transactions last 3 month Vs the same period last year to identify the best behavior for each credit card to diverted to the most successful bank account and also which country will work the better with each bank . we are talking about more than 130K lines each table.
the problem I am facing is the data is huge and to create two PIVOT table not showing the message in one screen you know how managers are.
I attached a sample of draft of how I am expecting to deliver unless you can advise with something better.
I tried to us VLOOKUP with Multiply conditions and INDEX/MATCH data takes too much time to update.
Not sure how to solve this issue.
Thank you for your help and advise in advance.

[petmak]

Hi Sorry didn't add data sample to the file

[JeteMc]

I feel that in order to be of any help we would have to see the data that relates to the values on sheet 1.
The only possible relation I find is that the amount in E2 on the data sheet may relate to the value in B5 on sheet 1 IF it has occurred within the past 3 months however there is no way to know as there is no date given.

[petmak]

HI JeteMc
Thank you for your time.
Please find attached new sample data with more details,
I used to PIVOT each table separately and copy past the data into the main file to get total COUNT Approve now next Approve last year and so on and this takes too much time to sort everything
I tried to add everything in the attached file and I am sure that the new fill will give you better idea.
The main aim for this report to check the behavior of each CC per store per bank which one of these will work better as we can set our system to send each CC transaction where we want for example if we see that BIN number 3 working better with store 5 and then Bank 3 we can do this. just to improve total sale according to this statistic
Thank you for your help in advance and waiting for your reply

[JeteMc]

Perhaps this will be of some help.
On the Main sheet fill in the blanks in row 2 with the Store (1), fill in the blanks in row 3 with the Bank.
Note that both are hidden using font color white.
The formula for Store 1 is:

Please Login or Register  to view this content.

Note that this process would need to be repeated for Store 2 etc.
On the Table 1 ( Recent Data ) sheet and on the Table 2 (Historical Data) sheet another column is added and filled using: =INDEX(Main!B$26:B$29,MATCH(E3,Main!A$26:A$29,0))
Note that the above column may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.

[petmak]

HI JeteMc
Thank you so much for your help, it is working 100% the exact way I want it,
Just want to add two extra adjustment to make the file more flexible.
I was thinking to change it to dynamic, I mean to get a unique list of BIN numbers from the recent data as this report should be updated daily so :

1st point : in Cell A5 I tried to get a unique list for BIN# using the following formula but it is not working =IFERROR(INDEX('Table 1 ( Recent Data )'!A3:A144,MATCH(0,COUNTIF($A$5:A5,'Table 1 ( Recent Data )'!$A$3:$A$144),1)),"")


2nd point: If we change the two data sheet to table ( CTRL+ T) to make it dynamic instead of locking the looking formula to a specific range of column and rows

is this possible, I am trying to make it nice and easy and avoid any editing to the perfect cods you add to this file and all we need to do just update the data into the table and formula range will update automatically.

I don't know what to say, MANY THANKS and waiting for your reply.

[JeteMc]

As to the 1st point, try:

Please Login or Register  to view this content.

As to the 2nd point: Yes, if you convert the ranges, starting with row 2, to tables then the formulas will update as new rows are entered (see formula in cell A5)
Let us know if you have any questions.

[petmak]

HI JeteMc
Thank you so much for your reply, I understand my mistake I didn't use INDEX.
Can I ask you one last thing and I promise this will be the final
Can we change the value in the table instated of total transaction to be total count ie( Cell B6 instead of $35,500 to be 9 transaction )
Thank you in advance
Regards
PetMak

[JeteMc]

Please try the following in cell B8 and then dragged across to cell I8:

Please Login or Register  to view this content.

Assuming the values in columns J:K should sum the respective values in B8:I8 use
For J8: =SUM(B8,D8,F8,H8)
For K8: =SUM(B8:I8)-J8
Let us know if you have any questions.

[petmak]

HI JeteMc
Thank you, I meant instead of showing total value transactions per Cell to get total count.
For example
Cell B6 for BIN 513379 it is showing total value $ 35,500 from total count 9 transaction
and in Cell C5 Bin 474839 it is showing total value $ 63K from total count 2 transaction
and in Cell C6 Bin 513379 it is showing total value $ 30,600 from total count 9 transaction
I want to change the whole table from total value to total COUNT
Thank you

[JeteMc]

The formula in B5 could modified to read:

Please Login or Register  to view this content.

Once the formula is entered copy down to B6 and then over to I6.
Let us know if you have any questions.

[petmak]

HI JeteMc
Thank you so much for all you help, sorry just replying you now, I heart my back and was offline for a while.
I sent the report to my managers and hope there is no more change.
Appreciate all you effort for helping me sort this problem and wishing you all the best.
Regards
Petmak

[JeteMc]

You're Welcome and I hope that you are able to recover from your back injury quickly. If your managers approve, then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[petmak]

HI JeteMc
Thank you, everything in order and everyone is happy
Thank you one more time for your help

[petmak]

HI JeteMc
Trust you are well.
Sorry to bother you again.
One of our manager asked if we can add drop-down list with multi choices for Currencies and Countries
They aim to extend the analysis deeper to see which countries could be the best sales for example ( South Africa, Egypt and Canada )
I can adjust the code to point to single Currency and single country but multi choices I can't
Could you please help on this.
Thank you
PetMak

[Punnam]

Hi Petmank,

Find the attachment in which I have updated the data validation.

Please Login or Register  to view this content.

Punnam

[AliGW]

Punnam - you have been asked before (by me and an administrator) to explain in your post solutions offered in an attachment. Please do so for the benefit of all readers. Thank you.

[petmak]

Hi Punnam
Thank you for your quick reply.
I was looking for muti choice more than one selection. so if they want to check Africa for example, they will choose Egypt, South Africa, …. etc
Thank you

[Punnam]

Please Login or Register  to view this content.

This will do

The difference part

Please Login or Register  to view this content.

Punnam

[petmak]

Hi Punnam
Thank you for your quick reply.
the way you set it still limited to 4 or 6 chooses, what if they want to select more than that 20 country for example ? remember the example excel file not including all data it is just simulations and believe me realty include most of the country worldwide and all currency too.

I was looking for Full list with an option to choose more than one item but not limited to 3 or 4 if you understand what I mean.

[Punnam]

Hi, petmak,
=IF(ISODD(COUNTIFS($B$4:B$4,B$4)),SUMPRODUCT(SUMIFS('Table 1 (Recent Data)'!$F$3:$F$144,'Table 1 (Recent Data)'!$C$3:$C$144,$A$2,'Table 1 (Recent Data)'!$D$3:$D$144,$B$3,'Table 1 (Recent Data)'!$G$3:$G$144,B$4,'Table 1 (Recent Data)'!$A$3:$A$144,$A5,'Table 1 (Recent Data)'!$B$3:$B$144,Main!$B$2:$E$2)),SUMPRODUCT(SUMIFS('Table 2 (Historical data)'!$F$3:$F$144,'Table 2 (Historical data)'!$C$3:$C$144,$A$2,'Table 2 (Historical data)'!$D$3:$D$144,$B$3,'Table 2 (Historical data)'!$G$3:$G$144,B$4,'Table 2 (Historical data)'!$A$3:$A$144,$A5,'Table 2 (Historical data)'!$B$3:$B$144,Main!$B$2:$E$2)))

You can increase the range as per your required length just copy the validation and likewise increase the bold part of the formula

Punnam

[petmak]

Hi Punnam
thank you for your help,
I think this option will not work my length for this table is 11 column. if I do it this way the table will be very wide and will not look nice if you agree with me.
can you adjusted it to be drop-down with multi chooses ? if blank means everything and if any country ticked and selected mean theses only ?
Thank you.

[JeteMc]

Drop downs will not allow multiple choices. I have read that Combo boxes will do that, however they are controlled using VBA of which I have too little experience to be of help. I suggest closing this thread by marking it 'Solved' using the thread tools menu above your first post and then opening a new thread on the VBA forum at this site. I hope that you have a blessed day.

[Punnam]

Hi Petmax,
I tried something new and it worked out for your information.
Create a list of countries as I did in Table-1 L3 to L13 and name it as Country
Create a list of countries as I did in Table-1 L3 to L12 and name it as All
Now for use of data validation in B2 use "country" and update the formula as below

Please Login or Register  to view this content.

The actual updation part is IF($B$2<>"All",$B$2, All), and it's working fine.
Punnam

[petmak]

Hi Punnam
Thank you for your reply.
this option looks fine but still not what I am looking for.
1st to add list with all Countries and Currencies is ok because they are looking for all transaction including everything is ok.
The problem here is when they want to choose more than one country and more than one Currency, if I copy the drop-down till M3 this will be 12 countries and 12 different currencies and this is not the case what if they want to choose more than that might be 20 or 25
Hope the above explain the challenge, I think this issue could be sorted with VBA codding if you agree with me.
Thank you

[AliGW]

I can move this to the VBA section for you, if you wish. You should NOT, as advised in post #23, open a new thread and you should remove the solved tag from this thread for now.

[Punnam]

Hi Petmak,

As suggested by "Aligw" in Post 26, I recommend moving the thread to VBA because such kind criteria are easily taken care.

Punnam

[petmak]

I can move this to the VBA section for you, if you wish. You should NOT, as advised in post #23, open a new thread and you should remove the solved tag from this thread for now.

Hi AliGW
I thank you, I already created a new ticket in the VBA section yesterday
I was just replying to Punna as he sent me message to thank him

[AliGW]

I am sorry you were ill-advised - I have closed the other thread and will move this one to the VBA section with an improved title.

[petmak]

Thank you AliGW

[JeteMc]

Hello petmak, I am sorry to see that there has been no help offered on this thread since last Thursday.
Was wondering if a Power Pivot (free add-in from Microsoft for Excel Version 2010) and pivot table option might be acceptable.
Attached is a sample of how that might look.
The Power Pivot produced table is on sheet 1 and the pivot table is on sheet 2.
Multiple countries may be chosen using the slicer and there are also filters for store and bank.
As you may already be aware the values could be changed from sum to count in the field list.
Let us know if you have any questions or interest in pursuing this further.

[petmak]

Hi JeteMc
I don't know how to thank you for your concern and following up and offering help.
Previously I was using PIVOT table to provide the report but the data are huge and to update this report daily takes about 35 min everyday to do this.
Please consider when manager checking around and changing any thing it takes the same time with every single changes.
the way you helped me with the report previously shorten the time to 09 min and also I have the ability to customize the report the way I see it ( new & Old ) next to each other and this is helps with analysis, just missing this drop-down list and I believe the report will be perfect.
I will applies the changes you suggest to the real data and see how it will work.
Thank you.