• 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.

Create report from the table by the format of the cells

herclau
hmm?
Your original know table was 62 x 65525.
Now, it's 94 columns and up to 65535 rows.
If use formulas then ... there should be ... many of those.
You can copy and paste Your 'the result of the saving of a software that collects in real time the status of a team' -data
to this file ( like Your original file )
and press [ Report ]-button.
 

Attachments

  • Report_needed.xlsb
    19.7 KB · Views: 3
vletm said:
herclau
hmm?
Your original know table was 62 x 65525.
Now, it's 94 columns and up to 65535 rows.
If use formulas then ... there should be ... many of those.
You can copy and paste Your 'the result of the saving of a software that collects in real time the status of a team' -data
to this file ( like Your original file )
and press [ Report ]-button.
Click to expand...
Hello
Here is a sample of the files generated by the Software.

The number of columns depends on the number of elements that are being observed. The row number can be up to 65536. If the test continues, the software generates a new file with the name of the previous file and adds the name "Part #"
Columns with a fixed name are "Fault" and "LastFault". That you have to locate them because they are not always in the same columns.

I had to delete rows. Since I receive the following alert from the Forum: "The uploaded file is too large."
 

Attachments

  • ST14 I37O43 .xlsb
    819.7 KB · Views: 3
vletm said:
herclau
hmm?
Your original know table was 62 x 65525.
Now, it's 94 columns and up to 65535 rows.
If use formulas then ... there should be ... many of those.
You can copy and paste Your 'the result of the saving of a software that collects in real time the status of a team' -data
to this file ( like Your original file )
and press [ Report ]-button.
Click to expand...
Please, please show in text the code that is executed with the report button. My server removes the macros or codes inside the excel file.!!!!
 
herclau
I tried to ask that You could test my file with Your data ... I don't need 'full data'.
I (tried to) test Your newer data ...
Do You know - how many In Fault -text are in Your sent files in Fault-column?
Those texts are keys for that report as You've written.
 
vletm said:
herclau
I tried to ask that You could test my file with Your data ... I don't need 'full data'.
I (tried to) test Your newer data ...
Do You know - how many In Fault -text are in Your sent files in Fault-column?
Those texts are keys for that report as You've written.
Click to expand...

I tried to run the macro in the last file that I attached. But I did not achieve any result in CV1.
FIXED .Cells (b, "B") = "In Fault" by .Cells (b, "AN") = "In Fault" which is where the Fault column is located
And .Cells (h, "A") by .Cells (h, "B") where is the Time column.
were these changes correct?

Knows? How many In Fault-text are in Your files sent in Fault-column?
This is not known until the analysis is done
In the attached file: ST14 I37O43 .xlsb. When deleting rows to make it possible to publish it, I eliminated, without noticing it, rows with the Fail column with IN-FAULT alerts.
it is possible to take groups of rows and in the column FAULT declare IN-FAULT several times in the file randomly and thus test the macro
 
Last edited:
herclau
Your #27- file has none In Fault -texts!
Now, You're writing something IN-FAULT ... hmm? The key In Fault is different than IN-FAULT!
You should send file which has useful data and You should also write - what is exact text which should trigger that!
Of course, You could do Yourself someway that data, which is like real data.
I updated that my sent code for You ... but there would need to do one more modification (which columns should check?)
 
vletm said:
herclau
Your #27- file has none In Fault -texts!
Now, You're writing something IN-FAULT ... hmm? The key In Fault is different than IN-FAULT!
You should send file which has useful data and You should also write - what is exact text which should trigger that!
Of course, You could do Yourself someway that data, which is like real data.
I updated that my sent code for You ... but there would need to do one more modification (which columns should check?)
Click to expand...
The idea is the same as that developed in the previous examples:
1. The type of fault declared in the LastFault column
2. Failure duration time
3. Compare the row of the initial time of the fault and the row immediately after the failure. To see the differences and report these. The heading of the column where the difference arises and the data of this in the row of the FAULT
 
It ran perfect!
How to achieve, what I have done filtering by colors. Differentiating fault intervals by colors. And calculating the duration time of the failure interval. And I only report the differences found in the first failure time of each interval.
 
I can't help feeling that you are trying to achieve too big a transformation without intermediate helper cells. There are two things going on. The computationally heavy step is filtering the data to include the datetime info from the first 'NoFault' along with the Report Labels from the final 'NoFault' recorded before returning the first record of the 'In Fault' batch. Just this filtering step would produce a useful intermediate table which would provide a sound basis for producing your final output. If, at a later date, you find you can eliminate this intermediate step, all to the good.

How that filtering is best achieved depends upon the version of Excel that you are using. If you are using Office 365 the worksheet function FILTER will soon be available which greatly simplifies that step. If you are using a paid up version of Excel then you need to use multiple functions, with some solutions not scaling well with problem size. Any step that involves searching the entire dataset for each output record should be a source of concern. If possible, accumulate steering data within a helper range as you have done in tbleje.help. LOOKUP or approximate MATCH are efficient functions operating over such data. SMALL or exact MATCHes are likely to prove expensive.

Something else that might affect your decisions, is the use-case for the data. If it is a one-off calculation of an imported dataset then it takes the time it does. If you are going to append new data to an existing dataset, then it is important to avoid volatile functions, including OFFSET. Those will force a complete recalculation whenever anything is changed or added. OFFSET can usually be replaced by the non-volatile function INDEX, here by subtracting 1 from the record number (another useful helper range).
 
p45cal said:
Which columns (apart from the Time, Fault and the additional 'duration' column) in this file do you need to show in the results?
Click to expand...
And a column with the Fault count that exists in the time interval
[Time] [duration] [Count Faults] and The heading of the column where the difference arises and the data of this in the row of the FAULT
 
herclau said:
And a column with the Fault count that exists in the time interval
[Time] [duration] [Count Faults] and The heading of the column where the difference arises and the data of this in the row of the FAULT
Click to expand...
OK, I'll add a count column too. But do you want all the other columns (C:AM, AO:CO) to be candidates for possible inclusion in the results?
 
This workbook explores the functionality that comes with Office 365. It is early days yet, so I have little idea whether it is the best solution.

61453

The table has a couple of helper columns that I have used to filter the timing information on the left and the state information on the right. Once the state data has been brought together in the grey-shaded helper range, FILTER is reapplied, this time horizontally row-by-row, to compact the state changes into the output table. There are no whole table searches so I believe the solution should scale linearly with data size.

I understand dynamic arrays have now started rolling out to Office 365 monthly updates, so not too long to wait for some! :)
 

Attachments

  • Dynamic Ranges v5A (PB).xlsx
    23.6 KB · Views: 6
While awaiting your response to my last question I've got a few questions.
Whilst testing I'm getting these results (I progressively removed the topmost rows of the source data):
61455

1. In all 4 results you might want one extra line at the bottom 2211 rows of No-Fault rows?
2. The original full list result is correct?
3. The starts with 27 in-faults at the top result shows information about those 27 rows. There being no 'No-Fault' row above them to compare with there are no results for this. Do you want this line? If so, do you want the first time too?
4. In the one no-fault at top results I've highlighted in red the zero value for the duration. This cell is zero because in your sample results you subtract the time of the first In-Fault row in a block from the last In-Fault row of the same block. You do the same with the No-Fault rows. If there is only one In-Fault row the first and last In-Fault row is the same row. This will happen regardless of whether the single In-Fault row is at the top,in the middle or at the bottom of your source data. This means that if you add all the durations together the result is different from the difference between the top and bottom rows of your source data. I'm guessing, but maybe you don't want this? I'd suggest working out the durations by subtracting the first row of a block from the first row of the next block?
 
p45cal said:
While awaiting your response to my last question I've got a few questions.
Whilst testing I'm getting these results (I progressively removed the topmost rows of the source data):
View attachment 61455

1. In all 4 results you might want one extra line at the bottom 2211 rows of No-Fault rows?
2. The original full list result is correct?
3. The starts with 27 in-faults at the top result shows information about those 27 rows. There being no 'No-Fault' row above them to compare with there are no results for this. Do you want this line? If so, do you want the first time too?
4. In the one no-fault at top results I've highlighted in red the zero value for the duration. This cell is zero because in your sample results you subtract the time of the first In-Fault row in a block from the last In-Fault row of the same block. You do the same with the No-Fault rows. If there is only one In-Fault row the first and last In-Fault row is the same row. This will happen regardless of whether the single In-Fault row is at the top,in the middle or at the bottom of your source data. This means that if you add all the durations together the result is different from the difference between the top and bottom rows of your source data. I'm guessing, but maybe you don't want this? I'd suggest working out the durations by subtracting the first row of a block from the first row of the next block?
Click to expand...

OK, I'm going to add an account column too. But do you want all the other columns (C: AM, AO: CO) to be candidates for possible inclusion in the results?
It would be prudent. But only at this time would it include the "SubFaults" column. And the Faults count
In the file that I attach in the second page I show, how would the report be.
 

Attachments

  • ML33AB.xlsb
    385.7 KB · Views: 11
Last edited:
Peter Bartholomew said:
This workbook explores the functionality that comes with Office 365. It is early days yet, so I have little idea whether it is the best solution.

View attachment 61453

The table has a couple of helper columns that I have used to filter the timing information on the left and the state information on the right. Once the state data has been brought together in the grey-shaded helper range, FILTER is reapplied, this time horizontally row-by-row, to compact the state changes into the output table. There are no whole table searches so I believe the solution should scale linearly with data size.

I understand dynamic arrays have now started rolling out to Office 365 monthly updates, so not too long to wait for some! :)
Click to expand...
In the Office I work with the FILTER function, they look like this: = _xlfn._xlws.FILTER (timing, tbl [StateChange]).
FILTER is not in the version of this Office.
When trying to use = _xlfn._xlws.FILTER (timing, tbl [StateChange]), a cell different to the work areas created by you, I get the following output: #NAME?
For me, curiously, the workbook works!
What is office 365? Introduce me, please.
 
Office 365 is the yearly subscription version of Excel. Whereas in Office 2010-2019 the functionality remains as it was when the version was released, Office 365 is updated regularly (the user opts for half-yearly, monthly etc.) For me, the important change that is just starting to be released is dynamic arrays. With dynamic arrays any cell can hold an array value as the result of a calculation; equally, any formula that references an array is treated as an array formula without needing CSE in order to override the normal implicit intersection step.

This allows new functions that return arrays to be introduced; one such is FILTER which, by definition, returns an array of size from zero up to the size of the original. Without this, one needs a CSE formula that: identifies the record number of matching entries; uses SMALL to compact the list; and INDEX to return the data. This turns a simple operation into a drama. Without this function, I would most likely use Advanced Filter / Extract but this introduces a manual step into the data processing.
 
Last edited:
herclau
You skipped my questions...
Your sent file has much more lines than mine code.
... but there is a challenge so far, because You didn't give answers!

Here is a sample report, featuring Yours the newest version, from ML33AB-file.
 

Attachments

  • ML33AB.pdf
    22.9 KB · Views: 3
Last edited:
vletm said:
herclau
You skipped my questions...
Your sent file has much more lines than mine code.
... but there is a challenge so far, because You didn't give answers!

Here is a sample report, featuring Yours the newest version, from ML33AB-file.
Click to expand...
Please:
What question have I jumped?
Yes, the great thing about your coding is all the code lines save!
 
herclau said:
In the file that I attach in the second page I show, how would the report be.
Click to expand...
There seem to be two tables on that second sheet (Faults found). Am I right that it's the table at cell A1 plus the column differences of all the other columns in the source data?
 
You must log in or register to reply here.
Back
Top