Combining Data From Adjacent rows

Cmjack777

New Member
Joined
Nov 22, 2012
Messages
2
Greetings,

I have 2 spreadsheets that I am working with who's data I am trying to merge so that each person will end up with only one row of data. One spreadsheet has the orientation completion date in it, while the other has the checkin date.
I copied and pasted worksheet 2 into the bottom of worksheet one, then sorted the data alphabetically so that if the person has both an orientation completion and checkin date then the rows will be adjacent to one another and look like below.

I am looking for a solution to get it to look like the table at the bottom. Any help would be much appreciated.

Worksheet 1 is set up like this.
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]LNAME
[/TD]
[TD]FNAME
[/TD]
[TD]THIS COLUMN BLANK
[/TD]
[TD]ORIENTATION COMPLETION DATE
[/TD]
[/TR]
[TR]
[TD]SMITH
[/TD]
[TD]JANE
[/TD]
[TD][/TD]
[TD]08/12/2017
[/TD]
[/TR]
[TR]
[TD]JACK
[/TD]
[TD]JIM
[/TD]
[TD][/TD]
[TD]08/12/2017
[/TD]
[/TR]
[TR]
[TD]BIVENS
[/TD]
[TD]SAM
[/TD]
[TD][/TD]
[TD]09/10/2017
[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2 is set up like this
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]LNAME
[/TD]
[TD]FNAME
[/TD]
[TD]CHECKIN DATE
[/TD]
[TD]THIS COLUMN BLANK
[/TD]
[/TR]
[TR]
[TD]BIVENS
[/TD]
[TD]SAM
[/TD]
[TD]08/30/2017
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SMITH
[/TD]
[TD]JANE
[/TD]
[TD]07/22/2017
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What it looks like after combining
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]LNAME
[/TD]
[TD]FNAME
[/TD]
[TD]CHECKIN DATE
[/TD]
[TD]ORIENTATION DATE
[/TD]
[/TR]
[TR]
[TD]BIVENS
[/TD]
[TD]SAM
[/TD]
[TD]08/30/2017
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIVENS
[/TD]
[TD]SAM
[/TD]
[TD][/TD]
[TD]09/10/2017
[/TD]
[/TR]
[TR]
[TD]JACK
[/TD]
[TD]JIM
[/TD]
[TD][/TD]
[TD]08/12/2017
[/TD]
[/TR]
[TR]
[TD]SMITH
[/TD]
[TD]JANE
[/TD]
[TD]0722/2017
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SMITH
[/TD]
[TD]JANE
[/TD]
[TD][/TD]
[TD]8/12/2017
[/TD]
[/TR]
</tbody>[/TABLE]

WHAT I WANT IT TO LOOK LIKE
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]LNAME
[/TD]
[TD]FNAME
[/TD]
[TD]CHECKIN DATE
[/TD]
[TD]ORIENTATION DATE
[/TD]
[/TR]
[TR]
[TD]BIVENS
[/TD]
[TD]SAM
[/TD]
[TD]08/30/2017
[/TD]
[TD]09/10/2017
[/TD]
[/TR]
[TR]
[TD]JACK
[/TD]
[TD]JIM
[/TD]
[TD][/TD]
[TD]08/12/2017
[/TD]
[/TR]
[TR]
[TD]SMITH
[/TD]
[TD]JANE
[/TD]
[TD]0722/2017
[/TD]
[TD]08/12/2017
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try this for Results of sheet1 & sheet2 to show On Sheet3.
Code:
[COLOR="Navy"]Sub[/COLOR] MG17Dec22
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Sht1 [COLOR="Navy"]As[/COLOR] Variant, Sht2 [COLOR="Navy"]As[/COLOR] Variant, Ray [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
Sht1 = Sheets("Sheet1").Cells(1).CurrentRegion
    Sht2 = Sheets("Sheet2").Cells(1).CurrentRegion
        Ray = Array(Sht1, Sht2)
            ReDim nray(1 To UBound(Sht1, 1) + UBound(Sht2, 1), 1 To 4)
                nray(1, 1) = "LNAME": nray(1, 2) = "FNAME"
                    nray(1, 3) = "CHECKIN DATE": nray(1, 4) = "ORIENTATION DATE"
c = 1
[COLOR="Navy"]For[/COLOR] R = 0 To 1
   Col = IIf(R = 0, 4, 3)
    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray(R), 1)
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Ray(R)(n, 1) & Ray(R)(n, 2)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            Dic(Ray(R)(n, 1) & Ray(R)(n, 2)) = c
            nray(c, 1) = Ray(R)(n, 1): nray(c, 2) = Ray(R)(n, 2): nray(c, Col) = Ray(R)(n, Col)
        [COLOR="Navy"]Else[/COLOR]
            nray(Dic(Ray(R)(n, 1) & Ray(R)(n, 2)), Col) = Ray(R)(n, Col)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] R

[COLOR="Navy"]With[/COLOR] Sheets("Sheet3").Range("A1").Resize(c, 4)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thank You Mick! That worked like a charm. Some code I understand but I don’t understand that bit of code you wrote. Thanks so much. Merry Christmas.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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