• 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

bosco_yip

Excel Ninja
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

Last edited:

Tom2

New Member
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:

Tom2

New Member
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:

bosco_yip

Excel Ninja
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
 

Tom2

New Member
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!
 
Top