Need help creating a multi-variable formula with conditional logic

[mgreenfi]

On a reoccurring basis I'm given a large amount of raw date in Excel and asked to produce metrics on it. Some of the reports are repetitive while others are one time requests, all of them can be time consuming. To aid I'm trying to automate parts of this task but need help.

Below is a mocked out subset of data that I am given. Using the information in the Raw Data section I need to create what you see in the Desired Data section, which is a report that will tell me all the companies that we flagged as having borrowed sorted by date. The part that is stumping me is the the fact that the company names are never consistent so i need to first dynamically pull and list all unique company names. I then have to determine how many times on a given date that company had an action of 'Borrow' and display that sum, i.e Company XYZ was flagged with an action of 'Borrow' 2 times on 4 Dec.

	A	B	C	D	E
1	Raw Data
2	Company	Input	Output	Action	Date
3	ABC	Apple	Orange	buy	1-Dec
4	ABC	Apple	Orange	borrow	1-Dec
5	ABC	Pear	Apple	sell	1-Dec
6	XYZ	Pear	Apple	borrow	1-Dec
7	ABC	Apple	Orange	buy	2-Dec
8	LMN	Orange	Pear	sell	2-Dec
9	XYZ	Orange	Pear	borrow	2-Dec
10	XYZ	Orange	Pear	sell	2-Dec
11	ABC	Pear	Apple	borrow	4-Dec
12	LMN	Orange	Pear	borrow	4-Dec
13	XYZ	Apple	Pear	borrow	4-Dec
14	XYZ	Orange	Pear	borrow	4-Dec
15
16
17
18	Desired Data
19	Date	Company	Borrowers
20	1-Dec	ABC		1
21	1-Dec	XYZ		1
22	2-Dec	XYZ		1
23	4-Dec	ABC		1
24	4-Dec	LMN		1
25	4-Dec	XYZ		2

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

In A18 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$3:$A$14&$E$3:$E$14=""),IF($D$3:$D$14="borrow",MATCH($A$3:$A$14&$E$3:$E$14,$A$3:$A$14&$E$3:$E$14,0))),ROW($A$3:$A$14)-ROW($A$3)+1),1))

In A20 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$20:A20)>$A$18,"",INDEX($E$3:$E$14,SMALL(IF(FREQUENCY(IF(1-($A$3:$A$14&$E$3:$E$14=""),IF($D$3:$D$14="borrow",MATCH($A$3:$A$14&$E$3:$E$14,$A$3:$A$14&$E$3:$E$14,0))),ROW($A$3:$A$14)-ROW($A$3)+1),ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($A$20:A20))))

In B20 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$20:A20)>$A$18,"",INDEX($A$3:$A$14,SMALL(IF(FREQUENCY(IF(1-($A$3:$A$14&$E$3:$E$14=""),IF($D$3:$D$14="borrow",MATCH($A$3:$A$14&$E$3:$E$14,$A$3:$A$14&$E$3:$E$14,0))),ROW($A$3:$A$14)-ROW($A$3)+1),ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($A$20:A20))))

In C20 just enter and copy down:

=COUNTIFS($E$3:$E$14,$A20,$A$3:$A$14,$B20,$D$3:$D$14,"borrow")