Creating a Date and Time Matrix

Problem:

Listed in column A are the dates and times of doctor's appointments. Column B contains the corresponding patient's name for each appointment.
We want to use this data to create a matrix in cells D1:G10, where each column is a date and each row is a time.

Solution:

Use the INDEX, MATCH, and TEXT functions as shown in the following Array formula:
{=INDEX($B$2:$B$18,MATCH(TEXT(E$1,"mmddyyyy")&TEXT($D2,"hh:mm"),TEXT($A$2:$A$18,"mmddyyyy")&TEXT($A$2:$A$18,"hh:mm"),0))}
Enter the above formula in cell E2, copy it down the column and across to column G.

To apply Array formula:
Select the cell, press and simultaneously press .

Comments

  1. "But is there a way to update the original worksheet data from the itemized list created from the Pivot Table drill down? In other words to link it somehow.

    It's nice and easy to check which records need updating through a pivot table, but a pain to go back to the original worksheet to update.

    Let me know!

    Scott"

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.