merging rows while keeping data in original columns

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"]
DATA CURRENTLY LOOKS LIKE
First SurnameEvent 1Event 2Event 3
CarolJones00:56:54
SarahSmith01:15:03
Peter Brown
Peter Brown01:01:38
Peter Brown01:05:36
Peter Brown01:06:15
StevenAnderson00:58:46
JoeEvans00:43:17
ChrisAndrew1:06:23
ChrisAndrew1:07:24
ChrisAndrew01:09:19
Chris Andrew
WOULD LIKE DATA TO LOOK LIKE
First SurnameEvent 1Event 2Event 3
CarolJones00:56:54
SarahSmith01:15:03
Peter Brown01:01:3801:05:3601:06:15
StevenAnderson00:58:46
JoeEvans00:43:17
ChrisAndrew01:09:191:06:231: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][/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:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try this:-
NB:- Results on sheet2.
Code:
[COLOR=navy]Sub[/COLOR] MG07Apr46
[COLOR=navy]Dim[/COLOR] ray [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
ray = Activesheet.Cells(1).CurrentRegion.Resize(, 6)
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
  .CompareMode = vbTextCompare
    ReDim nRay(1 To UBound(ray, 1), 1 To 6)
        [COLOR=navy]For[/COLOR] n = 1 To UBound(ray, 1)
            Txt = ray(n, 1) & ray(n, 2)
            [COLOR=navy]If[/COLOR] Not .Exists(Txt) [COLOR=navy]Then[/COLOR]
                c = c + 1
                [COLOR=navy]For[/COLOR] Ac = 1 To 6
                    nRay(c, Ac) = IIf(Ac > 2, Format(ray(n, Ac), "hh:mm:ss"), ray(n, Ac))
                [COLOR=navy]Next[/COLOR] Ac
                .Add Txt, c
            [COLOR=navy]Else[/COLOR]
                [COLOR=navy]For[/COLOR] Ac = 1 To 6
                  [COLOR=navy]If[/COLOR] Not ray(n, Ac) = "" [COLOR=navy]Then[/COLOR]
                    nRay(.Item(Txt), Ac) = IIf(Ac > 2, Format(ray(n, Ac), "hh:mm:ss"), ray(n, Ac))
                   [COLOR=navy]End[/COLOR] If
                [COLOR=navy]Next[/COLOR] Ac
            [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR]
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
NB:- Make sure you have a "Sheet2"
Regrds Mick

Regards Mick
 
Last edited:
Upvote 0
Thanks so much for your reply. It slightly throws me into a state of panic when I see Macro involved. Is there a simple (ish?) formula I could use instead?
 
Upvote 0
Sorry for the delay in replying. I have been trying to learn something about macros before giving your fix a go. I have had a go now but am getting an error when I run the macro that says 'run-time error 9 subscript out of range'. does that make any sense to you?
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top