COMBINING ROW DATA FROM 2 ROWS TO 1

[JLouis]

I'm trying to combine names from 2 different rows into a single rown as shown on the sheet below. Maybe I'm approaching this wrong and would welcome a different solution or a modification to my formula. Thanks for looking this over.

"=IFERROR(INDEX($A$1:$F$1, COLUMNS(A5:$F$5)), IFERROR(INDEX($A$2:$F$2, COLUMNS(A5:$F$5)-COLUMNS($A$1:$F$1)),""))"

DK Single Game.xlsm
	A	B	C	D	E	F	G
1	JONES	SMITH
2	ANDREWS	FORD	DUFFY	JOHNSON
3
4
5	0	0	0	0	SMITH	JONES	<- formula result
6
7	JONES	SMITH	ANDREWS	FORD	DUFFY	JOHNSON	<- what I would like it to be
TEAMS

Cell Formulas
Range	Formula
A5:F5	A5	=IFERROR(INDEX($A$1:$F$1, COLUMNS(A5:$F$5)), IFERROR(INDEX($A$2:$F$2, COLUMNS(A5:$F$5)-COLUMNS($A$1:$F$1)),""))

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	JONES	SMITH
2	ANDREWS	FORD	DUFFY	JOHNSON
3
4
5
6	JONES	SMITH	ANDREWS	FORD	DUFFY	JOHNSON
7
Sheet5

Cell Formulas
Range	Formula
A6:F6	A6	=TOROW(A1:F2,1)
Dynamic array formulas.

 

[JLouis]

That is incredible! I've never seen "TOROW" nor knew it existed. How easy and clean. Awesome solution and rapid response. Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JLouis]

I'm back. My apologies for not explaining fully. (Although I'm digging TOROW)

There are more names under the set of four. I need the first 2 names to be paired with the subsequent names going down the sheet.

JONES	SMITH
ANDREWS	FORD	DUFFY	JOHNSON	TO	JONES	SMITH	ANDREWS	FORD	DUFFY	JOHNSON
DALY	ABES	THOMAS	MASSEY	TO	JONES	SMITH	ANDREWS	FORD	DUFFY	JOHNSON

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	JONES	SMITH
2	ANDREWS	FORD	DUFFY	JOHNSON		JONES	SMITH	ANDREWS	FORD	DUFFY	JOHNSON
3	DALY	ABES	THOMAS	MASSEY		JONES	SMITH	DALY	ABES	THOMAS	MASSEY
Sheet5

Cell Formulas
Range	Formula
F2:K3	F2	=HSTACK($A$1:$B$1,A2:D2)
Dynamic array formulas.

 

[JLouis]

Thanks again Fluff. Very cool. Worked great except there is a formula in C1 that may or may not have a name in it. HSTACK put a zero in that spot on the return. If I remove the formula, HSTACK works perfectly.

I saw that the TOROW function could skip blanks, does HSTACK any such function or could I modify the formula in C1 to provide a result that HSTACK would skip?

Or maybe, if I fill in the names (constants) with VBA so there is not formula involved perhaps?

What do you think?

 

[Fluff]

What do you think?

I have no idea as you keep moving the goal posts.
You need to provide accurate information for me to be able to help.

 

[JLouis]

I'll make it work and understand my lack of clarity hampers your response. I appreciate your assistance greatly.