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

Re-organize Data Matrix

Tom2

New Member
Hello Excel Experts,

I'm a newbie in Excel. Like to get some advice about how to use formulas to reorganize a data matrix into a new format. Please find attached raw data and expected results. Thank you very much for your time.

Results:
63612
 

Attachments

  • data.xlsx
    10.6 KB · Views: 10
A pivot table could be used.
Otherwise dynamic arrays could be used in Office 365.
 

Attachments

  • Pivot table (PB)..xlsx
    22.2 KB · Views: 8
Last edited:
Try..................

1] For the column of "Parient ID", "Durg Name" and "Days' Supply" using the Excel build-in function as in :

Select A2:C19 >>Copy>> Select H13 >> Paste >> Remove Duplicates >> uncheck "My data has headers" >>OK

63619

Then,

2] In "Total Fill Days' Supply" K13, formula copied down :
=J13*N13

3] In "First Fill Date" L13, formula copied down :
=AGGREGATE(15,6,D$2:D$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)

4] In "Last Fill Date" M13, formula copied down :
=AGGREGATE(14,6,D$2:D$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)

5] In "# Fills" N13, formula copied down :
=COUNTIFS(A:A,H13,B:B,I13)

Regards
Bosco
 

Attachments

  • data (BY).xlsx
    32.1 KB · Views: 7
Last edited:
A pivot table could be used.
Otherwise dynamic arrays could be used in Office 365.

Hello Peter,

Thank you very much for your help. The formula-based solution looks great! Unfortunately I'm using Excel 2016 which doesn't have the function you use. Could you please show me how to implement the formula-based solution in Excel 2016 if possible? I really appreciate it!
 
Last edited:
Try..................

1] For the column of "Parient ID", "Durg Name" and "Days' Supply" using the Excel build-in function as in :

Select A2:C19 >>Copy>> Select H13 >> Paste >> Remove Duplicates >> uncheck "My data has headers" >>OK

View attachment 63619

Then,

2] In "Total Fill Days' Supply" K13, formula copied down :
=J13*N13

3] In "First Fill Date" L13, formula copied down :
=AGGREGATE(15,6,D$2:D$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)

4] In "Last Fill Date" M13, formula copied down :
=AGGREGATE(14,6,D$2:D$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)

5] In "# Fills" N13, formula copied down :
=COUNTIFS(A:A,H13,B:B,I13)

Regards
Bosco

Hello Bosco,

I really appreciate your help. Just wonder whether it's possible to apply formula to the column of "Parient ID", "Durg Name" and "Days' Supply" as well? BTW, I'm using Excel 2016. It's a good chance for me to learn.

63626
 
Last edited:
Hello Bosco,

I really appreciate your help. Just wonder whether it's possible to apply formula to the column of "Parient ID", "Durg Name" and "Days' Supply" as well? BTW, I'm using Excel 2016. It's a good chance for me to learn.

View attachment 63626
Formula solution to the column of "Parient ID", "Durg Name" and "Days' Supply"

63630

1] For the columns of "Parient ID" and "Durg Name"

In H13, formula copied right to I13 and all copied down :

=IFERROR(INDEX(A$2:A$19,AGGREGATE(15,6,ROW($A$1:$A$19)/(MATCH($A$2:$A$19&$B$2:$B$19,$A$2:$A$19&$B$2:$B$19,0)=ROW($A$1:$A$19)),ROWS($1:1))),"")

2] In "Days' Supply" J13, formula copied down :

=AGGREGATE(15,6,C$2:C$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)

Regards
Bosco
 
Formula solution to the column of "Parient ID", "Durg Name" and "Days' Supply"

View attachment 63630

1] For the columns of "Parient ID" and "Durg Name"

In H13, formula copied right to I13 and all copied down :

=IFERROR(INDEX(A$2:A$19,AGGREGATE(15,6,ROW($A$1:$A$19)/(MATCH($A$2:$A$19&$B$2:$B$19,$A$2:$A$19&$B$2:$B$19,0)=ROW($A$1:$A$19)),ROWS($1:1))),"")

2] In "Days' Supply" J13, formula copied down :

=AGGREGATE(15,6,C$2:C$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)

Regards
Bosco

Thanks a lot!
 
Back
Top