Solved

group items together

Posted on 2014-02-25
7
106 Views
Last Modified: 2014-02-26
Can an expert assist me with grouping amounts together?

This is just a smal sample but there can be 1000s of items in the spreadsheet and I do not want to use Sub Total as this will mess up other work on the spreadsheet.

So, I start with this in Columns L and M. There is other data in columns A - K.

I want to put the totals of each Account Group in Column N

 Amount       Account
 30.00       6101
 80.00       7101
 50.00       7101
 20.00       9107
 12.00       1101
 10.00       1101
 4.00       0108
 4.00       0108
 40.00       0108
 10.00       0108
 30.00       0108
 6.00       0108
 20.00       0108
 40.00       0108
 42.00       T102
 85.00       T102
 32.00       T102
 54.00       T102
 51.00       T102


So it would look like this

 Amount       Account      Grp Tot
 30.00       6101       30.00
 80.00       7101      
 50.00       7101       130.00
 20.00       9107       20.00
 12.00       1101      
 10.00       1101       22.00
 4.00       0108      
 4.00       0108      
 40.00       0108      
 10.00       0108      
 30.00       0108      
 6.00       0108      
 20.00       0108      
 40.00       0108       154.00
 42.00       T102      
 85.00       T102      
 32.00       T102      
 54.00       T102      
 51.00       T102       264.00


Thanks
0
Comment
Question by:Jagwarman
  • 4
  • 3
7 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39884995
Hi,

pls try

=IF(B2<>B3,SUMIF($M$2:$M$20,M2,$L$2:$L$20),"")

Open in new window

Regards
ee20140225.xlsx
0
 

Author Comment

by:Jagwarman
ID: 39885031
Thanks Rgonzo1971 works great.

any chance you could look at my other post  VBA to Identify items that match
0
 

Author Comment

by:Jagwarman
ID: 39885782
Rgonzo1971

What I have just discovered is that the file that I need to use has some of the data as Text and some as numbers. When I change the 'Text' to 'Numbers' where the number has a leading zero it drops the 0.

Any ideas to resolve this.

Why is nothing simple?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39885816
Hi,

You could use a custom Format  like  0000

Regards
0
 

Author Comment

by:Jagwarman
ID: 39885946
does not appear to work
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39886278
Pls try like in this example
EE20140225.xlsx
0
 

Author Comment

by:Jagwarman
ID: 39888121
Bizarre Rgonzo it does not work for me. Please see attached file
Copy-of-EE20140225.xlsx
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question