Hi
I am a very basic user of excel, I am trying to extract data based on 3 conditions, tried to understand and did what ever was shown but still unable to get a solution any help would be highly appreciated.
Columns P,Q,R are search criterion s is total meeting that criterion
need to extract all data from left hand side meeting the search criterion ideally on sheet 2, but can't even make it work on same sheet.
please help
Re: Trying to extract data meeting 3 conditions
Here is some VBA code that does what you ask. It is important that the three criteria are case sensitive.
Option Explicit
Sub Amitouj()
Dim i As Long, lr As Long, lr2 As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For i = 4 To lr
lr2 = Range("P" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
If Range("B" & i) = Range("P2") Then
If Range("D" & i) = Range("Q2") Then
If Range("C" & i) = Range("R2") Then
Range("B" & i & ":N" & i).Copy Range("P" & lr2 + 1)
End If
End If
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "complete"
End SubRe: Trying to extract data meeting 3 conditions
Dear Alan
thanks for your help but as i said i am a beginner level excel user, i pasted your code in a macro and clicked run but nothing happened am i doing anything wrong ? please assist..... is there no way we can do this as a formula
Re: Trying to extract data meeting 3 conditions
In the attached, it works for me. Did you make sure that your criteria are case sensitive. ie. The term annually must be in all caps as that is how the data is presented.
Re: Trying to extract data meeting 3 conditions
hi Alan,
That did the trick, i saved it and reopened and typed in caps and it retrives the data, but it does not clearing the old search results i.e my first seacrh parameters were execution 2, month = april,frequency = Annually, and then if need to find data for monthly it is not clearing the old search results but just adding new data to the list, i just need the data that fulfill the stated criterions can that be done ?
Re: Trying to extract data meeting 3 conditions
Here is some amended code to delete prior information before running query
Sub Amitouj()
Dim i As Long, lr As Long, lr2 As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
lr2 = Range("P" & Rows.Count).End(xlUp).Row
Range("P4:AB" & lr2).ClearContents
For i = 4 To lr
lr2 = Range("P" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
If Range("B" & i) = Range("P2") Then
If Range("D" & i) = Range("Q2") Then
If Range("C" & i) = Range("R2") Then
Range("B" & i & ":N" & i).Copy Range("P" & lr2 + 1)
End If
End If
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "complete"
End SubRe: Trying to extract data meeting 3 conditions
Hi
Alan, it worked Thank you so Much as i wanted it to but it removed the heading row and space btw the search and the result as well...... but it worked. can the result be dispalyed as it dispalayed inthe first code
Re: Trying to extract data meeting 3 conditions
Sorry Alan
tested the data 2nd time the deletion is not working properly as it keep deleting the rows from top rather than from particular rows where data is available. In second trial it even deleted the search query box from top. cant we fix rows where the result would display?
Re: Trying to extract data meeting 3 conditions
I don't understand your issue. In the code, it determines what is the last row in column P. It then deletes all data from Cells P4:AB and the last row. If this is not what you are looking for, then please be as specific as possible in your explanation and if possible show with an example of what you want.
Don’t have an account yet? Register yourself now and be a part of our community!