Classifying expenses

[Tigerexcel]

Classifying expenses.xlsx
	A	B	C	D	E	F	G	H
1	Amount	Details	Transfer out	Transfer in	Rent	Tax	Rates	Water
2	-$1,500	Transfer to other Bank	Transfer out
3	$1,000	Transfer from Bank		Transfer in
4	$12,000	Water City						Water City
5	$500	Rent Suburb			Rent Suburb
6	$3,000	Rent City			Rent City
7	$6,000	Rates City					Rates City
8	$3,000	Rates invoice City					Rates City
9	$500	Tax invoice Suburb				Tax Suburb
10
11
12		Summary	Balance
13		Transfer out	$1,500
14		Transfer in	$1,000
15		Rent Suburb	$500
16		Rent City	$3,000
17		Rates City	$9,000
18		Water 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.

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Amount	Details	Transfer out	Transfer in	Rent	Tax	Rates	Water
2	($1,500)	Transfer to other Bank	Transfer out
3	$1,000	Transfer from Bank		Transfer in
4	$12,000	Water City						Water City
5	$500	Rent Suburb			Rent Suburb
6	$3,000	Rent City			Rent City
7	$6,000	Rates City					Rates City
8	$3,000	Rates invoice City					Rates City
9	$500	Tax invoice Suburb				Tax Suburb
10
11
12		Summary	Balance
13		Transfer out	$1,500		1500
14		Transfer in	$1,000		1000
15		Rent Suburb	$500		500
16		Rent City	$3,000		3000
17		Rates City	$9,000		9000
18		Water City	$12,000		12000
19
Sheet1

Cell Formulas
Range	Formula
E13:E18	E13	=ABS(SUMPRODUCT(($C$2:$H$9=B13)*($A$2:$A$9)))

 

[Tigerexcel]

Thanks maabadi, if possible I need formulae to fill in C2 to H9.

 

[maabadi]

You want to numbers At range C2:H9, I thinks you want Extract summary based them.
Try this:

Example Sheet.xlsx
	A	B	C	D	E	F	G	H	I
1	Amount	Details	Transfer out	Transfer in	Rent	Tax	Rates	Water
2	-1500	Transfer to other Bank	$ (1,500.00)
3	1000	Transfer from Bank		$1,000.00
4	12000	Water City						$12,000.00
5	500	Rent Suburb			$ 500.00
6	3000	Rent City			$ 3,000.00
7	6000	Rates City					$6,000.00
8	3000	Rates invoice City					$3,000.00
9	500	Tax invoice Suburb				$ 500.00
10
Sheet1

Cell Formulas
Range	Formula
C2:H9	C2	=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]

You want to numbers At range C2:H9, I thinks you want Extract summary based them.
Try this:

Example Sheet.xlsx
	A	B	C	D	E	F	G	H	I
1	Amount	Details	Transfer out	Transfer in	Rent	Tax	Rates	Water
2	-1500	Transfer to other Bank	$ (1,500.00)
3	1000	Transfer from Bank		$1,000.00
4	12000	Water City						$12,000.00
5	500	Rent Suburb			$ 500.00
6	3000	Rent City			$ 3,000.00
7	6000	Rates City					$6,000.00
8	3000	Rates invoice City					$3,000.00
9	500	Tax invoice Suburb				$ 500.00
10
Sheet1

Cell Formulas
Range	Formula
C2:H9	C2	=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.