Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Date | Time | Completed | Name | ||
2 | 7/1/2022 | 9:45 | John Doe | |||
3 | 7/1/2022 | 9:45 | Jane Doe | |||
4 | 7/2/2022 | 11:00 | Mr. Excel | |||
Data |
Using the above example data in Excel 365, I want to populate a calendar-like sheet similar to the below example:
I am attempting to use the TEXTJOIN function to accomplish this but am running into multiple issues. The first issue is that the times will not pull over formatted correctly (i.e. 9:45 is pulled over as 0.40625) and the second is that I need to return the values per row as the example above shows and not per column (e.g., incorrect formatted times followed by names). An example array formula is below:
Excel Formula:
=CONCAT(TEXTJOIN(REPT(" ",5),TRUE,IF(Data!$A$2:$A$4=July!F3,July!$B$2:$B$4,"")),TEXTJOIN(CHAR(10),TRUE,IF(Data!$A$2:A4=July!F3,Data!$D$2:$D$4,"")))
I'm not proficient enough with VBA if that's what is required so am looking for some assistance. Thanks in advance.