Matching a value in two worksheets, and then copying a cell from one to other

[js4joe]

Hi there,
Noob alert
I have a workbook containing several worksheets. I will be adding another
worksheet that will, in effect, be a master for all of the other worksheets.
In the source worksheets (existing) there will be several employee id's
(unique) per sheet (sheets are different departments), and a seperate column
housing that employees pay rate. Any employee may work for one or more
departments, so there may be duplicate id's when considering all of the
worksheets, but in each, the id will be unique.
What I would like to do, is have the master worksheet with an individual
unique listing for every id appearing on the source sheets. Then, for
example, if one of the source sheets is named "Purchasing", I would like the
master to have a column for all purchasing pay-rates, should that employee
exist on the "Purchasing" sheet. I've been looking for something I could use
that would fulfill this psuedo code:

Copy pay rate from source to master Where employee id (source sheet) =
employee id (master sheet)

Then, in the master I could have a complete list of unique employees, each
row having a columns for each department.

Any ideas?

Thanks,
Joe

 

[Max]

Here's a way to set it up dynamically using non-array formulas ..

Assuming identically structured source sheets named as: Purchasing,
Accounts, Store, Admin, etc where employee names are running in A2 down, and
Pay rates listed in B2 down, eg:

---------Pay rate
Emp1 3
Emp3 7
Emp4 6
etc

Assume that all source sheets will contain up to a maximum of say, 10
employee names within A2:A11

In a sheet: X,

List the source sheetnames in any order in A1 down. Ensure these names match
exactly what's on the tabs (except for case)

Put in B1:
=INDEX(INDIRECT("'"&INDIRECT("A"&INT((ROW(A1)-1)/10)+1)&"'!A:A"),MOD(ROW(A1)-1,10)+2)

(Adjust the "10" within the INT(...) and MOD(...) to suit the max number of
employee names expected per sheet, in all the source sheets)

Put in C1:
=IF(ISERROR(B1),"",IF(B1=0,"",IF(COUNTIF($B$1:B1,B1)>1,"",ROW())))

Select B1:C1, copy down to cover the aggregated maximum expected no. of
employees in all the sheets. Eg if there's a total of 5 source sheets, copy
down to C50 (5 sheets x 10 rows each = 50 rows total)

X is a helper sheet to set it up for the extracts in the summary sheet

Then in the summary sheet, say: Z

Put in B1:
=INDEX(X!$A:$A,COLUMN(A1))
Copy B1 across as required to pull in all the source sheetnames as col
headers, eg copy B1 to F1 (for 5 source sheets)

Place in A2:
=IF(ROW(A1)>COUNT(X!C:C),"",INDEX(X!B:B,MATCH(SMALL(X!C:C,ROW(A1)),X!C:C,0)))

Put in B2:
=IF(OR($A2="",B$1=0),"",IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0)),"--",INDEX(INDIRECT("'"&B$1&"'!B:B"),MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0))))

Copy B2 across to F2. Then select A2:F2, copy down to cover the max expected
number of unique employees, eg down to F20 (say).

The above will return the required results dynamically from all the source
sheets. In col A will be returned the unique list of employees culled from
all source sheets, with employees' corresponding payrates (if any) listed
under the appropriate col headers in cols B to F. Employees not found in any
particular source sheet will have a "--" returned in the col. Adapt the "--"
return to suit.

 

[Max]

Here's a sample construct to complement:
http://www.savefile.com/files/147138
Dynamic extract unique emp list and payrates from several shts.xls

 

[Max]

The earlier set up fixes col B (ie "Pay rate" col, the key col) in the
source sheets as the desired return col. As an extension to the construct,
for greater flexibility as to which key col within the source sheets to
return the values within the summary sheet, try ...

In the summary sheet: Z,

Place this revised formula in B2 instead:
=IF(OR($A$1="",$A2="",B$1=0),"",IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0)),"--",INDEX(OFFSET(INDIRECT("'"&B$1&"'!A:A"),,MATCH($A$1,INDIRECT("'"&B$1&"'!1:1"),0)-1),MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0))))
then copy B2 across to F2. Then select A2:F2, copy down to cover the max
expected number of unique employees, eg down to F20 (say), as before. Rest
of earlier construct remains unchanged.

Then we could have a simple DV droplist in A1 to select the desired key col
labels, eg: Pay rate, Hours, etc. The values returned in Z from all the
source sheets would then correspond to the key col label selected in A1. The
selectable col labels in the DV must of course be consistent with those
within all the source sheets' row1.

Here's a revised sample with the above implemented:
http://www.savefile.com/files/147489
Dyn extract uniq emp list n keycol val fr var shts.xls

 

[js4joe via OfficeKB.com]

This is amazing. I am downloading the file you saved out now and can't wait
to get cracking on implementing your solution.
Thanks a million Max!

 

[Max]

You're welcome !
Thanks for feeding back ..