Combining rows yet keeping them in order utilizing a macro

[ikindaknow]

Hello!

This is my first post to this website! I have been lurking on here for sometime now...

I have been dabbling with macros for a bit, but this is my first major one I have tried to construct/meld together from what I have seen online. I am beyond stuck and close to giving up.

I am trying to meld two data sets together. Each have names that may or may not be in the other data set, but when they are in both columns I need the names to line up. Thus, it is possible for a name in "Name 1" to never match up with a name in "Name 2", when this occurs I would like spaces to fill up the gaps. Also, I have two separate entries for each name, with an in and a corresponding out time, it is silly, but thats the out put I was given, and these need to line up in order to calculate duration. So it is possible in one data set to have 2500 John Does and in another data set have 6400 John Does (the same person), but also 0 Mark F and 10 Mark F's. No names can be delete, just shift around with its corresponding data.

Example: (i didnt fill in the date and time, but the first line is a time in and the second one is a time in and they MUST stay together in order to calculate duration) The first two rows are how it looks.

Name 1 Time In Time Out Duration Date Name 2 Time In Time Out Duration Date
John D 1457 Jan 1/15 Mike B 1608 Feb 3/14
John D 0858 18:01 Jan 2/15 Mike B 2012 4:04 Feb 3/14
John D 1425 jan 2/15 John D John D 0830 18:05 jan 3 /15
Bill K John D
Bill K
Sean Matt B
Sean Matt B
Matt B
Matt B
Harry Bill K
Harry Bill K

Desired:

Name 1 Time In Time Out Duration Date Name 2 Time In Time Out Duration Date

John D 1457 Jan 1/15 John D
John D 0858 18:01 Jan 2/15 John D
John D 1425 jan 2/15
John D 0830 18:05 jan 3/15
Bill K Bill k
Bill K Bill K
Sean
Sean
Matt B Matt B 1608
Matt B Matt B 2012 4:04 Feb 3/14
Matt B
Matt B

Harry
Harry

Actual Columns
[TABLE="width: 2299"]
<colgroup><col width="185"><col width="110"><col width="40"><col width="99"><col width="36"><col width="56"><col width="26"><col width="41"><col width="38"><col width="30"><col width="71"><col width="64"><col width="83"><col width="64"><col width="31"><col width="18"><col span="2" width="64"><col width="147"><col width="25"><col width="24"><col width="18"><col width="81"><col width="109"><col width="68"><col width="58"><col width="121"><col width="153"><col width="56"><col width="61"><col width="130"><col span="2" width="64"></colgroup><tbody>[TR]
[TD="class: xl68, width: 185"]Name 1
[/TD]
[TD="class: xl68, width: 110"]Position 1
[/TD]
[TD="class: xl68, width: 40"]Site 1
[/TD]
[TD="class: xl70, width: 99"]Shift 1
[/TD]
[TD="class: xl69, width: 36"]Date In 1
[/TD]
[TD="class: xl71, width: 56"]Time In 1
[/TD]
[TD="class: xl68, width: 26"]Type 1
[/TD]
[TD="class: xl71, width: 41"]Time Out 1
[/TD]
[TD="class: xl72, width: 38"]Date out 1
[/TD]
[TD="class: xl73, width: 30"]Time Spent1
[/TD]
[TD="class: xl75, width: 71"]Name Match
[/TD]
[TD="class: xl75, width: 64"]Position Match[/TD]
[TD="class: xl68, width: 83"]Duration 1
[/TD]
[TD="class: xl74, width: 64"]Site 2
[/TD]
[TD="class: xl74, width: 31"]Level 2
[/TD]
[TD="class: xl74, width: 18"] Date 2
[/TD]
[TD="class: xl74, width: 64"]Time In 2
[/TD]
[TD="class: xl74, width: 64"]Shift 2
[/TD]
[TD="class: xl74, width: 147"]Person 2
[/TD]
[TD="class: xl74, width: 25"]Equipment 2
[/TD]
[TD="class: xl74, width: 24"]Reason
[/TD]
[TD="class: xl74, width: 18"] Remarks
[/TD]
[TD="class: xl74, width: 81"] [/TD]
[TD="class: xl74, width: 109"]Time 2
[/TD]
[TD="class: xl74, width: 68"]Department 2
[/TD]
[TD="class: xl74, width: 58"]Duration Temp
[/TD]
[TD="class: xl74, width: 121"]Time Out 2
[/TD]
[TD="class: xl74, width: 153"]Duration 2
[/TD]
[TD="class: xl74, width: 56"]Entered By[/TD]
[TD="class: xl74, width: 61"]Entered[/TD]
[TD="class: xl76, width: 130"]Name[/TD]
[TD="class: xl76, width: 64"]Job 2
[/TD]
[TD="class: xl76, width: 64"]Site 2
[/TD]
[/TR]
</tbody>[/TABLE]

My current macro:

Sub test()
Dim rngList1 As Range, rngList2 As Range
Dim colCount1 As Long, colCount2 As Long
Dim Name1 As String, Name2 As String

Dim HasHeaders As Boolean
Dim rowNum As Long

Set rngList1 = Sheet1.Range("T2"): Rem adjust
Set rngList2 = Sheet1.Range("A2"): Rem adjust
colCount2 = 3: Rem adjust number of columns in list 2
HasHeaders = True: Rem adjuts

colCount1 = rngList2.Column - rngList1.Column

With rngList1
With Range(.Cells(1, colCount1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo + CLng(HasHeaders)
.Select
End With
End With

With rngList2
With Range(.Cells(1, colCount1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo + CLng(HasHeaders)
.Select
End With
End With

rowNum = rngList1.Row - CLng(HasHeaders)
Set rngList1 = rngList1.Columns(1).EntireColumn
Set rngList2 = rngList2.Columns(1).EntireColumn

Do
Name1 = LCase(Application.Trim(CStr(rngList1.Cells(rowNum, 1).Value)))
Name2 = LCase(Application.Trim(CStr(rngList2.Cells(rowNum, 1).Value)))

If Name1 = Name2 Then
Rem do nothing
ElseIf Name1 < Name2 Then
rngList2.Cells(rowNum, 1).Resize(1, colCount2).Insert shift:=xlDown
Else
rngList1.Cells(rowNum, 1).Resize(1, colCount1).Insert shift:=xlDown
End If

rowNum = rowNum + 1
Loop Until rngList1.Cells(Rows.Count, 1).End(xlUp).Row < rowNum _
Or rngList2.Cells(Rows.Count, 1).End(xlUp) < rowNum

End Sub






















Any help would be greatly appreciated. Like I said, very much a beginner is writing the macros, so if you can help, take it easy on me please!

 

[ikindaknow]

Actual Columns
[TABLE="width: 2299"]
<tbody>[TR]
[TD="class: xl68, width: 185"]Name 1[/TD]
[TD="class: xl68, width: 110"]Position 1[/TD]
[TD="class: xl68, width: 40"]Site 1[/TD]
[TD="class: xl70, width: 99"]Shift 1[/TD]
[TD="class: xl69, width: 36"]Date In 1[/TD]
[TD="class: xl71, width: 56"]Time In 1[/TD]
[TD="class: xl68, width: 26"]Type 1[/TD]
[TD="class: xl71, width: 41"]Time Out 1[/TD]
[TD="class: xl72, width: 38"]Date out 1[/TD]
[TD="class: xl73, width: 30"]Time Spent1 [/TD]
[TD="class: xl75, width: 71"]Name Match[/TD]
[TD="class: xl75, width: 64"]Position Match[/TD]
[TD="class: xl68, width: 83"]Duration 1[/TD]
[TD="class: xl74, width: 64"]Site 2[/TD]
[TD="class: xl74, width: 31"]Level 2 [/TD]
[TD="class: xl74, width: 18"] Date 2[/TD]
[TD="class: xl74, width: 64"]Time In 2[/TD]
[TD="class: xl74, width: 64"]Shift 2[/TD]
[TD="class: xl74, width: 147"]Person 2[/TD]
[TD="class: xl74, width: 25"]Equipment 2[/TD]
[TD="class: xl74, width: 24"]Reason[/TD]
[TD="class: xl74, width: 18"] Remarks [/TD]
[TD="class: xl74, width: 81"][/TD]
[TD="class: xl74, width: 109"]Time 2[/TD]
[TD="class: xl74, width: 68"]Department 2[/TD]
[TD="class: xl74, width: 58"]Duration Temp [/TD]
[TD="class: xl74, width: 121"]Time Out 2 [/TD]
[TD="class: xl74, width: 153"]Duration 2[/TD]
[TD="class: xl74, width: 56"]Entered By[/TD]
[TD="class: xl74, width: 61"]Entered[/TD]
[TD="class: xl76, width: 130"]Name[/TD]
[TD="class: xl76, width: 64"]Job 2[/TD]
[TD="class: xl76, width: 64"]Site 2[/TD]
[/TR]
</tbody>[/TABLE]

The Name 1, Date 1 all the way to Duration 1 is one data set and site 2 to "Entered" is another data set. Information under Name, Site, position may or may not line up or even match. The first data set is entry into a building, the second data set is the access given inside of the building, but two completly different systems and they do not have the ability to talk to one another, hence me doing this, or at least trying to.