Looking up data if a cell has a value 0 or 1 only

[Guest]

Hope this makes sence

Some of our employees in our 12 branches operate a supervisor role some
months and a clerk role other months, there is no fixed pattern. Each branch
will list these employees twice in their branches workbook.

In column A - the employee number is held, column B & C the employee name
and in column D the employees working position (Supervisor for one row and
Clerk for the other).

The only way I can check what position the employee operated each month is
to go into each workbook and see if a 1 or 0 value is entered under the
column for that month i.e. Column N for December etc A Value will be entered
if the employee worked in that position i.e. if they were a supervisor for
December a 1 or 0 will be entered in the row where their position is
supervisor in column D and nothing will be entered in the row were their
position is clerk in column D - it will be left blank.

Each month the payroll department send me a list of 1500 employees salary
cost center code which indicates what the employee was paid as for a
particular month, i.e. supervisor or clerk.

My task is to check that the correct cost center code has been applied to
the employee for that month.

I need to build a formula to allow me on a clean workbook to enter the
column that needs to be checked in each branches workbook and if there is a 0
or 1 value in that column then the employee details in Column ABC and D must
be picked up for that row and pasted in my workbook. If there is no value or
a different value nothing should be returned.

I have a formula to compare the cost center codes when I get the position on
to this sheet.

Any ideas? , currently I am auto filtering and it takes for ever.

I guess I need something like, "IF'coulmn 'X' workbook address, is = 0 or 1
then lookup the value on column ABC & D and paste it in this workbook"

Thanks

 

[Kypp]

This is not an easy one to explain but this is the way that I would do
it:

I would create a Command Button and code it with the following code.
This would work for all of the values in column D and assuming your 0's
1's start in row 2 (January I am assuming) you could change it when you
wanted Feb, Mar, etc.

a = 0
b = 0

Do Until a = X (see below)
If Range("D2").offset(a, 0) = 0 Then
Range("O2") = Range("A2").offset(b, 0)
Range("P2") = Range("B2").offset(b, 0)
Range("Q2") = Range("C2").offset(b, 0)
b = b + 1
End If
If Range("D2").offset(a, 0) = 1 Then
Range("O2") = Range("A2").offset(b, 0)
Range("P2") = Range("B2").offset(b, 0)
Range("Q2") = Range("C2").offset(b, 0)
b = b + 1
End If
a = a + 1
Loop

X would be the difference between your first row of employee data and
the last row of employee data. Alternatively you could set it to say
5000 it will just take a second or two more to run. This should give
you all of the info in Columns A, B, and C that have values of 1 or 0
in the January column and output them into columns O, P, and Q. You
could then copy and paste these into your own workbook where ever you
wanted them. If you wanted say December instead of January then change
the "D2" to "D12"

 

[Guest]

Thanks Kypp

To be honest that lost me a bit. I'm not fluent enough to create a comand
(think your on about a macro).

If I just want to do a VLOOKUP on a table if a coumn has a value how would
Iwrite that.

I.e. IF column 12 has a value look up and paste data in column 1, 2 and 3 of
the table.?

Thanks.

 

[Kypp]

Vlookup wont work since it will only return the first value it finds
for a particular employee number and since you have different entries
for the same employee it wont do what you are looking for it to do.

Hope you find another solution though.

GL