excelfan14
New Member
- Joined
- Apr 6, 2018
- Messages
- 3
I wonder if any of you can help me. I have a large amount of data taking up over 1000 rows. The data is for entrants results across 4 events (columns C, D, E and F). Sometimes an entrant will take part in 2 or more of the events. Each event they take part in is shown in a separate row. What I would like to do is have each entrant with the same name consolidated into just one row while keeping the event data under the correct column. What I could do if it helps is concatenate the first and surname (ie Column A and B) into just one column.
Below is an example:
[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 192, bgcolor: transparent, colspan: 3"][TABLE="width: 491"]
<tbody>[TR]
[TD="colspan: 3"]
<tbody>
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 2"] Event 4 [/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]01:03:22[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]01:08:22[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 2"] Event 4 [/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]01:03:22[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
</tbody>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]So, Peter Brown has entered all 4 events and Chris Andrew has entered 4. Everyone else has entered just 1. I would like to find an easy way of ensuring that everyone with the same name that features more than once is consolidated into just one row as per the example at the bottom of the image above ie Peter Brown and Chris Andrew are on just one line but the data for each event is still kept under the appropriate event column.
I'm afraid that, while I am a massive fan of excel, I am very much a rather basic user so would need quite a simple guide to how to sort the data. I sometimes see things posted about 'macros' but would have no idea what or how they worked.
Any help greatly appreciated.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Below is an example:
[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 192, bgcolor: transparent, colspan: 3"][TABLE="width: 491"]
<tbody>[TR]
[TD="colspan: 3"]
DATA CURRENTLY LOOKS LIKE | |||||
First | Surname | Event 1 | Event 2 | Event 3 | |
Carol | Jones | 00:56:54 | |||
Sarah | Smith | 01:15:03 | |||
Peter | Brown | ||||
Peter | Brown | 01:01:38 | |||
Peter | Brown | 01:05:36 | |||
Peter | Brown | 01:06:15 | |||
Steven | Anderson | 00:58:46 | |||
Joe | Evans | 00:43:17 | |||
Chris | Andrew | 1:06:23 | |||
Chris | Andrew | 1:07:24 | |||
Chris | Andrew | 01:09:19 | |||
Chris | Andrew | ||||
WOULD LIKE DATA TO LOOK LIKE | |||||
First | Surname | Event 1 | Event 2 | Event 3 | |
Carol | Jones | 00:56:54 | |||
Sarah | Smith | 01:15:03 | |||
Peter | Brown | 01:01:38 | 01:05:36 | 01:06:15 | |
Steven | Anderson | 00:58:46 | |||
Joe | Evans | 00:43:17 | |||
Chris | Andrew | 01:09:19 | 1:06:23 | 1:07:24 |
<tbody>
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 2"] Event 4 [/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]01:03:22[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]01:08:22[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 2"] Event 4 [/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]01:03:22[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
</tbody>
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]So, Peter Brown has entered all 4 events and Chris Andrew has entered 4. Everyone else has entered just 1. I would like to find an easy way of ensuring that everyone with the same name that features more than once is consolidated into just one row as per the example at the bottom of the image above ie Peter Brown and Chris Andrew are on just one line but the data for each event is still kept under the appropriate event column.
I'm afraid that, while I am a massive fan of excel, I am very much a rather basic user so would need quite a simple guide to how to sort the data. I sometimes see things posted about 'macros' but would have no idea what or how they worked.
Any help greatly appreciated.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Last edited by a moderator: