Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate account codes that total amount
This should be an easy one, but nothing is coming to me! I have an expense
sheet where the user will list account codes and the amount spent for that code. They can enter the same account code numerous times. I then have a summary box that I want to auto populate the account codes used and the total dollar amount spent for that code: A B C 06000 50400 $15 06000 50200 $50 06000 50400 $26 06100 50200 $100 I would like the summary box to show the following: J K L 06000 50400 $41 06000 50200 $50 06100 50200 $100 I cannot figure out the formula that will combine the duplicates and then total the amount. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate account codes that total amount
Have a look at Pivot Tables. Worth while learning because if you can come to
grips with them, you will find numerous uses for them in the future. -- Regards, OssieMac "MMM" wrote: This should be an easy one, but nothing is coming to me! I have an expense sheet where the user will list account codes and the amount spent for that code. They can enter the same account code numerous times. I then have a summary box that I want to auto populate the account codes used and the total dollar amount spent for that code: A B C 06000 50400 $15 06000 50200 $50 06000 50400 $26 06100 50200 $100 I would like the summary box to show the following: J K L 06000 50400 $41 06000 50200 $50 06100 50200 $100 I cannot figure out the formula that will combine the duplicates and then total the amount. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate account codes that total amount
Thank you, but I have tried a pivot table and it is not going to work the way
I need it to. Is there a formula that I can use instead? "OssieMac" wrote: Have a look at Pivot Tables. Worth while learning because if you can come to grips with them, you will find numerous uses for them in the future. -- Regards, OssieMac "MMM" wrote: This should be an easy one, but nothing is coming to me! I have an expense sheet where the user will list account codes and the amount spent for that code. They can enter the same account code numerous times. I then have a summary box that I want to auto populate the account codes used and the total dollar amount spent for that code: A B C 06000 50400 $15 06000 50200 $50 06000 50400 $26 06100 50200 $100 I would like the summary box to show the following: J K L 06000 50400 $41 06000 50200 $50 06100 50200 $100 I cannot figure out the formula that will combine the duplicates and then total the amount. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate account codes that total amount
You can concatenate the first 2 columns and then use a pivot table with only
2 columns of data and it works perfectly. You can then select all the pivot table and copy-paste special-values. Then move the totals over one column to make a blank column in between the concatenated labels and the totals and then apply text to columns to separate the first 2 coloumns again. Suggest that when you concatenate the 2 columns that you also insert a space that will then be the delimiter for the text to columns. Example:- =A2&" "&B2 -- Regards, OssieMac "MMM" wrote: Thank you, but I have tried a pivot table and it is not going to work the way I need it to. Is there a formula that I can use instead? "OssieMac" wrote: Have a look at Pivot Tables. Worth while learning because if you can come to grips with them, you will find numerous uses for them in the future. -- Regards, OssieMac "MMM" wrote: This should be an easy one, but nothing is coming to me! I have an expense sheet where the user will list account codes and the amount spent for that code. They can enter the same account code numerous times. I then have a summary box that I want to auto populate the account codes used and the total dollar amount spent for that code: A B C 06000 50400 $15 06000 50200 $50 06000 50400 $26 06100 50200 $100 I would like the summary box to show the following: J K L 06000 50400 $41 06000 50200 $50 06100 50200 $100 I cannot figure out the formula that will combine the duplicates and then total the amount. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicate UPC Codes in 1 Column | Excel Discussion (Misc queries) | |||
Account codes | Excel Worksheet Functions | |||
sales tax total amount from one cell amount to another cell | Excel Discussion (Misc queries) | |||
money deposited into an account with 6.5% interest-amount of year | New Users to Excel | |||
How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions |