# Need help creating a multi-variable formula with conditional logic

#### mgreenfi

##### New Member
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>

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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")

Replies
3
Views
676
Replies
3
Views
402
Replies
1
Views
269
Replies
7
Views
345
Replies
2
Views
361

1,196,516
Messages
6,015,678
Members
441,915
Latest member
sm Hussaini

### 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.

### Which adblocker are you using?

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

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