Sum of Value for duplicate number

  • Thread starter Thread starter Len
  • Start date Start date
L

Len

Hi,

I'm having a difficulty to deal with the sum of value for all the
duplicate number, is there any excel function or excel vba can be
helped to solve this problem as per sheet1 and give the correct result
as shown in sheet2

e.g.

Sheet1
Col A B C D E
Date P/O No. Supplier Job No. RM $
04/01 07-0001 MrYS PP-06-013 150.00
04/01 07-0002 MrT PW-06-014 340.70
04/01 07-0003 MS RD PW-06-014 1,260.00
04/01 07-0004 MRS PV PP-04-001 20,510.50
04/01 07-0004 MRS PV PP-04-001 -
04/01 07-0005 MrH PW-06-014 12,000.00
04/01 07-0006 MS RFM PP-05-020 759.50
04/01 07-0007 S & H PP-06-012 1,070.00
04/01 07-0007 S & H PP-06-012 -
04/01 07-0007 S & H PP-06-012 -
04/01 07-0007 S & H PP-06-012 -
05/01 07-0008 Mr Hean PW-06-007 300.00
05/01 07-0009 Mr Pro Fib PW-06-014 23,000.00
05/01 07-0010 Ms M.E.A. VW-06-047 2,100.00

Total 61,490.70

Sheet2
Col A B
Job No. RM $
PP-06-013 150.00
PW-06-014 36,600.70
PP-04-001 20,510.50
PP-05-020 759.50
PP-06-012 1,070.00
PW-06-007 300.00
VW-06-047 2,100.00

Total 61,490.70

Thanks in advance
Best Rgards
Len
 
Sort Sheet1 by Job No. then create Data/Ssubtotals by Job No. and copy
subtotal rows to sheet2 and delete unnecessary columns.

Regards,
Stefi

„Len†ezt írta:
 
Hi,

I'm having a difficulty to deal with the sum of value for all the
duplicate number, is there any excel function or excel vba can be
helped to solve this problem as per sheet1 and give the correct result
as shown in sheet2

e.g.

Sheet1
Col A B C D E
Date P/O No. Supplier Job No. RM $
04/01 07-0001 MrYS PP-06-013 150.00
04/01 07-0002 MrT PW-06-014 340.70
04/01 07-0003 MS RD PW-06-014 1,260.00
04/01 07-0004 MRS PV PP-04-001 20,510.50
04/01 07-0004 MRS PV PP-04-001 -
04/01 07-0005 MrH PW-06-014 12,000.00
04/01 07-0006 MS RFM PP-05-020 759.50
04/01 07-0007 S & H PP-06-012 1,070.00
04/01 07-0007 S & H PP-06-012 -
04/01 07-0007 S & H PP-06-012 -
04/01 07-0007 S & H PP-06-012 -
05/01 07-0008 Mr Hean PW-06-007 300.00
05/01 07-0009 Mr Pro Fib PW-06-014 23,000.00
05/01 07-0010 Ms M.E.A. VW-06-047 2,100.00

Total 61,490.70

Sheet2
Col A B
Job No. RM $
PP-06-013 150.00
PW-06-014 36,600.70
PP-04-001 20,510.50
PP-05-020 759.50
PP-06-012 1,070.00
PW-06-007 300.00
VW-06-047 2,100.00

Total 61,490.70

Thanks in advance
Best Rgards
Len
Click to expand...

Based on your data, looks like a SumIf would work good. In Sheet2,
use something like this to get the sum.
=SUMIF(Sheet1!E:E,Sheet2!A2,Sheet1!F:F)
 
in B2 of Sheet2 put in the formula
=sumproduct(--(A2=Sheet1!$D$2:$D$500),Sheet1!$E$2:$E$500)

then drag fill down the column. Change the 500 to match the last row of
your data in sheet1.
 
in B2 of Sheet2 put in the formula
=sumproduct(--(A2=Sheet1!$D$2:$D$500),Sheet1!$E$2:$E$500)

then drag fill down the column. Change the 500 to match the last row of
your data in sheet1.
--
Regards,
Tom Ogilvy










- Show quoted text -
Click to expand...

Hi Tom, Stefi & JW,

Thanks for your advices and it works but the excel formula suggested
by JW, need to be modified as =SUMIF(Sheet1!D:D,Sheet2!A2,Sheet1!E:E)

However, I need one more step in which any excel functions where it
can help to obtain the result by showing in ascending order both the
job# and the corresponding sum of value for the duplicate number in
sheet2, otherwise I need to manually sort out the job# and apply the
excel formula to arrive at the sum of value.

Thanks again
Best Regards
Len
 
Back
Top