I would bring the text tile into Power Query which is called Get and Transform Data in later versions of Excel. Once in the PQ Editor, you can change the date from text to Date format.
Please upload (no pictures) a sample workbook that is representative of your actual data.
When I open your workbook, it is absolutely blank.
Try this:
Option Explicit
Sub Foo()
Dim i As Long, j As Long
Dim lr As Long, lr2 As Long
Application.ScreenUpdating = False
lr = Foglio1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = Foglio2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
For j = 2 To lr2
If Mid(Foglio1.Range("A" & i), 2, 13) = Mid(Foglio2.Range("A" & j), 2, 13) Then
Foglio1.Range("D" & i) = Foglio2.Range("A" & j)
End If
Next j
Next i
Application.ScreenUpdating = True
MsgBox "complete"
End SubWill there be more than one DT listed consecutively? ie. Cell B51 = DT and B52 = DT and then numbers continue? Is this ever the case?
Since you are using 365, you can read a PDF by bringing it into Power Query called Get and Transform Data and found on the Data Tab. Click on Data-->Get Data-->From File-->PDF. It will open in the PQ Editor. If you don't wish to make any changes to it, then click on Load and Close and it will bring it directly into Native Excel.
To get this info from a web site, use the same methodology but select From Web and enter the URL.
This a very easy task if you are willing to use Power Query. To demo this, instead of a picture (which I cannot manipulate), please upload your sample file.
Grouping is one of the simplest tasks that you can perform in Power Query which is on your Data Tab of the Ribbon. In your version, it is called Get and Transform Data.
An alternative is to employ Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [Last Takeoff EGTHDM SMOOTH current week]<=10 then 1 else 0),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"Here is a SQL statement I created using the data in an Access DB You should be able to adapt to MYSQL
SELECT Table1.COMPANY, Table2.TRUCK, Sum(Table2.TURNOVER) AS SumOfTURNOVER
FROM Table1 INNER JOIN Table2 ON Table1.TRUCKREG = Table2.TRUCK
GROUP BY Table1.COMPANY, Table2.TRUCK;Putting in VBA format
strSql = "SELECT Table1.COMPANY, Table2.TRUCK, Sum(Table2.TURNOVER) AS SumOfTURNOVER " & vbCrLf & _
"FROM Table1 INNER JOIN Table2 ON Table1.TRUCKREG = Table2.TRUCK " & vbCrLf & _
"GROUP BY Table1.COMPANY, Table2.TRUCK;"Have you tried using the Round Function?
An alternative to VBA is Power Query. Here is the Mcode and file attached.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.From([Column1]) is number otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = true then [Column1] else null),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"Option Explicit
Sub RemoveTime()
Dim rng As Range
Dim c As Range
Dim lr As Long
lr = Range("D" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Set rng = Range("D3:V" & lr)
For Each c In rng
If IsDate(c) Then
c = Format(c, "dd-mmm-yy")
End If
Next c
Application.ScreenUpdating = True
MsgBox "completed"
End SubYou may not need VBA. Depending upon which version of Excel you are using, FlashFill may be an option available to you.
In M6 =COUNTIFS('List of Tickets'!$M$2:$M$199,Sheet1!L6) and copy down.
A sample worksheet would help to get a solution.
To help you understand PQ:
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Can be achieved with Power Query.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 8), type number),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1"),
#"Filled Up" = Table.FillUp(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] = "Call")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
#"Removed Columns"In the PQ solution, if you add new data, click on the Refresh All and your PQ will update automatically
Can be accomplished with Power Query/Get and Transform Data
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reported Date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow() - DateTime.From([Reported Date])),
#"Inserted Days" = Table.AddColumn(#"Added Custom", "Days", each Duration.Days([Custom]), Int64.Type),
#"Inserted Hours" = Table.AddColumn(#"Inserted Days", "Hours", each Duration.Hours([Custom]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Custom]), Int64.Type),
#"Added Suffix" = Table.TransformColumns(#"Inserted Minutes", {{"Days", each Text.From(_, "en-US") & " Days", type text}}),
#"Added Suffix1" = Table.TransformColumns(#"Added Suffix", {{"Hours", each Text.From(_, "en-US") & " Hours", type text}}),
#"Added Suffix2" = Table.TransformColumns(#"Added Suffix1", {{"Minutes", each Text.From(_, "en-US") & " Minutes", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Added Suffix2",{"Days", "Hours", "Minutes"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Duration"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Custom"})
in
#"Removed Columns"File attached for review. Note that you have approx 40 rows with no reporting date. How would you like those handled?