Summary of daily data based on different criteria

Originally Posted by JohnTopley

People may have deemed this to large a project (to do unpaid) so maybe contact "Commercial Services"

Hi John,
Okay, I will try to contact commercial services.

Hi John,
I purchased points, but don't know how to post this on to commercial services. Can you help me to pls how to do that..

I ma working on the work sheet now..pls wait...

I just need the AVERAGE calculation: everything else looks OK.

Also, it doesn't matter if "line nos" show in front of every item/style..

I have attached with the change...

Hi John, it looks great and highly appreciate your hard work. I will take some time to check for any issues.

A few things I want to know;

>> I am going to use this program when each factory sends me the final shet (end of the month). In this case, what should I do? I mean, can I simply copy the macro to " summary sheet"? then will it work without any issue?
>> Does the "run" button work on both "summary sheet" and "data" sheet" when it is clicked?
>> Do I need to change anything when the a "new month" (ex: June, July etc.) data to be analyzed? Because, we create "a new work sheet" for each month, starting from first day of the month.
>> Can I add more rows(depending on the necessity) to the "daily record update sheets"? and what if I delete rows from the "daily data update sheets"

Code is installed in "Module1": instructions below (for EACH workbook)

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]


RUN button does everything. Ensure there is a "Data" tab in each workbook.

Month is in cell A1 (as per sample). Just change this BUT it must the same as on the tabs. I recommend you use "Jan","Feb" etc as the months


You can/add delete rows at will BUT do NOT put any other data below those data columns: it is bad practice.


AND as previously requested, PLEASE remove all confidential data from files posted to this forum. I would remove all the files you have posted on this thread. I will remove the ones I posted.

I followed your instruction and used the code for another work book, but it didn't work properly.
I have little knowledge about macro work outs.

By the way, I also have few questions;

>> Do I need to copy and paste the "Page format" also in the new work book? , I mean column labels, colors, fonts etc..?
>> Should the "Data" tab always be at the end of the work sheets? and "Style Wise Summary" sheet in the same location?
>> "do NOT put any other data below those data columns: it is bad practice",..which data columns do you mean?

I have attached the work book I tried out, pls let me know the mistakes I have done.

One more question;
What if I move "all the data tabs" of of other work books to this VBA modue, without trying to insert macro to each work book?

I can delete all the tabs leaving only "Style Wise Summary" and "Data" tabs in the VBA macro module(original module), and insert all the data tabs of a different work book in between...will it work??

It will work: if you select a month in A1 then it will work on all tabs which contain that name so you could have all months in one workbook.

Change one of the May tabs to JUN e.g "3 May" to "3 Jun" , change A1 to Jun and then click "RUN".

You totally ignore that I said there was to no data in column A or merged cells.

The problem is simple due to "corrupt" data which also appeared to have corrupted "Data" : I had to unmerge in all sheets.

I also note there are empty sheets which I do not test for as I assumed there always be data and only lines used would appear in column A.

AND you still post files with confidential data.

Clean up your data.

I was stupid not to see the merged cells hidden at the bottom of the sheet. I un merged all and deleted information on the column A. Now It worked. By the way, what if there is an empty row in the sheet tabs (daily data tabs)?

Hi John, I am getting some "warning" when I save the document..pls advise ..attached the screen shot...

I have amended to code to ignore the empty tabs in the "Data" sheet: See attached. I have also changed the customer names.

Please Login or Register  to view this content.

I have noticed two issues in the "Style Wise Summary" sheet

>> Number of days run of the style AWS-9036MN should be the number of "calendar days" (ex: 3 may, 4 may, 5 may...15 may..etc) of the particular month. Accordingly, this style has run 10 days in the month of May.

I think here, the macro considers "the first entry" of the style.

>> Secondly, you may see this AWS-9036MN again appears under the same line with some different value for the "number of days run". Pls look in to this..

This was noticed as per the last work sheet (test mod)..

I would like "Number of Days Run" to be the number of "calendar days" the particular style has run in that "particular month". The reason, is I need to calculate the Loss and profit of the style "based on the number of days" that particular style has run in the particular month.

Originally Posted by Anuru

I have noticed two issues in the "Style Wise Summary" sheet

>> Number of days run of the style AWS-9036MN should be the number of "calendar days" (ex: 3 may, 4 may, 5 may...15 may..etc) of the particular month. Accordingly, this style has run 10 days in the month of May.

I think here, the macro considers "the first entry" of the style.

>> Secondly, you may see this AWS-9036MN again appears under the same line with some different value for the "number of days run". Pls look in to this..

This was noticed as per the last work sheet (test mod)..

I found why the same style appearing two times; it happens when there are blank rows under a style whose "customer" information is mentioned/selected. Also it happens only when the "same customer name" is mentioned in a blank row.

See attached:

However this duplicating happens only once, for example, if there are "two blank rows" whose customer names are mentioned, it doesn't duplicate two times, instead it appears only one more time only. When the customer name is deleted from the blank row, it doesn't duplicate.

Where are these errors: I cannot see any summary.

I have attached here the work book to see how the duplicating happens. In the 3 May, I have added two blank rows whose customer name has been selected. Now, you can see in the Style Wise Summary, AWS-9036MN appears two times.

However, this can be sorted out by myself by not leaving any blank rows with the customer name selected. (attached the sheet)

I need a solution only now for the "Number of Days run".

This is my requirement
I would like "Number of Days Run" to be the number of "calendar days" the particular style has run in that "particular month". The reason, is I need to calculate the Loss and profit of the style "based on the number of days" that particular style has run in the particular month.

Accordingly, AWS-9036MN has run 10 days in the month of May. However, style wise summary shows a different value.

Also, I have noticed another issue in the column "W of" the "Style wise Summary".
Actually, in the column "Total Losses" should count the values in column "Z". The logic should be the same as columns K, M, N, O, P, Q etc..
Total losses doesn't mean the "cumulative losses" of all the styles..

Originally Posted by Anuru

Also, I have noticed another issue in the column "W of" the "Style wise Summary".
Actually, in the column "Total Losses" should count the values in column "Z". The logic should be the same as columns K, M, N, O, P, Q etc..
Total losses doesn't mean the "cumulative losses" of all the styles..

I saw, the columns J, L and T (average value columns) have the same issue. I meant "cumulative average" only of that style as per the number of days run. All averages should be only the average according to the number of days those styles were run in a particular line.

Updated: I added logic to remove "blank" lines from "Data" (as your data collection is so variable)

I will post code when you confirm it is OK.

I checked the work sheet, and seems ok as per my requirement.
However, still column W(total losses) of "style wise summary" not corrected. It should also follow the same logic as in columns M, N, O, P, Q etc...Pls re look at it...

You should be able to calculate with formula from columns U & V ????

Originally Posted by JohnTopley

You should be able to calculate with formula from columns U & V ????

No John, this column should also follow the logic of M, N, O, P Q etc (total values). A formula is not necessary, because the details are captured from daily data sheets (same as total production, total working hours etc). The Column Z is the target column from the daily sheets. Normally, this doesn't include losses daily basis, it include losses only if they occurs.

See attached updated:

Hi John,
I think now everything is working fine.
Just one more help is needed for the following req.

>> There is a column wise summary in every "daily sheet tab" in the row no 24 of every sheet. Also, there is a "Daily Summary" sheet tab which summarize this information in the row 24 of each sheet. Currently, I do this manually by copying required fields from row 24 to daily summary sheet as per the date. I transfer only the information highlighted in RED from row 24 to the daily summary sheet. There are some other columns that we have to manually fill, and formulas are not required for them.

Can you please provide me some formulas to do this?

TRY

in B2

=IFERROR(INDEX(INDIRECT("'" & TEXT($A3,"D")&"-" & TEXT($A3,"mmm") &"'!$A$24:$Z$24"),,MATCH('Daily Summary'!B$2,INDIRECT("'" & TEXT($A3,"D") & "-" & TEXT($A3,"mmm") &"'!$A$3:$Z$3"),0)),"")

Copy across and down

You have 2 "Profit and Loss" on the Daily sheets but only one on the Daily Summary.

NOTE: it is necessary for column headings in the Daily Sheets and the Daily Summary sheet to match.

So do you want "Profit / Loss Status (CM)" in the Daily Summary??

If so, change heading in "Daily Summary"

And change spelling of "Efficiency" w/book wide!

oK, wait pls..I will change and send it again...

No need to re-send: it the headings match it will pull the data.

Originally Posted by Anuru

oK, wait pls..I will change and send it again...

I have changed the heading to be similar in both the Daily summary and daily sheets. One more thing pls, I may have to insert new rows as per the requirement in the "daily sheets" (to increase the production lines), and the formula should not be affected by inserting new rows in to the daily sheet tabs..

Originally Posted by JohnTopley

You will need to add something the current line 24 which identifies it as the line of interest: Think before you keep changing your mind.

As for headings you need to do a lot of tiding up: e,g "CM / Dozen" vs "CM/ Dozen".

"Present OP" / "Present HP" are not in "Daily" sheets !!!!!!!

No problem I will do tidying up...

Current line/row 24 will be changed when I add rows above to increase lines. That is why I mentioned that the formula should be locked in the position not the row number, then adding rows will not affect.

Originally Posted by JohnTopley

TRY

in B2

=IFERROR(INDEX(INDIRECT("'" & TEXT($A3,"D")&"-" & TEXT($A3,"mmm") &"'!$A$24:$Z$24"),,MATCH('Daily Summary'!B$2,INDIRECT("'" & TEXT($A3,"D") & "-" & TEXT($A3,"mmm") &"'!$A$3:$Z$3"),0)),"")

Copy across and down

Hi john, everything works fine. Thanks a lot again for this great and brilliant work sheet.

So are you leaving it "fixed" on line 24?

I do understand the requirement: please give me some credit!

Obviously they don't appear as the formula is currently "fixed" on row 24.

I suggest you move those totals to row 4 and start the data in row 5. If not the formula is going to be somewhat more complex as we need to "match" the row starting from row 4 down.

Originally Posted by JohnTopley

I do understand the requirement: please give me some credit!

Obviously they don't appear as the formula is currently "fixed" on row 24.

I suggest you move those totals to row 4 and start the data in row 5. If not the formula is going to be somewhat more complex as we need to "match" the row starting from row 4 down.

I understand John. But moving summary totals to the beginning of the table looks a bit odd.

Put "Daily Summary" in column B i.e. currently row 24

in B3

=IFERROR(INDEX(INDIRECT("'" & TEXT($A3,"D")&"-" & TEXT($A3,"mmm") &"'!$A$4:$Z$100"),MATCH("Daily Summary",INDIRECT("'" & TEXT($A3,"D") & "-" & TEXT($A3,"mmm") &"'!$B$4:$B$100"),0),MATCH('Daily Summary'!B$2,INDIRECT("'" & TEXT($A3,"D") & "-" & TEXT($A3,"mmm") &"'!$A$3:$Z$3"),0)),"")

Originally Posted by JohnTopley

Put "Daily Summary" in column B i.e. currently row 24

in B3

=IFERROR(INDEX(INDIRECT("'" & TEXT($A3,"D")&"-" & TEXT($A3,"mmm") &"'!$A$4:$Z$100"),MATCH("Daily Summary",INDIRECT("'" & TEXT($A3,"D") & "-" & TEXT($A3,"mmm") &"'!$B$4:$B$100"),0),MATCH('Daily Summary'!B$2,INDIRECT("'" & TEXT($A3,"D") & "-" & TEXT($A3,"mmm") &"'!$A$3:$Z$3"),0)),"")

Thanks John. It worked and done.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi john,
I just had add one more piece of information("Garment Type)to the work book. It is highlighted in RED.
The logic for this information is also similar to "Style".
If you have time, pls do the necessary changes in the macro, and sorry for adding this again and disturbing you. I have attached the work book with
the added information.

Regards

Please check attached,

From Column "K" of the Style Wise Summary, and Column "T" of the "Data" sheet, I can see the values have been shifted(probably by one column). Needs re arranging..

See attached ....

The column shifting issue looks to be ok.

I just found a little problem in the "Style wise summary". In line no H2, "Days Run" should be 3, but it is showing 1. I found this error when put 1,2,3 etc to "Days run" in all sheet tabs for checking purpose. Pls check..I have highlighted this in red. Pls see the attached work book.

However, in the Data sheet, Days run for H2 line is showing correct...

Again see attached ...

I added sheet "Data Mapping" which shows correspondence between "Data" columns "Summary" columns

Originally Posted by JohnTopley

Again see attached ...

I added sheet "Data Mapping" which shows correspondence between "Data" columns "Summary" columns

It looks OK now.
By the way, what does the "Data Mapping" do? Does it help macro to identify the columns that should not be counted?

The "Data Mapping" is mainly for my benefit in case you have other amendments!

There an array (IDX) in the VBA which is effectively the mapping and it would be possible to incorporate the Data Mapping into the VBA but I have no plans to do so!