Grouping Excel Data

[Mustang8103]

Hi,

I work with a very large data set on a monthly basis and I am hoping there is an easy way to set up a pivot table or some other function to group this data. Below is a small sample of the type of report I am working with. Is there some way to automatically group the data by Group ID and Name? For example, so it will show Group A has a balance of $103,500, Steve's Store and Billy's Bazaar will be grouped together and show a balance of $462,000 (because some of the businesses share a name and some share a group number) and Emily's Emporium has a balance of $64,000.

ID Name Balance
A Bob's Business $100,000.00
B Steve's Store $87,000.00
Steve's Store $250,000.00
A Mary's Market $3,500.00
B Billy's Bazaar $125,000.00
Emily's Emporium $64,000.00

For some reason our underwriting department only set up some of our loans with a Group ID and some with the business's legal name. I need to go through roughly 1200 loans each month to group them together and show outstanding balance. As our portfolio grows it is very time consuming to scroll through and match them up manually.

Is there any other information I can provide? Any help would be appreciated. Thank you in advance!

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Mustang8103]

Book1.xlsx

As I said the data set is very large so please see the attached dummy data.

I am trying to group together accounts that have the same Parent ID and/or Legal Name. That is why I need Bob and Mary grouped together (both group A) and Steve and Billy grouped together (Steve is the same name and one of Steve's ID's is the same as Billy's).

I recieve the data in a spreadsheet like the 'Data' tab and I need to group the accounts by ID and Name and list from largest to smallest like the 'Results' tab.

Right now I just scan each line of the data and manually group accounts together. This isn't a problem in the sample data I provided because there are only 6 accounts, however the data I actually work with is about 1,200 accounts and scanning each line is very tedious and take roughly half my day. I am hoping there is an easy way to pivot the data or some way to automate this system.

P.S. - the data is accounts that did not pay us in a given month, so the data changes on a monthly basis.

[newdoverman]

Maybe a solution like this will work for you. It involves creating a table for company names with appropriate IDs.