Lookup tables
 

I have two worksheets named "Profit data" and "Labor". The Profit data sheet
includes amongst other things, the following:

Col A
Job #
Row 2 1185
1186
1204
1207
1214
to row 48

The "Labor" sheet has Employee Names and Jobs they worked on. E.G.

Col C Col D etc all the way to Col CH
Row 3 Brian Bill
Row 4 1202 1185
Row 5 1185 1214
etc
to Row 81

As an example In the "Profit data" sheet in Cell Z2 I want to create a
function that takes job 1885 and looks in the labor sheet for 1885 and then
returns the person's name that appears in Row 3, separated by a comma if more
than one person worked on the job. E.G. For Job 1885 the function needs to
return Brian,Bill

What is the best way to do this? I thought of using a lookup table but was
not sure how to return multiple names into the cell which are separated by a
comma.

Thanks.

Perhaps something along these lines might bring you
to a close approx of the desired end result?

In sheet: "Profit Data"
--------------
Assuming cols AA, AB, etc are all empty columns

Put in AA2: =IF(ISNUMBER(MATCH($A2,Labor!C:C,0)),Labor!C$3,"")

Copy AA2 across by 83* cols to to DE2,
fill down as many rows as there is data in col A
(*to cover the same range as in Labor, cols C to CH)

The above will return in cols AA to DE,
all the names from row3 of sheet: Labor
which match the job# in col A
(and that's the "close approx" results)

As for:

not sure how to return multiple names
into the cell which are separated by a
comma.

Think there was a subroutine recently posted by Bob Phillip
which could then take the results returned in cols AA to DE
and concat it nicely into col Z (I'll post the link later if I can find it
<g)

In the interim, only as a "demo" for the sample data posted
(assuming only cols C and D in "Labor")
you could put the formula below in Z2, and copy down:

=IF(COUNTIF(AA2:AB2,"")=2,"",IF(COUNTIF(AA2:AB2,"" )=1,TRIM(AA2&"
"&AB2),SUBSTITUTE(AA2&" "&AB2," ",", ")))

The above will return the results in Z2: Brian, Bill, and so on

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave" wrote in message
...
I have two worksheets named "Profit data" and "Labor". The Profit data
sheet
includes amongst other things, the following:

Col A
Job #
Row 2 1185
1186
1204
1207
1214
to row 48

The "Labor" sheet has Employee Names and Jobs they worked on. E.G.

Col C Col D etc all the way to Col CH
Row 3 Brian Bill
Row 4 1202 1185
Row 5 1185 1214
etc
to Row 81

As an example In the "Profit data" sheet in Cell Z2 I want to create a
function that takes job 1885 and looks in the labor sheet for 1885 and
then
returns the person's name that appears in Row 3, separated by a comma if
more
than one person worked on the job. E.G. For Job 1885 the function needs to
return Brian,Bill

What is the best way to do this? I thought of using a lookup table but was
not sure how to return multiple names into the cell which are separated by
a
comma.

Thanks.

Think there was a subroutine recently posted by Bob Phillips
which could then take the results returned in cols AA to DE
and concat it nicely into col Z (I'll post the link later if I can find it
<g)

Afraid I was mistaken, apologies. I found the thread but Bob's sub was for a
different scenario. Hope the close approximation suffices !

But do hang around awhile for possible insights from Bob P or others ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks! This will work and I can skip including the comma separated names and
use the data.

Is there a way to skip the interim steps that would be easier. What I am
ultimately trying to do is add up the profits from let's say Brian's jobs, is
there a way to skip the names and jump straight to the calculation using
sumif. The profit is in column N on the profit data sheet.

I know this is the wrong formula but what would the formula be if I want to
add the profit of the jobs Brian was on (i.e. from column N) if the job
number matched in his column in the labor allocation sheet.
=sumIF(ISNUMBER(MATCH($A2,Labor!D4:D41,0)),N2:N48)


"Max" wrote:

Think there was a subroutine recently posted by Bob Phillips
which could then take the results returned in cols AA to DE
and concat it nicely into col Z (I'll post the link later if I can find it
<g)

Afraid I was mistaken, apologies. I found the thread but Bob's sub was for a
different scenario. Hope the close approximation suffices !

But do hang around awhile for possible insights from Bob P or others ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

In sheet: "Profit Data"
----------------
Assuming the names are labelled across in AA1 to DE1 to correspond with the
order in row3 in "Labour" from C3 to CH3, viz: Brian, Bill, ... etc

Put in AA2:

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$48,Labor!C$4:C$50,0)),$N$2: $N$48)

Copy AA2 across to DE2

AA2 will return the total profit of all the jobs for Brian,
AB2 will return the corresponding total for Bill, and so on

Note that all the 3 ranges need to be identical in structure*, and you can't
use entire col references (e.g.: A:A, B:B, etc) in SUMPRODUCT

*Ranges: $A$2:$A$48, Labor!C$4:C$50 and $N$2:$N$48
are all 47 rows in height

(I used "$N$2:$N$48" to sync with your posted "N2:N48")

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave" wrote in message
...
Thanks! This will work and I can skip including the comma separated names
and
use the data.

Is there a way to skip the interim steps that would be easier. What I am
ultimately trying to do is add up the profits from let's say Brian's jobs,
is
there a way to skip the names and jump straight to the calculation using
sumif. The profit is in column N on the profit data sheet.

I know this is the wrong formula but what would the formula be if I want
to
add the profit of the jobs Brian was on (i.e. from column N) if the job
number matched in his column in the labor allocation sheet.
=sumIF(ISNUMBER(MATCH($A2,Labor!D4:D41,0)),N2:N48)

A sample file is at:
http://flypicture.com/p.cfm?id=45443
(right-click on link "Download File" at the top)
File: Dave_LookupTables_wksht.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----