Not sure what formula to use

[catecumper]

Hi all,

Long time user, 1st time poster

I have about 700 lines of salary to move into three separate columns (C3, C4, C5) on the right, some of the employee numbers are in the column A more than once, depending on the different rates of pay they get and to do this manually is painful. There must be a formula to say that if employee number 101 (A3) has a salary (B3), then add B3 to column C3 or C4 or C5 depending on which department they work in. I have a separate table with what employee numbers are in each dept, and have tried Vlookup and IF, but when i copy the formula down it doesn't work


[TABLE="width: 500"]
<tbody>[TR]
[TD]emp no[/TD]
[TD]salary[/TD]
[TD]Dept1[/TD]
[TD]Dept2[/TD]
[TD]Det3[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


all help gratefully received.

 

[Gerald Higgins]

Hi, welcome to the board.

I think I understand what you want to do but your request is a bit vague.

What I would do is bring the department numbers across using VLOOKUP, like this

=VLOOKUP(A3,F$1:G$100,2,false)

This assumes that . . .
Your employee numbers start in cell A3 as stated.
Your table of employee numbers and departments is in the range F1:G100, with employee numbers in column F, and department IDs in col G.
Adapt as required.

If you can get this bit working, we can then move on to allocating salary to the right department.

 

[catecumper]

Hi Gerald,

Thanks for the quick reply and your help, yes I have managed to get the formula working.

I'll try to explain it a little better,

Column A = employee number, this can be repeated down the column
Column B = their salary for the month and any additional payments maid, OT, etc
Column C = Dept 1
Column D = Dept 2
Column E = Dept 3

If employee 101 is paid £1000 for the month and works in Dept 1, I would like the formula to show £1000 in column C (Dept 1), then if they have any extra payments further down the column, then that payment will also show in the Dept 1 column.

hope that helps.
Catherine

Hi, welcome to the board.

I think I understand what you want to do but your request is a bit vague.

What I would do is bring the department numbers across using VLOOKUP, like this

=VLOOKUP(A3,F$1:G$100,2,false)

This assumes that . . .
Your employee numbers start in cell A3 as stated.
Your table of employee numbers and departments is in the range F1:G100, with employee numbers in column F, and department IDs in col G.
Adapt as required.

If you can get this bit working, we can then move on to allocating salary to the right department.

 

[Gerald Higgins]

Hi, thanks. In my opinion, it's not usually necessary to quote other people's posts in their entirety, just makes the thread longer

You say you've managed to get the formula working.

So which bit do you still need help with, if any ?