Need help developing a custom formula - Google Sheets

[RomoBot]

Basically I need raw data (on the right) to look like (the left)
Doesn't matter if cells B:D are concat together or split, the raw data can be either way, but I am struggling how to reconcile repeated values on different dates. Ideally this would all be done in one formula, but if needs to end up as steps that would be fine.

A	B	C	D		A	B
1/5/23	Washington	Oregon	Idaho		1/5/23	Washington
1/5/23	California				1/5/23	Oregon
1/5/23	New York				1/5/23	Idaho
3/16/23	Washington	Oregon	Idaho		1/5/23	California
3/16/23	California				1/5/23	New York
3/16/23	New York				3/16/23	Washington
4/4/23	Washington	Oregon	Idaho		3/16/23	Oregon
4/4/23	California				3/16/23	Idaho
4/4/23	New York				3/16/23	California
9/16/23	New York				3/16/23	New York
9/16/23	Texas				4/4/23	Washington
					4/4/23	Oregon
					4/4/23	Idaho
					4/4/23	California
					4/4/23	New York
					9/16/23	New York
					9/16/23	Texas

 

[Dave Patton]

Possibly

ByCol.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	State
2	01-May-2023	Washington		01-May-2023	Washington	Oregon	Idaho	California	New York
3	01-May-2023	Oregon		16-Mar-2023	Washington	Oregon	Idaho	California	New York
4	01-May-2023	Idaho		04-Apr-2023	Washington	Oregon	Idaho	California	New York
5	01-May-2023	California		16-Sep-2023	New York	Texas
6	01-May-2023	New York
7	16-Mar-2023	Washington
8	16-Mar-2023	Oregon
9	16-Mar-2023	Idaho
10	16-Mar-2023	California
11	16-Mar-2023	New York
12	04-Apr-2023	Washington
13	04-Apr-2023	Oregon
14	04-Apr-2023	Idaho
15	04-Apr-2023	California
16	04-Apr-2023	New York
17	16-Sep-2023	New York
18	16-Sep-2023	Texas
2c

Cell Formulas
Range	Formula
D2:I5	D2	=LET(u,UNIQUE(Table2[Date]),m,MAX(COUNTIF(Table2[Date],u)),f,WRAPROWS(TEXTSPLIT(TEXTJOIN(",",0,BYROW(u,LAMBDA(x,TEXTJOIN(",",0,EXPAND(FILTER(Table2[State],Table2[Date]=x),m,,""))))),","),m),HSTACK(u,f))
Dynamic array formulas.

 

[Dave Patton]

The suggestion above is for Excel 365; not Google Sheets.
I looked at the description not the title. This is an Excel forum.

 

[Joe4]

The suggestion above is for Excel 365; not Google Sheets.
I looked at the description not the title. This is an Excel forum.

Dave note that we do allow "Google Sheets" questions here. We ask that they mention it in their title and/or post, and post it in the "General Discussion & Other Applications" forum. The OP did both of those things, so did everything correctly.

Also note that if you are using the "Unanswered threads" listing to look for and answer questions, it shows you exactly which forum each thread is found in (many people miss that):