Hi,
I would like to understand how to summarise and group detail rows, which can have multiple statuses and rank the statuses so only the most relevant status is shown.
As an example:
Row 1 ProductA Planning
Row 2 ProductA Design
Row 3 ProductA Ready
Row 4 ProductB Design
Row 5 ProductB
Row 6 ProductB Planning
should be summarised as
Group1 ProductA Ready
Group2 ProductB Design
I've attached a sample file which hopefully makes the ask clearer...
Thanks
Hi,
A helper column in the Detail table that holds the Status Rank using an INDEX(MATCH()) formula will probably needed, and then a Pivot Table - probably for analysing it.
However you'll need to clearly explain how your required results relate to the original data. Manually Add exactly what you want to see and explain the rules you've used to obtain the results. For instance if the Summary expected in B2:D7 is exactly what you want, explain how D3 gets the status of Ready. Is the rule that you take the lowest status ranking for any Product from the Detail table and apply that Status description?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard,
If you open the attachment in the original post, I have included the expected results on the second tab.
You need to look at the 'Status Ranking' in A12:B16 of this tab in order to understand how the summarisation derives the correct status.
In the example posted above, you want to get the latest status in the delivery process, so you'd assume a ranking of:
3. Planning
2. Design
1. Ready
Therefore, ProductA = Ready, and ProductB = Design.
The spreadsheet includes some statuses with equal ranking. These groupings would be returned as "Conflict".
Does this help clarify?
Hi,
I did open the workbook. It certainly wasn't clear that the 'latest' status which you now mentions was the status with the lowest number, hence my surmise as to the rules.
The attached seems to give you what you want if you include the two Helper columns
Thank you for your response. The suggestions look very promising.
However, I've noticed that if the core values change, the derived values in the helper columns do not necessarily have the expected results.
For example, if the statuses of D10:D12 were to change so that they trigger the Conflict rule, Status2 doesn't update to reflect that they have conflicting statuses.
Please try at
C3
=UNIQUE(Detail!C3:C14)
D3 for only Confilct with Ready and Not Needed.
=IF(MMULT(COUNTIFS(Detail!C3:C14,C3#,Detail!D3:D14,TRANSPOSE(B12:B13)),{1;1})>1,"Confilct",XLOOKUP(C3#,Detail!C3:C14,Detail!D3:D14,,,-1)&"")
or with any conflict
=IF(ISNA(MODE.MULT(TRANSPOSE(FILTER(XLOOKUP(Detail!$D$3:$D$14,$B$12:$B$16,$A$12:$A$16,""),Detail!$C$3:$C$14=C3)))),VLOOKUP(C3,SORTBY(Detail!$C$3:$D$14,XLOOKUP(Detail!$D$3:$D$14,$B$12:$B$16,$A$12:$A$16,"")),2,0)&"","Confict")
Wow, that's so complex I can't actually figure out what's happening enough to troubleshoot it when I transcribe it.
I've sanitised the target workbook and attached it. When I transcribe, I'm getting a #VALUE error, but can't trace what the cause is. Can you spot it?
Try
=IF(MMULT(COUNTIFS(Detail!B5:B2397,C5#,Detail!F5:F2397,TRANSPOSE('Cover Sheet'!$L$2:$L$3)),{1;1})>1,"Conflict",XLOOKUP(C5#,Detail!B5:B2397,Detail!F5:F2397,,,-1)&"")
Thank you, that's resolved the error so that it returns values. However, the results returned are often not consistent with the values in the detail tab. For instance, returning null values when there is a value to return, or showing 'Conflict' when there is no conflict trigger.
Thoughts?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks