Combining rows

[toomb]

Hi, I am looking for some help with an Excel formula (unsurprisingly)

I have 4,000+ rows of data in the format below. Every row only has one result in one of the year columns. I am looking for a way to combine the rows into something like in the second image, and have been tearing my hair out trying to work out how to do it.

Any help would be very gratefully accepted

 

[Peter_SSs]

Welcome to the MrExcel board!

Hi, I am looking for some help with an Excel formula

If you are looking to do this with a formula, then you will need to create a separate table as shown below. Here are two suggestions.

A) The solution in columns J:Q requires the CONCAT function that is only available in recent Excel versions.
J2 is copied down
K2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and downdown.

B) The solution in columns S:Z requires Excel 2010 or later.
S2 copied down
T2 is a standard-entry formula and again copied across and down.

(For the future, you will get many more potential helpers if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post. My signature block below has help regarding that. Helpers cannot copy from images like you posted and generally shy away from having to type out sample data manually. )

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Player	2003	2004	2005	2006	2007	2008	2009		Player	2003	2004	2005	2006	2007	2008	2009		Player	2003	2004	2005	2006	2007	2008	2009
2	Player 1	a								Player 1	a	a							Player 1	a	a
3	Player 1		a							Player 2		b	b	b	b	b	b		Player 2		b	b	b	b	b	b
4	Player 2		b
5	Player 2			b
6	Player 2				b
7	Player 2					b
8	Player 2						b
9	Player 2							b
Combine Rows

 

[toomb]

Thank you so much for the help, that has worked perfectly.

(and for the point on how I displayed the question!)

Thanks again

 

[Peter_SSs]

Thank you so much for the help, that has worked perfectly.

You are welcome.