Rearranging data

[excel_beta_345User]

I have two tables as per attached images. Now I want to take data from table 2 and arrange that in table 3 in such order that it matches names as per Column A from table 1. If name matches the cell from Colum A of table 1 A then only it should list all the details in row in table 3. In case if it does not match then it should not be added at all, row should remain blank. It would be great if those non-matched name and their details from table 2 are added at the end of table 3 but if not, I can apply conditional formatting to find out those non matching between table 2 and 3 and cut paste them to move out at the end of table 3, hope that works.

Common factor between table 1 and 3 should be Column with Header "Name". Name in column J should be able to match the same order as of Column A. Can you please help to achieve this?

Sorry that I cannot use XL2BB add-in, unfortunately 0365 policies does not allow that hence had to share image again.

 

[excel_beta_345User]

See if this is what you want.

25 01 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2	Name	Jersey	Team	Date		Name	Car	Country		Name	Car	Country
3	Pele					Alfredo Di Stefano	G3	H3
4	Diego Maradona					Ferenc Puskas	G4	H4
5	Lionel Messi					Xavi Hernandez	G5	H5		Lionel Messi	G9	H9
6	Cristiano Ronaldo					Garrincha	G6	H6
7	Johan Cruyff					Franz Beckenbauer	G7	H7
8	Szinedine Zidane					George Best	G8	H8
9	Michel Platini					Lionel Messi	G9	H9
10	Franz Beckenbauer									Franz Beckenbauer	G7	H7
11	Garrincha									Garrincha	G6	H6
12	George Best									George Best	G8	H8
13										Alfredo Di Stefano	G3	H3
14										Ferenc Puskas	G4	H4
15										Xavi Hernandez	G5	H5
16
Rearrange

Cell Formulas
Range	Formula
J3:L15	J3	=VSTACK(MAKEARRAY(ROWS(A3:A12),3,LAMBDA(r,c,IFERROR(INDEX(F3:H9,MATCH(INDEX(A3:A12,r),F3:F9,0),c),""))),FILTER(F3:H9,ISNA(MATCH(F3:F9,A3:A12,0))))
Dynamic array formulas.

Thank you so much Peter for your help, this worked.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Cubist]

Here's another option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2	Name	Jersey	Team	Date		Name	Car	Country		Name	Car	Country
3	Pele					Alfredo Di Stefano	G3	H3
4	Diego Maradona					Ferenc Puskas	G4	H4
5	Lionel Messi					Xavi Hernandez	G5	H5		Lionel Messi	G9	H9
6	Cristiano Ronaldo					Garrincha	G6	H6
7	Johan Cruyff					Franz Beckenbauer	G7	H7
8	Szinedine Zidane					George Best	G8	H8
9	Michel Platini					Lionel Messi	G9	H9
10	Franz Beckenbauer									Franz Beckenbauer	G7	H7
11	Garrincha									Garrincha	G6	H6
12	George Best									George Best	G8	H8
Sheet11

Cell Formulas
Range	Formula
J3:L12	J3	=IFNA(DROP(REDUCE("",A3:A12,LAMBDA(x,y,VSTACK(x,FILTER(F3:H9,F3:F9=y,"")))),1),"")
Dynamic array formulas.

 

[Peter_SSs]

Here's another option:

.. but it doesn't produced the expected results shown in post 1 - unless you append the remaining rows from Table 2

25 01 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2	Name	Jersey	Team	Date		Name	Car	Country		Name	Car	Country
3	Pele					Alfredo Di Stefano	G3	H3
4	Diego Maradona					Ferenc Puskas	G4	H4
5	Lionel Messi					Xavi Hernandez	G5	H5		Lionel Messi	G9	H9
6	Cristiano Ronaldo					Garrincha	G6	H6
7	Johan Cruyff					Franz Beckenbauer	G7	H7
8	Szinedine Zidane					George Best	G8	H8
9	Michel Platini					Lionel Messi	G9	H9
10	Franz Beckenbauer									Franz Beckenbauer	G7	H7
11	Garrincha									Garrincha	G6	H6
12	George Best									George Best	G8	H8
13										Alfredo Di Stefano	G3	H3
14										Ferenc Puskas	G4	H4
15										Xavi Hernandez	G5	H5
16
Rearrange (2)

Cell Formulas
Range	Formula
J3:L15	J3	=VSTACK(IFNA(DROP(REDUCE("",A3:A12,LAMBDA(x,y,VSTACK(x,FILTER(F3:H9,F3:F9=y,"")))),1),""),FILTER(F3:H9,ISNA(MATCH(F3:F9,A3:A12,0))))
Dynamic array formulas.

 

[Cubist]

A little shorter.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2	Name	Jersey	Team	Date		Name	Car	Country		Name	Car	Country
3	Pele					Alfredo Di Stefano	G3	H3
4	Diego Maradona					Ferenc Puskas	G4	H4
5	Lionel Messi					Xavi Hernandez	G5	H5		Lionel Messi	G9	H9
6	Cristiano Ronaldo					Garrincha	G6	H6
7	Johan Cruyff					Franz Beckenbauer	G7	H7
8	Szinedine Zidane					George Best	G8	H8
9	Michel Platini					Lionel Messi	G9	H9
10	Franz Beckenbauer									Franz Beckenbauer	G7	H7
11	Garrincha									Garrincha	G6	H6
12	George Best									George Best	G8	H8
13										Alfredo Di Stefano	G3	H3
14										Ferenc Puskas	G4	H4
15										Xavi Hernandez	G5	H5
Sheet15

Cell Formulas
Range	Formula
J3:L15	J3	=IFNA(DROP(REDUCE("",UNIQUE(VSTACK(A3:A12,F3:F9)),LAMBDA(x,y,VSTACK(x,FILTER(F3:H9,F3:F9=y,"")))),1),"")
Dynamic array formulas.