Showing datatable as a visual

Question

I have some data which includes a list of tasks for 2 individuals.

I want to represent this data in the following format.

So that it looks like the below.

How can I achieve this with the use of formulas?

[EDIT] I don't think this question is the same as 'Data Wrangling in Excel - Rearranging Columns and Rows' as mine is about reflecting schedules in a more gantt chart-esque like format, whereas the other question seems to be more about transposing data.

Answer 1

Not sure if you have your desired output on the same sheet or a new sheet, but I used a new sheet starting in cell A1. The source datasheet is referred to as S1 in the formulas.

Assumptions:

• Column C = Date Start
• Column D = Date End
• Persons' entries are grouped together (Person1, Person1, then Person2; never Person1, Person2, Person1)
• Person can't be assigned two different areas for the same timeframe (e.g., Person1 can't have A and B for a given month)
• Your desired dates are always the first of the month

Output/Formulas:

• A1 is blank
• A2: =INDEX('S1'!$A$1:$A$6,IFERROR(MATCH(A1,'S1'!$A$1:$A$6,0),1)+COUNTIF('S1'!$A$1:$A$6,A1))
• A3: Formula copied down from A2
• B1: =IF(A1<>"",EDATE(A1,1),MIN('S1'!$C$1:$D$6))
• C1 to J1: Formula copied from B1
• B2: =INDEX('S1'!$B$1:$B$6,MATCH(1,INDEX(('S1'!$A$1:$A$6=$A2)*('S1'!$C$1:$C$6<=B$1)*('S1'!$D$1:$D$6>=B$1),0),0))
• B3 and C2 to J3: Formula copied from B2

Explanations:

• The A2 formula: Looks at where the previous Person entry first occurs (MATCH part), and counts how many entries it has (COUNTIF part) to determine the next Person entry in line (INDEX part). The IFERROR portion is used to return the first Person entry, which is assumed to be on row 1.
• The B1 formula: Adds one month to the previous date heading (EDATE part). The very first date heading looks for the earliest date in your source data (MIN part).
• The B2 formula: Uses a match with multiple criteria on your source data (Person entry must match the indicated person, start date must be before or on the date heading, and end date must be after or on the date heading). The second INDEX creates an array of the combined criteria results (1 if true, 0 if false), and the MATCH returns which entry that is. The first INDEX returns the task (A, B, or C).