List with wages

  • Thread starter Thread starter Thorleif Cederqvist
  • Start date Start date
T

Thorleif Cederqvist

Hello

I have a list of people with related wage data.
In column "A" there is a unique number for each person.
In column "B" the name of the person.
In column "C" number of days.
Every person can be in one or more rows.
I want to sum the values in column "C" for each person, and write the data
(in a single row) into a new sheet.

Any helt would be appreciatad

Regards,
TC, Sweden
 
Thorleif,

For each person's number, use a formula like

=SUMPRODUCT((Sheet1!A1:A20=1)*(Sheet1!C1:C20))

the 1 being the unique number. Repeat for each person.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
TC

One way:

Select the new sheet
Select Data | Filter | Advanced Filter
Click on "copy to another location"
In the List range box, select the column (A) with the "staff number" data in
the "master" sheet
In the Copy to box, select cell A1 on the new sheet
Select Unique records only
Click OK

In cell B1 on the new sheet, enter the formula:
=VLOOKUP(Sheet2!A1,Sheet1!$A$1:$C$11,2,FALSE)
Adjust the cell ranges as necessary and then fill down

In cell C1 on the new sheet, enter the formula:
=VLOOKUP(Sheet2!A1,Sheet1!$A$1:$C$11,3,FALSE)
In cell C2 on the new sheet, enter the formula:
=SUMIF(Sheet1!$A$2:$A$11,A2,Sheet1!$C$2:$C$11)
Adjust the cell ranges as necessary and then fill down

You could record this to see the code generated but you would need to modify
it a little to cater for the varying number of rows of data.

This is some sample code based on 11 rows of information (including the
header row). Note this is not what you get if you simply record the
actions.

Sub Macro1()
With Sheets("Sheet2")
Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True
.Range("B1").FormulaR1C1 =
"=VLOOKUP(Sheet2!RC[-1],Sheet1!R1C1:R11C3,2,FALSE)"
.Range("B1").AutoFill Destination:=.Range("B1:B5"), Type:=xlFillDefault
.Range("C1").FormulaR1C1 =
"=VLOOKUP(Sheet2!RC[-2],Sheet1!R1C1:R11C3,3,FALSE)"
.Range("C2").FormulaR1C1 =
"=SUMIF(Sheet1!R2C1:R11C1,RC[-2],Sheet1!R2C3:R11C3)"
.Range("C2").AutoFill Destination:=.Range("C2:C5"), Type:=xlFillDefault
End With
End Sub


Regards

Trevor
 
Back
Top