Merging multiple rows of data for one animal

[EtudiantWisconsin]

Hello I have a data set like this on about 200 animals so about 1000 rows:​

Cat Rabies # Microchip # Collar # Clinic # (each number below is in a different column - each time I edit this post they all snap back to the cat's name!)

Shaggy 4345
Shaggy RE3455678
Shaggy 9008
Shaggy 1222


And I want to end up with this:



Cat Rabies# Microchip # Collar # Clinic #

Shaggy 4345 RE3455678 9008 1222


Can anyone help with this problem?

Thank you
etudiant

 

[Robert Mika]

Hello and welcome to MrExcel.
Are your data sorted and you have got always 4 lines for each animals?
And the final result is like this?
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Cat	Rabies	Microchip	Collar	Clinic	Cat	Rabies	Microchip	Collar	Clinic
Shaggy	Shaggy	RE3455678
Shaggy	RE3455678
Shaggy
Shaggy

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4345[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4345[/TD]

[TD="align: right"]9008[/TD]
[TD="align: right"]1222[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9008[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1222[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[EtudiantWisconsin]

My data is sorted on the cat's name column.
I have anywhere from 1 to 6 lines per cat.

Your row number 2 columns H through L is what I would like to create - where all the ID's are in one row


​

 

[Robert Mika]

My data is sorted on the cat's name column.
I have anywhere from 1 to 6 lines per cat.

Your row number 2 columns H through L is what I would like to create - where all the ID's are in one row


​

Try this(Excel 07/10 version)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Cat	Rabies	Microchip	Collar	Clinic	ELSE		Cat	Rabies	Microchip	Collar	Clinic	ELSE
2	Shaggy	4345						Shaggy	4345	RE3455678	9008	1222
3	Shaggy		RE3455678					mama	1	2	3	4
4	Shaggy			9008				kaaa	A	B
5	Shaggy				1222			rrrr	11	22	33	44	55
6	mama	1
7	mama		2
8	mama			3
9	mama				4
10	kaaa	A
11	kaaa		B
12	rrrr	11
13	rrrr		22
14	rrrr			33
15	rrrr				44
16	rrrr					55
Sheet1

 

[EtudiantWisconsin]

Mr. Mika,
Thank you very much for your help. I don't understand your string formula at all mainly because of my limited Excel skills. Would you be kind enough to talk (write) me through the steps of your formula so that I understand the functions you have employed. I also don't understand the significance of cell 12 either? Please explain. Thank you again for your excellent help. Exactly how would I use your formula? Please consider me a low-skilled Excel user.