Intercompany reports

D3Fella

New Member
Joined
Nov 22, 2019
Messages
4
Office Version
365
Platform
Windows
Hi
I have a source table as follows:
Entity 1 Entity 2Amount
abcjkl
150​
defmno
250​
ghipqr
350​
jklabc
-100​
mnodef
-200​
pqrghi
-300​
abcmno
350​
mnoabc
-295​
defjkl
45​
jkldef
-75​

I want to summarise it to look like this:
Entity 1 Entity 2Amount1Amount2
abcjkl
150​
-100​
defmno
250​
-200​
ghipqr
350​
-300​
abcmno
350​
-295​
defjkl
45​
-75​

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,308
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
Joined
Mar 31, 2015
Messages
2,016
Office Version
365
Platform
Windows
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
Joined
Nov 22, 2019
Messages
4
Office Version
365
Platform
Windows
Thanks , should have mentioned I need to generate Entity values in cols A and B also.
 

D3Fella

New Member
Joined
Nov 22, 2019
Messages
4
Office Version
365
Platform
Windows
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
Joined
Mar 31, 2015
Messages
2,016
Office Version
365
Platform
Windows
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......
 

Forum statistics

Threads
1,077,895
Messages
5,337,054
Members
399,120
Latest member
Sravankumar

Some videos you may like

This Week's Hot Topics

Top