Forum Discussion
Data Extraction
Hi, i hope that I have understood the task. You want to check if a date is on monday to friday?
I write an userdefined function, see attached file, too.
Function commonDays(strYear As String, strMonth As String, strDay) As Boolean
Dim dteDate As Date
dteDate = DateSerial(strYear, strMonth, strDay)
If Application.WorksheetFunction.Weekday(dteDate, vbMonday) < 6 Then
commonDays = True
Else
commonDays = False
End If
End FunctionRegards from germany
Bernd
http://www.vba-Tanker.com
7 Replies
This was a solution obtained with the dynamic array version of Excel to produce a list. It is possible to do it in standard versions of Excel. The count only requires COUNTIFS though.
I am sure an efficient VBA solution is available for this class of problem using the scripting dictionary to identify duplicates.
Firstly, is this the calculation you have in mind?
I have taken the first table as the master and identified the records that appear in all three secondary tables. A further helper field is used to identify the dates but the orange cell gives the count without using this field.
Hi Peter,
Actually, i have to apply composite analysis on these extracted number of common days. Thank you for the help.
Farhan
Hi, i hope that I have understood the task. You want to check if a date is on monday to friday?
I write an userdefined function, see attached file, too.
Function commonDays(strYear As String, strMonth As String, strDay) As Boolean
Dim dteDate As Date
dteDate = DateSerial(strYear, strMonth, strDay)
If Application.WorksheetFunction.Weekday(dteDate, vbMonday) < 6 Then
commonDays = True
Else
commonDays = False
End If
End FunctionRegards from germany
Bernd
http://www.vba-Tanker.com
Hi Bernd,
Thank you to your prompt response to my request. The function that you defined is informative but actually i want to extract the numbers of days that are found common among the four data sheets in excel.
Likewise;
Sheet-1 contain days 24,25,26, for April, 1979
Sheet-2 only contain 28th day of April, 1979 while;
Sheet-3 and 4 does not contain any number of days in April, 1979 so we can say that we do not found common numbers of days in April, 1979.
Whereas;
Sheet-1,2,3 and 4 all contain the days of 18,19 for the month of June, 1979 as common number of days.
These common days are also highlighted with yellow color in the attached excel sheet, thanks
Regards
Farhan
Hi Farhan,
ok, let's take a look at the attached file. These days who are common in every sheet are colored green in column A.
Sub CommonDaysInAllSheets()
Dim lngCol As Long
Dim lngRow As Long
Dim lngRowMax As Long
Dim rngFind1 As Range
Dim rngFind2 As Range
Dim rngFind3 As Range
With Tabelle1
lngRowMax = .Range("A" & .Rows.Count).End(xlUp).Row
For lngCol = 4 To 17 Step 4
For lngRow = 3 To lngRowMax
.Cells(lngRow, lngCol).Value = _
DateSerial(.Cells(lngRow, lngCol - 3).Value, .Cells(lngRow, lngCol - 2).Value, .Cells(lngRow, lngCol - 1).Value)
Next lngRow
Next lngCol
For lngRow = 3 To lngRowMax
Set rngFind1 = .Range("H:H").Find(what:=.Cells(lngRow, "D").Value, lookat:=xlWhole)
If Not rngFind1 Is Nothing Then
Set rngFind2 = .Range("L:L").Find(what:=.Cells(lngRow, "D").Value, lookat:=xlWhole)
If Not rngFind2 Is Nothing Then
Set rngFind3 = .Range("P:P").Find(what:=.Cells(lngRow, "D").Value, lookat:=xlWhole)
If Not rngFind3 Is Nothing Then
.Cells(lngRow, "D").Interior.ColorIndex = 4
End If
End If
End If
Next lngRow
End With
End SubRegards
Bernd
http://www.vba-tanker.com
