Forum Discussion
Need help with Excel formula or VBA to list same data found with a sum of a related field
I am trying to do something that I think is more advanced than I am :-) I have an Excel sheet that I made to a form that I am using Index/Match, lists and basic formulas to drive the data. The form is being used to calculate company credit card transactions and each transaction needs to be linked to one or more "Org codes". Individual purchased items, grouped into similar items (by user) will then need to be linked with an "object code" which is the category of the item(s) purchased. In addition, if the "Org Code" is divided into 2 or 3 pieces, each piece will be assigned a percent of 100% that will then be carried to the items purchased/"Object Code". So the scenario would be as follows.
A transaction is made at a store to support two buildings. Building 1 and Building 2.
Building 1 needs 100% of the transaction purchased for it allocated to it, but needs to be split. Building 1 has two floors, 1a and 1b. 1a needs 55% of the transaction(s) and 1b needs 45% of the transaction(s)
Building 2 needs 100% of the transactions purchased for it allocated to it
Two transactions are made for both buildings, Building 1 (inclcuding 1a and 1b) and Building 2 at the same store
Transactions
- Transaction 1 - Object Code Book Purchase (15 books) - $450
- Transaction 2 - Object Code Small Tools (10 hammers) - $200
Building Transactions
- Building 1a -55%
- For building 1a: Tx1 - 10 books: $300 = $165 (55% of $300)
- For building 1a: Tx2 - 10 hammers: $200 = $110 (55% of $200)
- Building 1b-45%
- Transaction 1b - Tx1 - 10 books: $300 = - $135 (45% of $200)
- Transaction 1b - Tx2 - 10 hammers - $90 (45% of $200)
- Building 2 - 100%
- Transaction 1c - 5 books: $150 = $150 (100% of $150)
The user has the option of multiple Org Codes with many having split percentages and up to 4 object codes for each org code. No transaction will be the same with the same criteria.
here's an image of the "form" https://imgur.com/a/J4ySYXa
What I need is to have a formula or VBA script scan column F (the Object Code column) and in another place in the sheet, group the ones that are the same as one entry. For example, if there are 3 "Subscriptions and Books", then I would see a cell with "Subscriptions and Books" entered once.
Then next to the cell with the Object Code, I would add up all the items in column I (% of Org Code) that match to the same object code. So as in the image, Subscriptions and Books would be in one cell and in the next cell, it would show "$175", which is the sum of all the % of Org Code that are in the object code Subscriptions and Books.
Then I'd want it to do the same for whatever Object Code is next, then next until all the shown object codes are displayed with the total of the % org code next to each.
A simple table would work such as:
| Object Code | % of Org Code |
|---|---|
| Subscriptions and Books | $175 |
| Training and Education | $1500 |
I have tried index/match, index/match with sumif, Xlookup, Vlookup. Problems I am running into are that when I select the range, Excel seems to get confused with the text and blank fields. Also I'm not sure how to show the one Object code when there are multiple. I hope this makes more sense.
2 Replies
You can organize and summarize your Excel data by grouping unique "Object Codes" and calculating the total of related values using either a PivotTable or a combination of formulas like UNIQUE and SUMIF.
First, make sure your data is well-structured with clear headers such as "Object Code" and "% of Org Code."
If you're using a PivotTable, select your data range, go to the "Insert" tab, and choose "PivotTable." Place "Object Code" in the Rows area and "% of Org Code" in the Values area to automatically get a clean summary of each code with its corresponding total.
If you prefer using formulas (especially in Excel 365 or 2019), you can use the UNIQUE function to list all distinct Object Codes.
Then, next to each unique entry, apply a SUMIF formula to total up the values from the "% of Org Code" column that match each Object Code.
This method provides a dynamic and clear summary of how much is allocated per object category. Adjust ranges according to your actual data to ensure accurate results.
From your example and description, a simple formula-based approach can work.
Formula-based (No VBA) – Using UNIQUE + SUMIF (Excel 365)
F2:F100 = Object Code
I2:I100 = Amounts (the "% of Org Code" applied to transaction)
The output summary table will be in columns K and L.
In K2:
=UNIQUE(F2:F100)
In L2:
=SUMIF(F2:F100, K2, I2:I100)
Then drag down.
Wrap it in SORT() if you want it alphabetically sorted:
=SORT(UNIQUE(F2:F100))
Of course you can use VBA too…your choice
VBA Script to Auto-Summarize Object Codes
This is a great option if:
You want a "clean button click", or you don't want formulas all over your sheet, or you want to paste in new data and auto-refresh the summary.
Sub SummarizeObjectCodes() Dim ws As Worksheet Dim lastRow As Long Dim dict As Object Dim objCode As String Dim amount As Double Dim i As Long Dim outRow As Long Set ws = ActiveSheet Set dict = CreateObject("Scripting.Dictionary") ' Identify last row with data lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row ' Loop through object codes For i = 2 To lastRow ' assuming headers in row 1 objCode = Trim(ws.Cells(i, "F").Value) If objCode <> "" Then amount = ws.Cells(i, "I").Value If dict.exists(objCode) Then dict(objCode) = dict(objCode) + amount Else dict.Add objCode, amount End If End If Next i ' Output summary starting from column K outRow = 2 ws.Cells(1, "K").Value = "Object Code" ws.Cells(1, "L").Value = "% of Org Code (Total)" For Each objCode In dict.Keys ws.Cells(outRow, "K").Value = objCode ws.Cells(outRow, "L").Value = dict(objCode) outRow = outRow + 1 Next objCode MsgBox "Object Code Summary Generated!", vbInformation End Sub 'Code is untested back up your file first.Don’t forget to Save the file as .xlsm
My answers are voluntary and without guarantee!
Hope this will help you.
