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

Customers Breakdown based on Items Purchased

Juniad

Active Member
Dear fellows,
Kindly attached the sample sheet where I want the breakdown of customers based on Items they purchased.. Example if Customer 1 (C1) purchased items1 in 3 quantity then it should be expand in 3 rows and so on. Sample sheet attached for better understanding .
In Excel 2010
Thanks in Advance
 

Attachments

  • Breakdown of Customer based on Items purchased.xlsx
    9.3 KB · Views: 9
1] In F3, formula copied down :

=IFERROR(INDEX(B$2:D$2,AGGREGATE(15,6,COLUMN($1:$1)/(MMULT({1,1},--(B$3:D$4))>=ROW($1:$30)),ROW(A1))),"")

2] In G3, formula copied down :

=IF(F3="","",F3&TEXT(COUNTIF(F$3:F3,F3),"-000"))

3] In H3, formula copied down :

=IFERROR(INDEX(A$3:A$4,AGGREGATE(15,6,ROW(A$1:A$100)/(MMULT(--(B$3:D$4),{1;1;1})>=COLUMN($1:$1)),ROW(A1))),"")


75608
 

Attachments

  • Breakdown of Customer based on Items purchased.xlsx
    12.5 KB · Views: 5
Last edited:
There's also a Get & Transform Data method (aka Power Query). In the attached right-click on the table at cell F18 and choose Refresh after updating the values in the table at cell A2.
 

Attachments

  • Chandoo46708Breakdown of Customer based on Items purchased.xlsx
    20.2 KB · Views: 1
Back
Top