| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of Value for duplicate number
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of Value for duplicate number
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of Value for duplicate number
On Oct 1, 9:42 am, Len wrote:
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 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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of Value for duplicate number
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 "Len" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of Value for duplicate number
On Oct 1, 10:18 pm, Tom Ogilvy
wrote: 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 "Len" wrote: 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- Hide quoted text - - Show quoted text - 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i give unique number in set of duplicate number? | Excel Discussion (Misc queries) | |||
| Do not duplicate number | Excel Discussion (Misc queries) | |||
| duplicate a range a specified number of times | Excel Programming | |||
| How Excel can tell me i have entered a duplicate number | Excel Discussion (Misc queries) | |||
| duplicate number | Excel Discussion (Misc queries) | |||
Linear Mode
