Replicate Pivot Table Kind of Report by Functions
Hello Everyone,
In the attached sample file I have a Tab with data and another "Report".
I know this kind of report could be easily generated by Pivot tables. But for some reason the end user is requesting this to be generated by Excel Functions alone.
The first column in the report should have distinct entries only and then the second column to have the related distinct entries from the code column.
Any help is appreciated.
Last edited by ibuhary; 02-02-2019 at 01:30 PM.
I think you forgot to attach the file.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.
Hope this helps.
Pete
Attachment Done
Is it correct that some 'Codes' apply to more than one 'Item'? As in code 36 applies items 101 and 115.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Yes, that is correct and that was the fact what drives this process a difficult one.
This proposal employs four helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper combines item and code using: =DATA!A2&"/"&DATA!D2
The second helper lists unique item/code combinations using:Formula:
Please Login or Register to view this content.
The third helper separates the codes using: =IFERROR(RIGHT(G2,LEN(G2)-SEARCH("/",G2)),"")
The fourth helper separates the item using: =IFERROR(VALUE(LEFT(G2,SEARCH("/",G2)-1)),"")
The Item column of the report is populated using: =IFERROR(SMALL(I$2:I$77,ROW(1:1)),"")
Note that duplicate item numbers are hidden using conditional formatting.
The Code column is populated using: =INDEX(H$2:H$77,AGGREGATE(15,6,(ROW($2:$77)-1)/(I$2:I$77=L2),COUNTIFS(L$2:L2,L2)))
The Sum of B/A columns are populated using: =IF($L2="","",SUMIFS(DATA!B$2:B$77,DATA!$D$2:$D$77,$M2,DATA!$A$2:$A$77,$L2))
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks