Create list of non-empty cells with corresponding Date and Category

[Danielneedshelp]

Hi everyone,

I have a range of cells where I manually enter names at the intersection of a certain date and category. I'd like to automatically make a list in one column of every name I've entered while ignoring empty cells and have adjacent columns list the corresponding date and category.
Here is a simplified version of where I would manually input the names:

And here is the corresponding desired output I'd like to achieve (I've done this manually, but looking to automate it):

In the case I'd input more names, I'd like for the name to be added to the output list with the corresponding date and category. The order doesn't matter so long as each row has the correct corresponding date and category.

A few precisions:

• I can't use VBA
• Some names will repeat
• I'd prefer to output to another sheet
• I'm using Excel 365

Any help would be very much appreciated as this seems so simple in my head but I cant get it to work.
Thanks so much.

 

[Georgiboy]

If you convert your range to a table and add the heading "Category" you could use the below, it will update if you add in more dates, categories and names:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Category	21/08/2023	22/08/2023	23/08/2023	24/08/2023	25/08/2024			Date	Name	Cat
2	CAT1	Betty		Arnold	Betty				21/08/2023	Betty	CAT1
3	CAT2	Susan			Arnold				21/08/2023	Susan	CAT2
4	CAT3			Jim		Sam			21/08/2023	Dave	CAT5
5	CAT4			Connie					23/08/2023	Arnold	CAT1
6	CAT5	Dave							23/08/2023	Jim	CAT3
7	CAT6					George			23/08/2023	Connie	CAT4
8									24/08/2023	Betty	CAT1
9									24/08/2023	Arnold	CAT2
10									25/08/2024	Sam	CAT3
11									25/08/2024	George	CAT6
Sheet1

Cell Formulas
Range	Formula
I2:K11	I2	=LET( d,TOCOL(Table1[#Headers]&","&Table1&","&Table1[Category]), t,FILTER(d,(NOT(ISNUMBER(FIND(",,",d))))*(LEFT(d,8)<>"Category")), xml,"<t><s>"&SUBSTITUTE(t,",","</s><s>")&"</s></t>", SORT(FILTERXML(xml,"//s["&SEQUENCE(,3)&"]"),1,1))
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F
1	Category	21/08/2023	22/08/2023	23/08/2023	24/08/2023	25/08/2024
2	CAT1	Betty		Arnold	Betty
3	CAT2	Susan			Arnold
4	CAT3			Jim		Sam
5	CAT4			Connie
6	CAT5	Dave
7	CAT6					George
8
Sheet1

Fluff.xlsm
	A	B	C
1	Date	Name	Category
2	21/08/2023	Betty	CAT1
3	23/08/2023	Arnold	CAT1
4	24/08/2023	Betty	CAT1
5	21/08/2023	Susan	CAT2
6	24/08/2023	Arnold	CAT2
7	23/08/2023	Jim	CAT3
8	25/08/2024	Sam	CAT3
9	23/08/2023	Connie	CAT4
10	21/08/2023	Dave	CAT5
11	25/08/2024	George	CAT6
12
Sheet2

Cell Formulas
Range	Formula
A1:C11	A1	=LET(d,Sheet1!B2:F20,VSTACK({"Date","Name","Category"},HSTACK(TOCOL(IF(d<>"",Sheet1!B1:F1,1/0),2),TOCOL(d,1),TOCOL(IF(d>"",Sheet1!A2:A20,1/0),2))))
Dynamic array formulas.

 

[Danielneedshelp]

If you convert your range to a table and add the heading "Category" you could use the below, it will update if you add in more dates, categories and names:

Hi & welcome to MrExcel.
Another option

Thanks so much for your answers, both of you.
I tried Fluff's option first as I'm not sure what converting the range to a table in my actual document might limit down the line.
Which works great!
Now I'm just wondering if my Name and Category ranges could be non-contiguous as follows:

How would you go about that situation?
Thanks again for your great help so far!

 

[Fluff]

How about

=LET(d,VSTACK(Sheet1!A2:F7,Sheet1!A10:F11,Sheet1!A14:F14,Sheet1!A16:F16),e,DROP(d,,1),VSTACK({"Date","Name","Category"},HSTACK(TOCOL(IF(e<>"",Sheet1!B1:F1,1/0),2),TOCOL(e,1),TOCOL(IF(e<>"",INDEX(d,,1),1/0),2))))

 

[Danielneedshelp]

How about

=LET(d,VSTACK(Sheet1!A2:F7,Sheet1!A10:F11,Sheet1!A14:F14,Sheet1!A16:F16),e,DROP(d,,1),VSTACK({"Date","Name","Category"},HSTACK(TOCOL(IF(e<>"",Sheet1!B1:F1,1/0),2),TOCOL(e,1),TOCOL(IF(e<>"",INDEX(d,,1),1/0),2))))

Hmm, I'm not getting this second one to work.
I had to advance on my actual document to test this, apologies for the delay.
I decided to keep your first suggestion Fluff and thought I'd just reference the original cells below in the depths of the document. That means the data is a formula though (i.e.: =IF(D25<>"",D25,"") )
And now I'm not getting your first one to work either.

=LET(d,CONSOLIDATED!D200:UW259,VSTACK({"Date","Brand","Type"},HSTACK(TOCOL(IF(d<>"",CONSOLIDATED!D1:UW1,1/0),2),TOCOL(d,1),TOCOL(IF(d>"",CONSOLIDATED!C200:C259,1/0),2))))

Any idea what I did wrong?

 

[Fluff]

As the cells are not empty try

=LET(d,CONSOLIDATED!D200:UW259,VSTACK({"Date","Brand","Type"},HSTACK(TOCOL(IF(d<>"",CONSOLIDATED!D1:UW1,1/0),2),TOCOL(IF(d<>"",d,1/0),2),TOCOL(IF(d>"",CONSOLIDATED!C200:C259,1/0),2))))

 

[Danielneedshelp]

As the cells are not empty try

=LET(d,CONSOLIDATED!D200:UW259,VSTACK({"Date","Brand","Type"},HSTACK(TOCOL(IF(d<>"",CONSOLIDATED!D1:UW1,1/0),2),TOCOL(IF(d<>"",d,1/0),2),TOCOL(IF(d>"",CONSOLIDATED!C200:C259,1/0),2))))

Yes, thank you! That solved it!!
I'll mark this as solved, thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.