• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with creating a table with provided data for specific dates

Posthuman

New Member
Hello again Last Excel Benders!!

This time I need tables.. Actually 2 tables..

I use this data every day and make 2 tables with them.. and report them to my directors. I have lame formulas which really does not help me to automate the process..

I explained details in the excel file which I attached. Hope it is clear. If not, I can answer your questions.

Thanks in advance!
 

Attachments

  • EXAMPLE (1).xlsx
    14.8 KB · Views: 8
Thanks for answer vletm, I was aware there is a pivot table option. But pivot not a total solution for me because I need data in this tables, otherwise I need to do extra work to convert them in this format. Since I need this process automated, pivot may not be a saver :(

Also "controlled?" status is kinda tricky and pivot can not handle it very well. But thanks for your opinon anyways.
 
@Posthuman ... You asked only answer to that step. If You could give an idea what do You want for next step too. It would be much easier to help You.
 
Hi again vletm,

I actually need formulas in that tables which generates required numbers. Not a pivot table. Because pivot table does not give me all the necessary information such as "controlled" status.. And it is not in the format that I need. I have to copy them from pivot & paste them in the table and re-organize. It means I need to do more work than current situation.

If the data in "controlled?" column has "not controlled" it should not count in tables (both for daily and monthly table). If something else writes there it should count as controlled.

As you can see control date always later than utilization date. So it may be tricky.

In the end I need 2 tables like the one I manually filled in the excel file with provided "Example Data".

Hope this clarifes.

Thanks!
 
Thank you very much vletm! You are so kind!

Seems it works flawless! But what I shared here was an example excel file. Could you explain or share how to do it? If possible with formulas? As you know, I just can not copy and paste "DO" button to my actual worksheet :)

So if I know logic behind this, I can do it with my files. Also in the future if something changes I can alter formulas accordingly.

Thanks again!

Edit: Seems this is a macro. Is not it possible to do this with formulas? :)
 
Last edited:
@Posthuman ... What about those differences between Your and this?
Did You find/solve the reasons of differences?
New result tables can move upper, in the right position, after You know which is correct.
>> How do this work:
The basic thing to do this is try to do it as manually, step by step.
The macro is someway 'do with Your instructions'.
This uses formulas, but if You have a huge data, then You'll have a lot of formulas copied or so. That makes more possibilities to get mistakes.
Later, if You'll need to make changes, You could make Your changes to Marco, not to sheet.
>> How to use/share this ...
Your data have to be in same format.
You can have more data rows or channels.
You'll have many possibilities to get this work with You...
1) You'll copy Your data to this workbook and You'll do Your calculations with this.
2) You'll save Your 'the real data-file' to .xlsm or .xlsb -format. After that, You'll copy 'EXAMPLE(1).xlsb - Sheet1 (Code)'s two macros to You 'real-files' code-page. If You want, You can make those buttons to You sheet too.
3) You'll send Your 'the real file' for me, via inbox, and I could make it working for You. There no need to be all data, some data needed. I need just one 'data-sheet'.
Ideas?
 
Hi again,

Thanks a lot for all this detailed helps.

I can not share an excel file right now but,

The data format is almost same in "real file". Just 2 more columns (in total 7)and their positions different. Actually it is something like below. So, you can put random numbers there and test them. Since table doesnt include customer name and number no need it. Below titles can start from A1 and goes like B1, C1 in this order..

Control Date
Utilization Date
Customer No
Customer Name
Controller
Amount
Channel

Edit: Also I checked your version.. For left table (daily one) yours correct, mine is wrong. However on right table (monthly) controlled cases for SMS channel actually 15 (you can check them on "controlled?" column) but yours seems 18. I dont know why.
 
Last edited:
Thank you coolsac12,

But however I can not see numbers on right (monthly) table. I guess it's because of different date formats.

Another thing is, on right table (monthly), if the dates change on example data, for example: when datas from february 2016 instead of November 2015 will it still work?
 
Thank you coolsac12,

But however I can not see numbers on right (monthly) table. I guess it's because of different date formats.

Another thing is, on right table (monthly), if the dates change on example data, for example: when datas from february 2016 instead of November 2015 will it still work?

I have attached another sheet..It will work if date is changed to Feb'16.

I am confused how would i know that data is controlled or not? Because expected output data is provided, it's count exclude "not controlled" however Amount includes "not controlled"
For that I used "controller?" column.
In table 1 SMS count of date 11/04/2015 is 2 however expected output shows 0. I am confused for that.

Thanks & regards,
Sachin Gupta
 

Attachments

  • EXAMPLE_(1)(1).xlsx
    18.7 KB · Views: 3
@Posthuman 'example data' <> 'real life'
Those changes are possible to do ... it just needs time to change and check.
Do You need those titles in that order? ( from A to G )
It have to make few new columns..
Is the 1st data row 2nd or 3rd? Now, it's 3rd.
Do You need it?

'Not controlled' & 'Not Controlled' are different words.
Check rows 'Not controlled' 29 & 36 ... after that 'Not Controlled'.
Now, it's 'Not Controlled'!
I left that 'SMS'-checking for Your checking.
 

Attachments

  • EXAMPLE (1).xlsb
    31.2 KB · Views: 2
I have attached another sheet..It will work if date is changed to Feb'16.

I am confused how would i know that data is controlled or not? Because expected output data is provided, it's count exclude "not controlled" however Amount includes "not controlled"
For that I used "controller?" column.
In table 1 SMS count of date 11/04/2015 is 2 however expected output shows 0. I am confused for that.

Thanks & regards,
Sachin Gupta

Thank you Sachin,

You are right about Nov 4. It was my mistake to add them in manually created table. So your table 1 works totally fine. And what you do with "controller" column is also right.
 
Back
Top