Intercompany reports

D3Fella

New Member
Joined
Nov 22, 2019
Messages
4
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try:
Copy formulas down.

Book1
ABCD
1Entity 1Entity 2Amount
2abcjkl150
3defmno250
4ghipqr350
5jklabc-100
6mnodef-200
7pqrghi-300
8abcmno350
9mnoabc-295
10defjkl45
11jkldef-75
12
13
14Entity 1Entity 2Amount1Amount2
15abcjkl150-100
16defmno250-200
17ghipqr350-300
18abcmno350-295
19defjkl45-75
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
I suspect you want to use formulas to extract the unique combinations of Entity1 and Entity2 and then lookup the amounts, right?
 
Upvote 0
Thanks , should have mentioned I need to generate Entity values in cols A and B also.
 
Upvote 0
DRSteele, its tricky to explain : I want rows 2 and 5 in source data to be one row in summarised list, and so on.
 
Upvote 0
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......
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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
Back
Top