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.
LNAME
FNAME
THIS COLUMN BLANK
ORIENTATION COMPLETION DATE
SMITH
JANE
08/12/2017
JACK
JIM
08/12/2017
BIVENS
SAM
09/10/2017

<tbody>
</tbody>

Worksheet 2 is set up like this
LNAME
FNAME
CHECKIN DATE
THIS COLUMN BLANK
BIVENS
SAM
08/30/2017
SMITH
JANE
07/22/2017

<tbody>
</tbody>

What it looks like after combining
LNAME
FNAME
CHECKIN DATE
ORIENTATION DATE
BIVENS
SAM
08/30/2017
BIVENS
SAM
09/10/2017
JACK
JIM
08/12/2017
SMITH
JANE
0722/2017
SMITH
JANE
8/12/2017

<tbody>
</tbody>

WHAT I WANT IT TO LOOK LIKE
LNAME
FNAME
CHECKIN DATE
ORIENTATION DATE
BIVENS
SAM
08/30/2017
09/10/2017
JACK
JIM
08/12/2017
SMITH
JANE
0722/2017
08/12/2017

<tbody>
</tbody>
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

Cmjack777

New Member
Joined
Nov 22, 2012
Messages
2
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.
 

Forum statistics

Threads
1,082,603
Messages
5,366,578
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top