Match 3 Criteria In A Range Then Pull Value

Xndman

Board Regular
Joined
Nov 2, 2002
Messages
211
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!!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Would Advanced Filter do what you want?

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

Dom
 

Xndman

Board Regular
Joined
Nov 2, 2002
Messages
211
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
is this what you are seeking?
Book3
ABCDEFGHIJ
1FYGroupEOCCLOSECriteria:
2FY03 BudLab6301100000GroupEOCFYClose
3FY04 BudOR630197500LAB7203FY03 BUD85000
4FY03 BudHR640595000
5FY04 BudLab720392500
6FY03 BudOR655690000
7FY04 BudHR655687500
8FY03 BudLab720385000
9FY04 BudOR720382500
10FY03 BudHR742380000
11FY04 BudLab742377500
12FY05 BudOR742375000
13FY03 BudHR660772500
14FY04 BudLab660770000
15FY05 BudOR660767500
16FY03 ActHR712065000
17FY03 BudLab712062500
18FY04 ActOR712060000
19FY04 BudHR712057500
20FY05 ActLab712055000
21FY05 BudOR712052500
22
Sheet1


HTH
texasalynn
 

Xndman

Board Regular
Joined
Nov 2, 2002
Messages
211
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top