Consolidate duplicate Names

datastudent

datastudent

New Member
Joined
Sep 7, 2021
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hi!

I have a table where I used the filter function (column AC:AJ). Then a second table where I want the category on the first table to show and how much it cost. I would like to ask for help on how I can combine the duplicate names in my second table (column AL)?

Income & Expense Tracker.xlsx
ACADAEAFAGAHAIAJAKALAM
1
2MONTHYEARDATETYPECATEGORYAMOUNTDESCRIPTIONCATEGORYAMOUNT
3March20243/15/2024ExpensePlywood₱3,000.000Plywood3,000.00
4March20243/15/2024ExpenseCar Maintenance₱3,000.000Car Maintenance3,000.00
5March20243/15/2024ExpenseCat Food₱1,270.00dry and 3 can of wet foodCat Food1,270.00
6March20243/22/2024ExpenseRenew Car Register₱3,060.000Renew Car Register3,060.00
7March20243/21/2024ExpenseGas₱300.000Gas300.00
8March20243/22/2024ExpenseDog Grooming₱530.0030 for the diaperDog Grooming530.00
9March20243/24/2024ExpenseSkin Care₱300.000Skin Care478.00
10March20243/25/2024ExpenseIpad Loan₱6,100.000Ipad Loan6,100.00
11March20243/26/2024ExpenseInternet₱1,500.000Internet1,500.00
12March20243/26/2024ExpenseCar Loan₱12,200.000Car Loan12,200.00
13March20243/26/2024ExpenseSt. Peter₱2,300.00100 for DRNSt. Peter2,300.00
14March20243/26/2024ExpenseSkin Care₱178.000Skin Care478.00
15- 
16- 
Monthly Overview
Cell Formulas
RangeFormula
AC3:AJ14AC3=FILTER(TRANSACTIONS,(TRANSACTIONS[TYPE]=C11)*(TRANSACTIONS[MONTH]=E7)*(TRANSACTIONS[YEAR]=E8))
AL3:AL16AL3=IF(AG3="","-",AG3)
AM3:AM16AM3=IF(AG3<>"",SUMIF($AG:$AG,AL3,$AI:$AI),"")
Dynamic array formulas.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
How about
Fluff.xlsm
ACADAEAFAGAHAIAJAKALAM
1
2
3March20243/15/2024ExpensePlywood₱30000Plywood3000
4March20243/15/2024ExpenseCar Maintenance₱30000Car Maintenance3000
5March20243/15/2024ExpenseCat Food₱1270dry and 3 can of wet foodCat Food1270
6March20243/22/2024ExpenseRenew Car Register₱30600Renew Car Register3060
7March20243/21/2024ExpenseGas₱3000Gas300
8March20243/22/2024ExpenseDog Grooming₱53030 for the diaperDog Grooming530
9March20243/24/2024ExpenseSkin Care₱3000Skin Care478
10March20243/25/2024ExpenseIpad Loan₱61000Ipad Loan6100
11March20243/26/2024ExpenseInternet₱15000Internet1500
12March20243/26/2024ExpenseCar Loan₱122000Car Loan12200
13March20243/26/2024ExpenseSt. Peter₱2300100 for DRNSt. Peter2300
14March20243/26/2024ExpenseSkin Care₱1780
Data
Cell Formulas
RangeFormula
AC3:AJ14AC3=A3:H14
AL3:AL13AL3=UNIQUE(INDEX(AC3#,,5))
AM3:AM13AM3=SUMIFS(AI:AI,AG:AG,AL3#)
Dynamic array formulas.
 
Upvote 1
Solution
Fluff said:
How about
Fluff.xlsm
ACADAEAFAGAHAIAJAKALAM
1
2
3March20243/15/2024ExpensePlywood₱30000Plywood3000
4March20243/15/2024ExpenseCar Maintenance₱30000Car Maintenance3000
5March20243/15/2024ExpenseCat Food₱1270dry and 3 can of wet foodCat Food1270
6March20243/22/2024ExpenseRenew Car Register₱30600Renew Car Register3060
7March20243/21/2024ExpenseGas₱3000Gas300
8March20243/22/2024ExpenseDog Grooming₱53030 for the diaperDog Grooming530
9March20243/24/2024ExpenseSkin Care₱3000Skin Care478
10March20243/25/2024ExpenseIpad Loan₱61000Ipad Loan6100
11March20243/26/2024ExpenseInternet₱15000Internet1500
12March20243/26/2024ExpenseCar Loan₱122000Car Loan12200
13March20243/26/2024ExpenseSt. Peter₱2300100 for DRNSt. Peter2300
14March20243/26/2024ExpenseSkin Care₱1780
Data
Cell Formulas
RangeFormula
AC3:AJ14AC3=A3:H14
AL3:AL13AL3=UNIQUE(INDEX(AC3#,,5))
AM3:AM13AM3=SUMIFS(AI:AI,AG:AG,AL3#)
Dynamic array formulas.
Click to expand...
Hi @Fluff ,
I tried the formula for AL3 but I'm getting a #SPILL! :(
 
Upvote 0
You're using the FILTER function (exclusive to Ex365) while your profile indicates that you're using Ex2016. Which one are you actually using?
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
How to sum with multiple criteria in 2 different columns? - census data
Replies
1
Views
166
hagia_sofia
hagia_sofia
T
  • Solved
Problem With Multi Filter
Replies
1
Views
174
hagia_sofia
hagia_sofia
X
  • Solved
Problems when i add new rows in table via userform. Formulas wont get taken over and cell formats are changing
Replies
7
Views
569
Anthony47
A
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
571
Greenbehindthecells
G
T
  • Question Question
SUMIFS or SUMPRODUCT?
Replies
1
Views
386
awoohaw
awoohaw

Forum statistics

Threads
1,223,934
Messages
6,175,487
Members
452,647
Latest member
MatthewBiersay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top