Subscribe to DSC Newsletter

Hello All,

I have a small query regarding pulling out the required observations out of a dataset with given conditions.The question is as follows:

I have a dataset XYZ which has three numeric variables namely "acc_no" ,"month" and "txn_amt".There is only one transaction per month for any acc_no, in case there is any transaction in that particular month.The dataset contains data for one particular year(12 months) i.e. the maximum number of transactions an account number can have in an year would be 12.The transactions necessarily start in January and continue with one transaction per month.Once there occurs a month in which there is no transaction for an acc_no  ,then there do not occur any transactions till the end of the year. i.e if  a particular account number has a last transaction in the month of JUNE then the account definitely has one entry each from January to June.This is the background of the situation.

Now I sort XYZ by "acc_no" and "Month".

Next, I drop the variable month.

Dataset XYZ has only two variables now namely,  "acc_no"  and "txn_amt"

Now I want to pull out those observations which are the entries for the months of March and July.

How would I go about?

Please find attached a sample of the sorted data(Sort_Drop_Pull.XLSX).

Thanks!

Tags: Sorting

Views: 212

Attachments:

Reply to This

Replies to This Discussion

Hi dear,

I would request you try the following code that will help you out.

Sub Pulling_Data()
'
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
ActiveWorkbook.Activate
Worksheets("Sheet1").Activate
Cells(1, "A").EntireRow.Copy
Worksheets("Sheet2").Activate
Cells(1, "A").Select
Selection.PasteSpecial Paste:=xlValue
Worksheets("Sheet1").Activate
i = 1
j = 1
Do
If Cells(i, "A") = Cells(i, "A").Offset(1, 0) Then
If Cells(i, "B") = 3 Or Cells(i, "B") = 7 Then
Cells(i, "A").EntireRow.Copy
Worksheets("Sheet2").Activate
If Cells(j, "A") <> "" Then
Cells(j, "A").Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlValues
j = j + 1
Worksheets("Sheet1").Activate
End If
End If
End If
Application.CutCopyMode = False
i = i + 1
Loop Until Cells(i, "A") = ""
Application.ScreenUpdating = True
End Sub

Have a nice evening.

RSS

On Data Science Central

© 2019   AnalyticBridge.com is a subsidiary and dedicated channel of Data Science Central LLC   Powered by

Badges  |  Report an Issue  |  Privacy Policy  |  Terms of Service