# Intercompany reports

#### D3Fella

##### New Member
Hi
I have a source table as follows:
 Entity 1 Entity 2 Amount abc jkl 150​ def mno 250​ ghi pqr 350​ jkl abc -100​ mno def -200​ pqr ghi -300​ abc mno 350​ mno abc -295​ def jkl 45​ jkl def -75​

I want to summarise it to look like this:
 Entity 1 Entity 2 Amount1 Amount2 abc jkl 150​ -100​ def mno 250​ -200​ ghi pqr 350​ -300​ abc mno 350​ -295​ def jkl 45​ -75​

Any help would be appreciated - if I haven't explained it clearly let me know.
Thanks

#### AhoyNC

##### Well-known Member
Try:
Copy formulas down.

xl2bb.xlam
ABCD
1Entity 1Entity 2Amount
2abcjkl150
3defmno250
4ghipqr350
5jklabc-100
6mnodef-200
7pqrghi-300
8abcmno350
9mnoabc-295
10defjkl45
11jkldef-75
12
13
14Entity 1Entity 2Amount1Amount2
15abcjkl
16defmno
17ghipqr
18abcmno
19defjkl
 Sheet1
Cell Formulas
Range(s)Formula
C15:C19C15=SUMPRODUCT((\$A\$2:\$A\$11=\$A15)*(\$B\$2:\$B\$11=\$B15)*(\$C\$2:\$C\$11))
D15:D19D15=SUMPRODUCT((\$B\$2:\$B\$11=\$A15)*(\$A\$2:\$A\$11=\$B15)*\$C\$2:\$C\$11)

#### DRSteele

##### Well-known Member
I suspect you want to use formulas to extract the unique combinations of Entity1 and Entity2 and then lookup the amounts, right?

#### D3Fella

##### New Member
Thanks , should have mentioned I need to generate Entity values in cols A and B also.

#### D3Fella

##### New Member
DRSteele, its tricky to explain : I want rows 2 and 5 in source data to be one row in summarised list, and so on.

#### DRSteele

##### Well-known Member
I am having a devil of a time trying to extract that list. I can't even do it with the new dynamic array functions in Excel 365 Insider.

Stay tuned - a revelation or stroke of insight might occur......

#### D3Fella

##### New Member
Thanks for the efforts DRSteele