Extract data with 2 criteria

[marreco]

Hi.
I have a worksheet called "agenda" and another called "Result"

I need to return the worksheet "Output" the "Clients" (which is in worksheet "agenda"), according to cell "C1" and cells "A2", "C2", "E2", "G2", "I2 "plnilha the" Result "

I did a manual example for you, can understand.

[Excel Fox]

marreco, try this to start for


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
If Target.Address(0, 0) = "C1" Then
With Worksheets("AGENDA")
For Each rng In .UsedRange.Rows(1).Find(What:=Me.Range("C1").Value, LookAt:=xlWhole).MergeArea.Cells(1).Offset(2, 2).Offset(, -2).Resize(.UsedRange.Rows.Count - 1).Cells
'Modification here'
Next rng
End With
End If

End Sub

[marreco]

Hi.

I do not know much about vba.
I can not imagine how to continue.
but has a detail, your code should pull the spreadsheet data "ANGENDA" to the sheet "Result"

Thank you!!

[marreco]

Hi.

Any idea?

thank you!!

[marreco]

Hi.

Cross-Post
Extract data with 2 criteria (http://www.excelforum.com/excel-programming-vba-macros/876257-extract-data-with-2-criteria.html)

[Admin]

Hi

Put this code in the Result sheet module (Right click on tab Result > View code and paste)


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim c As Range, x, y

If Target.Address(0, 0) = "C1" Then
y = Range("a2:j2").Value2
With Worksheets("AGENDA")
Set c = .UsedRange.Rows(1).Find(What:=Me.Range("C1").Value, LookAt:=xlWhole).MergeArea.Cells(1)
If Not c Is Nothing Then
For Each rng In c.MergeArea.Cells(1).Offset(2).Offset(, 2).Resize(.UsedRange.Rows.Count - 2).Cells
x = Application.Match(rng.Value, y, 0)
If Not IsError(x) Then
Application.EnableEvents = False
Me.Cells(4, x) = rng.Offset(, -2).Value
Application.EnableEvents = True
End If
Next rng
End If
End With
End If

End Sub

[marreco]

Hi.
I do not know why but when you change the data in "C1" worksheet "Results" in nothing happens.

[Charles]

Hi,

I modified the code supplied by "Admin"
In this example I used a command button to populate the data to the "Result" sheet.
All you need to do is select the data in C1 and click Run.

[marreco]

Hi.

Yes!!, perfect!!

Thank you!!

[Charles]

Thanks,
Please check the spelling in row 2 of the "Result" sheet.
I changed it to that of the spelling in the "Activity" sheet.
Some of the words have a "-", but the Result sheet did not.
You need to make sur the spelling is the same in both sheets.

[marreco]

Hi.
I have a new file with one more criterion.
I need that data extraction also look for the date.
Now I have 3 criteria
1st Go check in "C1" worksheet "Result" and look at sheet "AGENDA"

2nd Go check in "A4", "C4", "E4", "G4", "I4" worksheet "Result" look in worksheet "AGENDA"

3 and also dates "C2" and "C3" worksheet "Result" look in worksheet "AGENDA"
See the file

[Charles]

marreco,

In looking at the worksheet. How would you determine the "First" and "Last" date.

[marreco]

Hi.
Because dates were in "C2" and "C3"
All that is greater than "C2" and less than "C3"

[Charles]

HI,

You want to look in "AGENDA" for the Dates.
How would you determine which Date in that sheet you want in C2 and C3 of the "Result" sheet?

[marreco]

Hi
by I do not know speak English, sometimes I get confused reading.

in this case the sheet "Results" criteria will "C2" and "C3" and then extract the data from "AGENDA"

[Charles]

Hi,

In the sheet "AGENDA" for Raposa you have several Dates in column F. I'm assuming you want these dates to populate the Result sheet C2 and C3.
But, if you look at Column F you have several Dates. Which of these Dates do you want to populate Result?
How would you determine this?

[marreco]

Hi.
Imagine that you are on the "Results" in "C1", you typed "RAPOSA".
Now in "C2", you typed the following date: dd/MM/yyyy (01/11/2012).
And in "C3" You have entered the following data: dd/MM/yyyy (03/11/2012).

then the tab labeled "Results" in "G6" we the Cliente "D", and "I6" we the Cliente "E"

[Charles]

OK,

If I understand what you want you will enter the "Client" and the "Dates" in the "Result" sheet.
You then just want to populate the Result sheet that equals the Client and Date from the Agenda sheet.
Right?

[marreco]

Yes!!

[marreco]

Hi Any idea?

Thank you!!

[Charles]

Hi,

Sorry I was side tracked.
As for the dates. What if 1 date is missing?
Will the dates that you specify be in the "Agenda" sheet?

[Charles]

Hi,

Here's the file. I left the dates that I used in place.
I did not fully test.

[marreco]

Hi Charles

I'm trying to change your code to fetch the column Lápis.
Enjoy what is ready to put the Lápis.

and am having trouble on the dates

[Charles]

Hi,

I'd like to help, but you need to tell me what your trying to do.
In the code I see you have "c" and "d" doing the same thing.

[marreco]

Hi.
has a down arrow, I need to return the data for the value that is in "C1" and also by date (equal and maoir) -> "E1" and "G1" <- (equal and smaller)

I got back when "C1" has the value "CASA"
but other values {Computador, Familia, Lápis} I can not return.,

[Charles]

Hi,

Check your Date range. The Dates in Casa falls with in your range in C1 and E1 and the others do not.
Thats why Casa works.

[marreco]

Hi.

I realized that this format is reading
Date = MM / dd / yyy

I need that law in this format
Date = DD / mm / yyy

[Charles]

It's not the format of the dates its the date range.
The dates in the other than Casa do not fall with in the specified dates.

[marreco]

Hi.
but because when I change from "E1" and "G1" I get back "Pencil" and others?

[Charles]

Hi,

Your problem is with the dates.
The Dates that you have in "Casa" is ok.
The Dates you have in "Raposa" are not ok.
The date you have in E1 of PROGRAMAÇÃO is


11/4/2012

and the dates in Raposa is


11/3/2012
10/10/2012
10/11/2012
11/1/2012
11/2/2012
12/18/2012
11/3/2012
11/3/2012
11/3/2012
11/3/2012

As you can see the list of dates are not >= 11/4/2011.

Thats why when you run the code it does not copy any thing to the Program sheet.

The Dates for Casa" are


11/2/2012
11/1/2012
11/5/2012
11/3/2012
11/2/2012
11/8/2012
11/4/2012
11/5/2012
11/6/2012
11/7/2012

And as you can see you have several Dates that are >= 11/4/2012.

There for the code will show the data in the Programe sheet.

I hope tis helps you.