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.
<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>
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>