Using VBA to combine data from 2 sheets using a common number.

[ZeroTrinder]

Good Afternoon All!

I have been searching for a while for a solution to this problem but I am still having issues (I don't think I am using the correct search terms!).

Basically I have 2 sheets that need combining,

"Sanding Report"
This contains a date and time in Column A, an ID number in B, a Sand location in C.

"Sheet1"
This contains an ID number in A (which is the common data), a diagram ID in B, ID code in C, Location in D and Location arrival time and date in E.

Basically I am trying to get a 3rd sheet created called "Sanding Overview" which has:

ID number in A, the sanding report sand location in B, sanding report date and time in C, Diagram ID in D, ID Code in E, Sheet1 Location in F and Location arrival time in G.

Something like this:

I would be running this with new data every day so ideally I need the "Sanding Report" and "Sheet1" to be deleted after and the "Sanding Overview" to be sorted by "Unit" and "Date and time" with the option to filter by end location (but all of that is just a dream at the moment, the initial hurdle is the biggest issue!)

If anyone can help it would be very much appreciated!

Thank you for you time,

Zero

 

[NdNoviceHlp]

Hi Zero.... your sanding report, sheet1 and sanding overview doesn't seem to correspond ie. your sanding report contains different dates & unit numbers not represented in sheet1 and sanding overview. A few questions... are there duplicate unit numbers; what happens the 2nd time U sort the info into sanding overview (ie. append new info to the bottom or replace the previous info); when sorting by unit numbers... lowest to highest or vice versa, when sorting by date.... new to old or vice versa, when sorting by end location A to Z or whatever? Can you please post a file with representative data... it's very difficult to code and test without sample data. HTH. Dave

 

[ZeroTrinder]

Apologies, basically the Sanding Report contains when the units were filled with sand, i have a macro that deletes the blanks and any that have been sanded in the last 3 days, the information in sheet 1 is when end location of every unit, i basically need the result to show what units are overdue for sanding including the last sanded date and the end location an time so they can be attended. If any units appear in the sheet1 that aren't shown in Sanding report, they can be deleted and ignored.

Then next time the report is run, new data will be exported into the sheet again so all the old data is irrelevant. The sort needs to be unit lowest to highest and date oldest to newest, for example;

158881 01/01/20
158880 02/01/20
158889 02/01/20
458519 01/01/20
458520 01/01/20
707019 02/01/20
etc
etc

As for the data that's another tough one as I think it's company sensitive.

Thank you for getting back to me.

Kind regards,

Zero

 

[NdNoviceHlp]

Hey Zero, just to be clear, the unit number will be present in both Sheet 1 (always) and the sanding report if it needs to be in the overview? The date and time in "C" of the sanding overview is from the sanding report? How many rows of data exist/may potentially exist? It seems fairly doable but it will take some time to replicate your data and test any VBA solution. I will await your reply to my questions. Dave

 

[NdNoviceHlp]

Seems like it snowed and I'm avoiding outside work. I'll assume the unit number exists in both sheet1 and the sanding report and the date and time is from the sanding report. The sanding overview sheet must exist. This code sorts by unit. To sort by date and time or by location, comment out the code U need and remove the comment from the relevant line of code. HTH. Dave

Option Explicit
Sub Test()
Dim Lastrow As Integer, Lastrow2 As Integer, Cnt As Integer
Dim Cnt2 As Integer, SortRange As Range
Sheets("Sanding Overview").UsedRange.ClearContents
Sheets("Sanding Overview").Range("A" & 1).Value = "Unit"
Sheets("Sanding Overview").Range("B" & 1).Value = "Depot"
Sheets("Sanding Overview").Range("C" & 1).Value = "Date and Time"
Sheets("Sanding Overview").Range("D" & 1).Value = "Diagram ID"
Sheets("Sanding Overview").Range("E" & 1).Value = "Head Code"
Sheets("Sanding Overview").Range("F" & 1).Value = "End Location"
Sheets("Sanding Overview").Range("G" & 1).Value = "Arrival Time"
With Sheets("Sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Sheets("Sanding report")
    Lastrow2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With
'put all data out of order on sheet
For Cnt = 2 To Lastrow
'unit
Sheets("Sanding Overview").Range("A" & Cnt) = Sheets("sheet1").Range("A" & Cnt)
'depot & date & time
For Cnt2 = 2 To Lastrow2
'depot
If Sheets("sheet1").Range("A" & Cnt) = Sheets("Sanding report").Range("B" & Cnt2) Then
Sheets("Sanding Overview").Range("B" & Cnt) = Sheets("sanding report").Range("C" & Cnt2)
'date & time
Sheets("Sanding Overview").Range("C" & Cnt) = Sheets("sanding report").Range("A" & Cnt2)
Exit For
End If
Next Cnt2
'diagram ID
Sheets("Sanding Overview").Range("D" & Cnt) = Sheets("sheet1").Range("B" & Cnt)
'head code
Sheets("Sanding Overview").Range("E" & Cnt) = Sheets("sheet1").Range("C" & Cnt)
'end location
Sheets("Sanding Overview").Range("F" & Cnt) = Sheets("sheet1").Range("D" & Cnt)
'arrival time
Sheets("Sanding Overview").Range("G" & Cnt) = Sheets("sheet1").Range("E" & Cnt)
Next Cnt
'sort data
With Sheets("Sanding Overview")
Set SortRange = .Range(.Cells(2, 1), .Cells(Lastrow, 7))
End With
With SortRange
'sort by unit
.Sort Key1:=.Range("A2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'sort by date and time
'.Sort Key1:=.Range("C2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'sort by end location
'.Sort Key1:=.Range("F2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

 

[alansidman]

a fairly simple task with Power Query. No elaborate Code required.

Join two or more tables in Excel with Power Query

Need to analyze a lot of data scattered across many worksheets in Excel? Join tables by matching one or more columns with Power Query or Merge Tables Wizard.

www.ablebits.com

 

[ZeroTrinder]

Seems like it snowed and I'm avoiding outside work. I'll assume the unit number exists in both sheet1 and the sanding report and the date and time is from the sanding report. The sanding overview sheet must exist. This code sorts by unit. To sort by date and time or by location, comment out the code U need and remove the comment from the relevant line of code. HTH. Dave

Option Explicit
Sub Test()
Dim Lastrow As Integer, Lastrow2 As Integer, Cnt As Integer
Dim Cnt2 As Integer, SortRange As Range
Sheets("Sanding Overview").UsedRange.ClearContents
Sheets("Sanding Overview").Range("A" & 1).Value = "Unit"
Sheets("Sanding Overview").Range("B" & 1).Value = "Depot"
Sheets("Sanding Overview").Range("C" & 1).Value = "Date and Time"
Sheets("Sanding Overview").Range("D" & 1).Value = "Diagram ID"
Sheets("Sanding Overview").Range("E" & 1).Value = "Head Code"
Sheets("Sanding Overview").Range("F" & 1).Value = "End Location"
Sheets("Sanding Overview").Range("G" & 1).Value = "Arrival Time"
With Sheets("Sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Sheets("Sanding report")
    Lastrow2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With
'put all data out of order on sheet
For Cnt = 2 To Lastrow
'unit
Sheets("Sanding Overview").Range("A" & Cnt) = Sheets("sheet1").Range("A" & Cnt)
'depot & date & time
For Cnt2 = 2 To Lastrow2
'depot
If Sheets("sheet1").Range("A" & Cnt) = Sheets("Sanding report").Range("B" & Cnt2) Then
Sheets("Sanding Overview").Range("B" & Cnt) = Sheets("sanding report").Range("C" & Cnt2)
'date & time
Sheets("Sanding Overview").Range("C" & Cnt) = Sheets("sanding report").Range("A" & Cnt2)
Exit For
End If
Next Cnt2
'diagram ID
Sheets("Sanding Overview").Range("D" & Cnt) = Sheets("sheet1").Range("B" & Cnt)
'head code
Sheets("Sanding Overview").Range("E" & Cnt) = Sheets("sheet1").Range("C" & Cnt)
'end location
Sheets("Sanding Overview").Range("F" & Cnt) = Sheets("sheet1").Range("D" & Cnt)
'arrival time
Sheets("Sanding Overview").Range("G" & Cnt) = Sheets("sheet1").Range("E" & Cnt)
Next Cnt
'sort data
With Sheets("Sanding Overview")
Set SortRange = .Range(.Cells(2, 1), .Cells(Lastrow, 7))
End With
With SortRange
'sort by unit
.Sort Key1:=.Range("A2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'sort by date and time
'.Sort Key1:=.Range("C2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'sort by end location
'.Sort Key1:=.Range("F2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

Thank you for getting back to me, I have just tried this and its so close! I feel like I am really restricting you by holding on to the data.

I can send you what I have so far but I'm fairly new to this (forums and Excel) so not sure the best way to do it?

The Issue is the data coming into the Sanding overview is in the wrong lines (such as Vehicle formation is being pasted in Diagram ID and End Location in Start Time).

Thank you so much once again for getting back to me, I really really appreciate your help!

Zero

 

[NdNoviceHlp]

Hi Zero. "data into Sanding overview is in the wrong lines" .... wrong rows or wrong columns? There was no Vehicle formation or Start Time in your examples??? It's fairly easy to re-arrange where the data is moved to if you were to share exactly what column from what sheet should go to exactly what column of the Sanding Overview report. A bit more info is needed. Dave

 

[ZeroTrinder]

Hi Zero. "data into Sanding overview is in the wrong lines" .... wrong rows or wrong columns? There was no Vehicle formation or Start Time in your examples??? It's fairly easy to re-arrange where the data is moved to if you were to share exactly what column from what sheet should go to exactly what column of the Sanding Overview report. A bit more info is needed. Dave

Apologies, Wrong columns and the vehicle information shouldn't be being shown at all.

I feel I need to add some more background. Basically we have a wide list of units that must be sanded every 3 days.

Those units end at different locations every night. Staff sand the units and check them as done on an app which shows where it was done. I then use a Macro to open this information on the sanding overview spreadsheet and delete any units that have been done in the last 3 days (so only showing anything older than that).

I then use another program that shows where every unit will end tonight (for example) and export that data using another Macro.

The part i can't figure out is getting a Macro to see what units appear on both sheets and combine the information to show me a list of units that need sanding with where they were last sanded / when, what diagram they are on, the head code of the last journey of the diagram, where the unit ends up and what time the the unit gets to that last location.

In a perfect world it would keep the data from the day before to show the end location from the previous night (to understand where the unit left in the morning) but I felt this was far too complicated.

It gets even more complicated when the information needs to be run again in the afternoon

I am completely lost and this spreadsheet could be so useful and reduce a workload by about 3-4 hours every day.

I feel like I am taking up so much of your time but I really appreciate the replies!

 

[NdNoviceHlp]

I don't mind helping but if U want a solution again I'm not psychic... "It's fairly easy to re-arrange where the data is moved to if you were to share exactly what column from what sheet should go to exactly what column of the Sanding Overview report. A bit more info is needed." Dave