Match 3 Criteria In A Range Then Pull Value

[Xndman]

Hello All,
I have one spreadsheet that contains all my data by FY, Group, EOC, and $ Amount. I want to populate another spreadsheet showing the data in table format (not using pivot table because I want to be able to give $ minimum criteria) by changing the Group.

Group Lab
EOC FY03 Bud FY04 Bud FY05 Bud
7203

In this example, I want only the Lab values for each time period to appear in the currect columns for EOC 7203.




FY Group EOC CLOSE
FY03 Bud Lab 6301 100000
FY04 Bud OR 6301 97500
FY03 Bud HR 6405 95000
FY04 Bud Lab 7203 92500
FY03 Bud OR 6556 90000
FY04 Bud HR 6556 87500
FY03 Bud Lab 7203 85000
FY04 Bud OR 7203 82500
FY03 Bud HR 7423 80000
FY04 Bud Lab 7423 77500
FY05 Bud OR 7423 75000
FY03 Bud HR 6607 72500
FY04 Bud Lab 6607 70000
FY05 Bud OR 6607 67500
FY03 Act HR 7120 65000
FY03 Bud Lab 7120 62500
FY04 Act OR 7120 60000
FY04 Bud HR 7120 57500
FY05 Act Lab 7120 55000
FY05 Bud OR 7120 52500



Any thoughts??

Thanks!!

 

[Domski]

Hi,

Would Advanced Filter do what you want?

You can specify your criteria and then copy it to another location.

Dom

 

[Xndman]

Thanks for the reply. Sorry my table looks wacked, but I can't get the plug in to show the excel format to work. What I'm looking for is Excel to see that the: Group = Lab, EOC = 7203, and to return a FY03 Bud value of 85,000 and a FY04 Bud value of 92,500. Basically match the Group and EOC value to the correct time period.

Thanks!!

 

[Domski]

I think Advanced Filter is the way forward for you.

I'm afraid I'm having trouble with the HTML maker myself but I'll try to explain.

If you have your data in columns A:D insert a few rows above the data and copy your headings so as it looks like this:

Row 1 FY¦Group¦EOC¦CLOSE
Row 2 Blank
Row 3 Blank
Row 4 FY¦Group¦EOC¦CLOSE
Row 5 Data
Row 6 Data
Row 7 Etc.

In Row 2 you can specify your critieria so as in your example put Lab in B2 and 7203 in C2

Select Data...Filter...Advaned Filter and use these settings:

Action: Copy to another location
List Range: The range of your data (in this example $A$4:$D$24 or however far your data goes)
Criteria: $A$1:$D$2
Copy to: $F$4

Doing this with your example data I got the results you expected. You can use >80000 in the CLOSE criteria if you want to specify minimum $ values.

Hope this helps.

Dom.

 

[texasalynn]

is this what you are seeking?

Book3
	A	B	C	D	E	F	G	H	I	J
1	FY	Group	EOC	CLOSE		Criteria:
2	FY03 Bud	Lab	6301	100000		Group	EOC	FY	Close
3	FY04 Bud	OR	6301	97500		LAB	7203	FY03 BUD	85000
4	FY03 Bud	HR	6405	95000
5	FY04 Bud	Lab	7203	92500
6	FY03 Bud	OR	6556	90000
7	FY04 Bud	HR	6556	87500
8	FY03 Bud	Lab	7203	85000
9	FY04 Bud	OR	7203	82500
10	FY03 Bud	HR	7423	80000
11	FY04 Bud	Lab	7423	77500
12	FY05 Bud	OR	7423	75000
13	FY03 Bud	HR	6607	72500
14	FY04 Bud	Lab	6607	70000
15	FY05 Bud	OR	6607	67500
16	FY03 Act	HR	7120	65000
17	FY03 Bud	Lab	7120	62500
18	FY04 Act	OR	7120	60000
19	FY04 Bud	HR	7120	57500
20	FY05 Act	Lab	7120	55000
21	FY05 Bud	OR	7120	52500
22
Sheet1

HTH
texasalynn

 

[Xndman]

Thanks for all the responses. Texas your solution worked perfectly. Dom your solution would have been great, but there are many EOCs and the $ amounts applied for the budget column will change often.

Thanks!!