Classifying expenses

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Classifying expenses.xlsx
ABCDEFGH
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2-$1,500Transfer to other Bank Transfer out
3$1,000Transfer from BankTransfer in
4$12,000Water CityWater City
5$500Rent SuburbRent Suburb
6$3,000Rent CityRent City
7$6,000Rates CityRates City
8$3,000Rates invoice CityRates City
9$500Tax invoice SuburbTax Suburb
10
11
12SummaryBalance
13Transfer out$1,500
14Transfer in$1,000
15Rent Suburb$500
16Rent City$3,000
17Rates City$9,000
18Water City$12,000
Sheet1

Desired output is in C2 to H9. Ultimately would like to have a summary as shown. The problem I'm running into is that the Detail field can vary but I'd need formulae/functions to pick up variations such as Rates City or Rates Invoice City. The details do come in an inconsistent manner so could also have something like Invoice 234 Rates (Jan to Mar) City.
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,809
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Book1
ABCDEFGHI
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2($1,500)Transfer to other BankTransfer out
3$1,000Transfer from BankTransfer in
4$12,000Water CityWater City
5$500Rent SuburbRent Suburb
6$3,000Rent CityRent City
7$6,000Rates CityRates City
8$3,000Rates invoice CityRates City
9$500Tax invoice SuburbTax Suburb
10
11
12SummaryBalance
13Transfer out$1,5001500
14Transfer in$1,0001000
15Rent Suburb$500500
16Rent City$3,0003000
17Rates City$9,0009000
18Water City$12,00012000
19
Sheet1
Cell Formulas
RangeFormula
E13:E18E13=ABS(SUMPRODUCT(($C$2:$H$9=B13)*($A$2:$A$9)))
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks maabadi, if possible I need formulae to fill in C2 to H9.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,809
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You want to numbers At range C2:H9, I thinks you want Extract summary based them.
Try this:
Example Sheet.xlsx
ABCDEFGHI
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2-1500Transfer to other Bank$ (1,500.00)
31000Transfer from Bank $1,000.00
412000Water City $12,000.00
5500Rent Suburb $ 500.00
63000Rent City $ 3,000.00
76000Rates City $6,000.00
83000Rates invoice City $3,000.00
9500Tax invoice Suburb $ 500.00
10
Sheet1
Cell Formulas
RangeFormula
C2:H9C2=IF(COLUMN()-2=MATCH(IF(LEFT($B2,FIND(" ",$B2)-1)="transfer",LEFT($B2,FIND(" ",$B2,FIND(" ",$B2)+2)-1),LEFT($B2,FIND(" ",$B2)-1)),CHOOSE({1,2,3,4,5,6},"Transfer to","transfer from","rent","tax","rates","Water"),0),$A2,"")
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You want to numbers At range C2:H9, I thinks you want Extract summary based them.
Try this:
Example Sheet.xlsx
ABCDEFGHI
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2-1500Transfer to other Bank$ (1,500.00)
31000Transfer from Bank $1,000.00
412000Water City $12,000.00
5500Rent Suburb $ 500.00
63000Rent City $ 3,000.00
76000Rates City $6,000.00
83000Rates invoice City $3,000.00
9500Tax invoice Suburb $ 500.00
10
Sheet1
Cell Formulas
RangeFormula
C2:H9C2=IF(COLUMN()-2=MATCH(IF(LEFT($B2,FIND(" ",$B2)-1)="transfer",LEFT($B2,FIND(" ",$B2,FIND(" ",$B2)+2)-1),LEFT($B2,FIND(" ",$B2)-1)),CHOOSE({1,2,3,4,5,6},"Transfer to","transfer from","rent","tax","rates","Water"),0),$A2,"")
Thanks Maabadi, I will give this a try, much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,677
Messages
5,637,735
Members
416,981
Latest member
PLonchar

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
Top