Data Query using Match/Index
 

Every month my company outputs an excel file in the format (not that the
project number is only used once on a differnt row than everything else):

project Desc Amount
1.2.1
labor 10
material 5
other 15
1.2.X
labor 12
material 33
other 11

If, however, a project (using project 1.2.1 as an example) did not incur
material expenses then the report would look like:

project Desc Amount
1.2.1
labor 10
other 15
1.2.X
labor 12
material 33
other 11

I'd like to set up a query to find out the cost for a particular project and
a particular desc... for example, what were the labor costs for project
1.2.1. I can easily use match/index if the project number was on every row,
but since it is only on one row, I am stumped. Any help would be much
appreciated.

Data Query using Match/Index
 

I can easily use match/index if the project number was on every row,
but since it is only on one row, I am stumped.

It's easy to set it up to be completely filled in a helper col,
then just use the helper in the index/match

Assume the top project number is in A2
In D2: =A2
In D3: =IF(A3="",D2,A3)
Copy D3 down to the last row of data in cols B/C, to return the required
"filled" replica of col A
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Bara Reyna" wrote:
Every month my company outputs an excel file in the format (not that the
project number is only used once on a differnt row than everything else):

project Desc Amount
1.2.1
labor 10
material 5
other 15
1.2.X
labor 12
material 33
other 11

If, however, a project (using project 1.2.1 as an example) did not incur
material expenses then the report would look like:

project Desc Amount
1.2.1
labor 10
other 15
1.2.X
labor 12
material 33
other 11

I'd like to set up a query to find out the cost for a particular project and
a particular desc... for example, what were the labor costs for project
1.2.1. I can easily use match/index if the project number was on every row,
but since it is only on one row, I am stumped. Any help would be much
appreciated.

Data Query using Match/Index
 

Sure this is an Excel group, but what you described would be perfect for an
Access query. Maybe you should look at this:
http://office.tizag.com/accessTutori...essqueries.php

HTH,
Ryan--

--
RyGuy


"Max" wrote:

I can easily use match/index if the project number was on every row,
but since it is only on one row, I am stumped.

It's easy to set it up to be completely filled in a helper col,
then just use the helper in the index/match

Assume the top project number is in A2
In D2: =A2
In D3: =IF(A3="",D2,A3)
Copy D3 down to the last row of data in cols B/C, to return the required
"filled" replica of col A
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Bara Reyna" wrote:
Every month my company outputs an excel file in the format (not that the
project number is only used once on a differnt row than everything else):

project Desc Amount
1.2.1
labor 10
material 5
other 15
1.2.X
labor 12
material 33
other 11

If, however, a project (using project 1.2.1 as an example) did not incur
material expenses then the report would look like:

project Desc Amount
1.2.1
labor 10
other 15
1.2.X
labor 12
material 33
other 11

I'd like to set up a query to find out the cost for a particular project and
a particular desc... for example, what were the labor costs for project
1.2.1. I can easily use match/index if the project number was on every row,
but since it is only on one row, I am stumped. Any help would be much
appreciated.