Data Extraction

[farhansaleem]

Hi,

I have some data arranged in Year, Month, Day format. I want to extract the common numbers of days (respective to month and year) from the 4 data sets. 

I have also attached the excel file to review.

Please guide me thanks

Regards

Farhan

[#2 · Accepted solution]

@farhansaleem 

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 Function

Regards from germany

Bernd

www.vba-Tanker.com

[#3]

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

[#4]

@farhansaleem 

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 Sub

Regards

Bernd

www.vba-tanker.com

[#5]

@farhansaleem 

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.

[#6]

Hi Bernd,

Yes, man this is exactly i want to extract the common numbers of days from multiple sheets. Thank you for the help and guidance on it.

Farhan

[#7]

Hi Peter,

Actually, i have to apply composite analysis on these extracted number of common days. Thank you for the help.

Farhan

[#8]

@farhansaleem 

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.

@Berndvbatanker 

I am sure an efficient VBA solution is available for this class of problem using the scripting dictionary to identify duplicates.