How to distinguish data from same column to different columns

[bobbiehkmskan]

Good day,

I would like to solve this problem using formula, query, or pivot.

My company is issuing invoices. Most of them charge audit service fee and outlay together, and a few of them only charge outlay.
I cannot think of a logic to distinguish them in the format I expected.

Please help.

 

[jorismoerings]

Hi and welcome to MrExcel,

Try this and see if this helps:

Book1
	A	B	C	D	E	F
1
2	Invoice Date	Invoice Number	Reference	Account	Gross	Invoice Total
3	8-1-2021	72100012	E265/10320	50101 - Audit Ser Fee Rec'd-Audit	5.000,00	5.800,00
4	8-1-2021	72100012	E265/10320	32001 - Outlay Rec'd-T/E & Courier	200,00	5.800,00
5	8-1-2021	72100012	E265/10320	32002 - Outlay	300,00	5.800,00
6	8-1-2021	72100012	E265/10320	32007 - Outlay	300,00	5.800,00
7	20-1-2021	72100028	L420/10320	32001 - Outlay Rec'd-T/E & Courier	900,00	900,00
8
9
10	Invoice Date	Invoice Number	Reference	Fee	Outlay	Invoice Total
11	8-1-2021	72100012	E265/10320	5.000,00	800,00	5.800,00
12	20-1-2021	72100028	L420/10320	-	900,00	900,00
Sheet2

Cell Formulas
Range	Formula
A11:C12	A11	=UNIQUE($A$3:$C$7,FALSE)
D11:E12	D11	=SUMIFS($E$3:$E$7,$D$3:$D$7,"*"&D$10&"*",$C$3:$C$7,$C11,$B$3:$B$7,$B11,$A$3:$A$7,$A11)
F11:F12	F11	=SUM(D11:E11)
Dynamic array formulas.