• 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

Member
I have given the cells of the table using Column difference. Grouping the rows two by two.
I need to make a report that is shown in the attached file.
Any ideas?
Gracias
 

Attachments

herclau

Member
herclau
Would there be clear logic of report?
Or would next be A's value?
.. and after that 'only' time?
I have software that generates an excel sheet of 62 columns and up to 65525 rows.
In one of the columns the occurrence of failures (In-Fault / No-fault) is reported. I search in that row where I reported the failure column time and marked the previous row to this time and the time row of the error, With the help of the command differences of columns in the menu to go to special, I frame the differences.I need to make a report of these differences found in the columns and show the header of the same.

In the picture I show a simplified example of the source table and the report to be made:
60909
 

herclau

Member
herclau
You sent a snapshot from that file ... nothing new.
Did You answer any of my three questions? --- no?
When in-Fault appears in the Fault column, I select the previous row and the in-Fault row. with two rows selected. Execute the command: "Column diferences" points out the differences that exist. the cells selected as different will change the background color.
And with these signs pointed out I wish to make a report of the differences found in the columns of the row in which is in-fault
 

Attachments

vletm

Excel Ninja
herclau
Press Cell H1's [ Report ]-button to get You report with Your sample file!
As Your sample file seems not be as Your normal used file ... then You've made a new challenge for You.
 

Attachments

herclau

Member
herclau
Press Cell H1's [ Report ]-button to get You report with Your sample file!
As Your sample file seems not be as Your normal used file ... then You've made a new challenge for You.
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. And try to log me through the cell phone to download the file and do not recognize the password
Or you can send me the file to this address herozigle@gmail.com
Sorry for the inconvenience that I may cause to you.
 

vletm

Excel Ninja
herclau
A code is in that file. You can get it from there.
What kind of server do You have? Isn't that dangerous?
The cell phone ... You cannot use that file with the cell phone.
Go somewhere normal place to use normal PC and download it ...
 

herclau

Member
herclau
A code is in that file. You can get it from there.
What kind of server do You have? Isn't that dangerous?
The cell phone ... You cannot use that file with the cell phone.
Go somewhere normal place to use normal PC and download it ...
I am in my work center. When I download the file it is scanned by the security system and removes the errors and codes: and I receive a file with the following name:
Report_needed.cleaned.xlsb
I'll wait to get home.
Thank you
 

Peter Bartholomew

Well-Known Member
I have used formulas that test their row location to determine whether it is the header or the code that is to be reported. The whole thing is built from Names which, by their nature, hold (array) formula fragments which are nested within other formula as Excel calculates.

60921
To be honest, I haven't found many developers that use such techniques but in my experience they work well and are easy to maintain.
 

Attachments

herclau

Member
o be honest, I haven't found many developers that use such techniques but in my experience they work well and are easy to maintain.
I would like to delve into the technique used here. You could indicate some sites where you can see other examples or where they explain this technique.
Thank you
 

GraH - Guido

Well-Known Member
No-one can explain it better the Peter himself. Just read some (all?) of his threads over here. Be aware he does rattle the cage from time to time.

Here is my futile attempt.
The technique is rather simple, and it is used by many Excel developers, but rarely in such a disciplined way as Peter does. Basically one is replacing helper cells/columns/tables with the same formulas used in the name manager. As Peter said the advantage of using the name manager is that array calculations do not need a particular manipulation like confirming with CTRL+SHIFT+ENTER. Second advantage: your end formulae become "readable", kind of a natural language like offered by table structured references. Third advantage is you replace formulae arguments with (dynamic) variables.

Documenting these formulae as Peter does, is key for awareness and maintenance purposes. Knowing the formulae are kind of tucked away in the name manager, users who do not know this feature, can easily get lost by these and might have the tendency of "knowing better" and reverting back to A1-style references all together. (At this point Peter nearly died of a heart attack). If they start to update one of the end formulae visible on the sheet, trouble^3 is what you'd get. (The user just killed Peter. Poor Peter...)
 

vletm

Excel Ninja
As herclau has written...
I have software that generates an excel sheet of 62 columns and up to 65525 rows.
~4062550 cells seems to need formulas.
 

Peter Bartholomew

Well-Known Member
Guido
I love it; I should hire you as a publicist!

herclau
I am at a bit of a loss to find suitable material. As Guido says, my posts to this site tend to provide examples of the same approach. Hui might be able to point to relevant podcasts by Daniel Ferry who published insightful material when he was active (I remember one that pointed out that Names are simply references to array formulas).

I have published a paper to EuSpRIG (https://arxiv.org/ftp/arxiv/papers/1704/1704.01142.pdf) but it is not exactly follow along step-by-step training. Again Craig Hatmaker has described some of the methods I use
(https://sites.google.com/site/beyondexcel/project-updates/multi-cellarraymodelling)
and he also has interesting things to say on Names and transparency. I have published a series of articles (one attached) aimed at an engineering audience who will use matrix methods day in day out as part of their job but still revert to traditional spreadsheet development techniques when it comes to Excel.

The interesting thing is that some team at MS put a lot of effort into developing Names but with remarkably little impact on the userbase at large. I remind myself that I am merely following in their footsteps as a user.

As for practicing with the current spreadsheet, the formulas are restricted to row-relative arrays (in order to apply SMALL or FILTER on a row-by-row basis), so any formula may be entered in-line with your table and a name may be given to the array of four cells. That name may be used to reference the helper range to build downstream formulas. If the initial formula is instead entered into the refers to box in Name Manager in place of the range references then the downstream formulas still work. The formula is recalculated whenever the Name in encountered, nested within another formula.
 

Attachments

Hui

Excel Ninja
Staff member
herclau
I am at a bit of a loss to find suitable material. As Guido says, my posts to this site tend to provide examples of the same approach. Hui might be able to point to relevant podcasts by Daniel Ferry who published insightful material when he was active (I remember one that pointed out that Names are simply references to array formulas).
Daniel taught that Names are actually more correctly Named Formulae and that all Named Formulae are Array Formula

ie: A100 is actually a Named Formula referencing some memory that has a value or formula that you associate to a logical grid position of A100

Daniel went into great lengths in the Excel Hero Academy to expand on that.
The Module on Names was the first module published
You may still be able to enrol in the EHA, but beware that Daniel never did complete the course
Although what he does teach is far and beyond anything else available

The last enrollee that I am aware of was in March 2018, but there maybe more after that
 

herclau

Member
Daniel taught that Names are actually more correctly Named Formulae and that all Named Formulae are Array Formula

ie: A100 is actually a Named Formula referencing some memory that has a value or formula that you associate to a logical grid position of A100

Daniel went into great lengths in the Excel Hero Academy to expand on that.
The Module on Names was the first module published
You may still be able to enrol in the EHA, but beware that Daniel never did complete the course
Although what he does teach is far and beyond anything else available

The last enrollee that I am aware of was in March 2018, but there maybe more after that
Hi Hui,
I have been looking for examples where the technique used by Peter to solve my question is shown. But without excitement!
Could you direct me to some other link within this forum where this technique was used?
 

Hui

Excel Ninja
Staff member
Hi Hui,
I have been looking for examples where the technique used by Peter to solve my question is shown. But without excitement!
Could you direct me to some other link within this forum where this technique was used?
I would look at Peter's example and work through the data flow that you need, see where/how it is handled in Peters model

There are times where Names don't appear as simple as maybe you think it should be
But the logic of what peter Has setup is flexible and extensible in that it allows you to add new names as the data grows
 

p45cal

Well-Known Member
A macro solution. In the attached there's this macro which will run on the click of the button at cell M1. It puts the results in columns N:P.
I suspect your real table is larger and has the In Fault/No Fault column elsewhere so this macro will need a tweak.
Code:
Sub blah()
Set Destn = Range("N2")    'Where the first result will go.
Set myRng = Range("A1").CurrentRegion
For rw = 2 To myRng.Rows.Count
  If myRng.Cells(rw, "B").Value = "In Fault" Then
    Set zzz = myRng.Rows(rw - 1).Resize(2).ColumnDifferences(myRng.Cells(rw - 1, 1))
    Intersect(Range("A:A,C:F"), Union(zzz.Offset(-rw + 1), zzz)).Copy Destn
    Set Destn = Destn.Offset(2)
  End If
Next rw
Destn.Resize(, 3).EntireColumn.AutoFit
End Sub
 

Attachments

Last edited:

herclau

Member
I would look at Peter's example and work through the data flow that you need, see where/how it is handled in Peters model

There are times where Names don't appear as simple as maybe you think it should be
But the logic of what peter Has setup is flexible and extensible in that it allows you to add new names as the data grows
I have based my search on the Internet, to locate Peter Bartholomew's method, with the following search headlines:
The use of the name manager.
Use of formula arguments with (dynamic) variables.
Referenced structure.
arrays of nested formulas.
Use of Name of ranges to avoid VBA.
Multi-Cell Array Modeling.
Formulas without a cell references.
Use of names in the formulas;

But I have hardly had success!

The most repeated is:
And within the Forum I have only been able to find these three implementations of the method:

Extracting Multiple Criteria (PB) .xlsx

FilteredData (PB) .xlsx
sales person (PB) .xlsx
These two examples I can not find them again !!

My question:
What would be the correct statement to have more access to the method within the Internet and the Forum?
 

Peter Bartholomew

Well-Known Member
@herclau
This is meant to be a simple example that may help you interpret what is going on in the solution I offered to your problem.
The task is to calculate the geometric mean of corresponding values in two arrays A and B. There is a worksheet function for geometric means (GEOMEAN) but it will take all eight values and aggregate to give a single result.

First I used 'Formulas / Create from Selection' to generate formulas A and B that reference the two arrays. The column P contains the formula
{= A * B}

61059

Having generated the pairwise products, I introduced the name 'P' to reference the result. The product is then square-rooted to give the array of geometric means '√P' using the formula
{= SQRT(P)}

In order to remove the helper range 'P', I can redefine the name P to hold the formula
= SQRT(P)
by cutting and pasting it from the worksheet into the 'Refers to' box. Once that is done, the values in column P no longer have dependents and may be deleted. To evaluate the values √P Excel will first invoke the formula P (you can see this using 'Evaluate Formula') and 'Stepping In' allows you to
follow its definition and subsequent evaluation in terms of A and B.

Why do I do this? I use named arrays because it is at this level I am likely to be able to attribute significance to the data in terms of the application domain. The location of the data on a worksheet is arbitrary and it is an irrelevant attribute of the date when it comes to calculation.
How common is the approach? You can get a feel for that from a comment made by Mike (excelisfun) Girvin:
" I have never, in almost 3 decades, seen a spreadsheet like yours with ALL the calculations entered as arrays!!!
Now I see why your are so excited by the new Excel Calculation Engine
"
What are the limitations? If the data is record-based, with data being constantly added, the incremental calculation offered by Tables and Pivot Tables would be far more appropriate and efficient. The methods I use are more suited to compact, calculation-heavy models where it is likely that a data change would require a complete reevaluation of the problem.
 

Attachments

herclau

Member
Hello,
Using the technique shown by Peter Bartholomew and also with the help of GraH - Guido I have my first approach to the problem of reporting my table of 94 columns and up to 65535 rows.
The tables shown here have been a simplification of the problem.

In this first attempt some concerns arise:
1. Are not the functions very nested, in my case? I will not have problems when handling the "Great" Table.
2. Using the mixed or combined references ($ A1, or L $ 1) to index the rows or column has happened to me, that at times, I have skipped the indices to values outside of my analysis table. For example: $ A10345. What has ruined the results. And I have managed to identify the problem with the help of F3 (Paste Name / Paste List). And in the references shown of the names I have seen the error!
3. With the indirect function I can make indirect references to cells. But there is some way to assign values to a cell indirectly. (if A1 = 10, C1 = 1, C1 = C1 + 1)
4. In the comments I have indicated with asterisks and in red the functions or names that I think it is possible to improve, especially when thinking about the time to build the template.

61412
Gracias
 

Attachments

herclau

Member
herclau
As written #13 Reply,
~4062550 cells seems to need formulas ... or more - hmm?
... but it's Your choice.
Hello
The cells of the report are the result of the saving of a software that collects in real time the status of a team. As far as the heading is concerned, everything is text or numbers.
????
 
Top