Quick Excel question:
I have a large database of M&A transactions (date announced, target, acquiror, transaction value, LTM revenue, etc.) on one sheet. There's hundreds upon hundreds of transactions. One the second sheet, I want to set up six tables, each table showing the deals by a specific acquiror (consolidator in the industry; think Microsft, IBM, etc.) from sheet one. I know I can set up the data autofilter, but I want to be able to pull it into a different format than the data dump and have it automated (so I don't have to copy and paste and blah blah blah). There's a way to do it, and I have a feeling it has to do with arrays, and I am not too sure how to go about doing it.
Also, I'd like to set up a third tab that lists the 10 (or X) biggest deals since mm/dd/yyyy. So I would enter in a date in the sheet and use that to determine which deals meet the cutoff, and then pick the largest deals from the data. Again, another array.
I apologize for my lack of clarity; any help would be appreciated. Thanks.
Pivot tables are your savior.
Eh. Not much for using PivotTables. Isn't there a way to do it with arrays? Any help here would be great (aside from "Pivot tables are your savior.")
I am not well-versed with PivotTables, but it does not do what I am looking to do.
I am essentially looking for a way to filter data without running the data filter constantly. I want to have multiple sheets set up that have different parameters (i.e., one set up for acquirors listing their acquisitions that updates as I add deals, one set up for quarterly deals, etc.). Basically, I want to check an item in each deal (each deal is a row, with different information about the deal in a column), and if it meets specific criteria, I want to list the entire deal (again, say the criteria is the acquiror is Microsoft; I want to list each and every Microsoft deal in my dataset).
Pivot tables do exactly what you want to do. Just play with them as they are very easy to understand but may not be intuitive at first.
My issue is with getting the PivotTable to simply act as a filter. I understand what it can do (group data), but I have no idea how to use it to filter a list of data. It seems to pull it out of the rows I have it in.
Say I have a set of data... The columns go: Data Announced, Target, Acquiror, Transaction Value. I want to display all deals in which the acquiror was Microsoft. What steps do I take to display this data, but only so it shows the deals done by Microsoft?
Thanks for the help!
You should use access instead of Excel if you really want to find specific criteria within a list and create new lists from this...
Really? If I were going to do this, I'd just filter the data and copy and paste.
OK, so let me explain what I'm trying to do a bit more clear. I have a list of data. Each piece of data is numbered 1-XXX. All data is sorted by date (most recent deal is first). So a deal that happened today is 1, yesterday is 2, etc. I want to run an array around the small function so that it returns to me the smallest numbered deal with the acquirors name. There must be a way to do this using an array...
If all you want to do is this... just use a vlookup. It will return the first match.
=vlookup(microsoft,data_array,X_columns_to_ID_#,false)
I agree with this. Or another database-oriented program, really, like SAS.
PivotTables will do the job, but they will not update automatically if you change the underlying data (seems like a functionality you're interested in). You could deselect all companies.. and then manually select the 6 or so companies you're interested in seeing.
Arrays are typically used for quantitative manipulation involving multiple conditions across a range of data. For example, sum of all transactional data for Microsoft in a specific year. I've never used arrays as a "filter"... although I'd be interested if this was possible.
chron3k,
I think you may actually understand what I'm trying to do. I don't mind hitting "Refresh" on each table in the Pivot Table if there's a way to display the data in the way I want to display it; when I want to use it, I can just hit the four keys to refresh it.
What I've seen before is some form of function that looks at the data and returns the row number in which a deal is located. So what would happen if I were looking for MSFT deals... It would look at the set of data and return the smallest row in which Microsft was the acquiror (which would be identified using the SMALL function and k=1). Would run the same function again, this time k=2. This would return the row number in which the deal was in, and then I could index or vlookup or whatever based on the row (could just toss a column at the far left called ROW() to lookup off that).
I'm looking to return more than just the first one... I'd like to return the 2nd, and the 3rd, and the 4th, etc.
Ghettotastic solution: Copy your table with a few extra rows Paste links + formats Run Autofilter on reference table. Done. Make more sheets.
EDIT: Nevermind, see what you're saying. But I can't run a total TV or median revenue multiple off that, because it just hides rows.
I know there is an actual solution to this.
Nevermind... Figured it out. I'm an idiot... Forgot that when entering an array, you must hit CTRL+SHIFT+ENTER.
In case anyone is wondering, if you label each deal in the transaction sheet with the number 1 through whatever, you can do an array function using SMALL(IF(Acquiror=Name in Sheet,Return Row Count),1), it'll return the smallest row count. Do that again for the 2nd (change the 1 to 2, or link it to a cell), and keep going. Then just do a VLOOKUP or INDEX or whatever you'd like to do off of the number it returns.
Yeah I was messing with the small but couldn't quite get the syntax right. Glad you found it, happy grinding...
Ya, this makes it much easier to put together output tables off a large database of crap. Also makes chart-making much easier.
Quasi voluptates aut et voluptatem inventore laboriosam alias. Modi animi optio consectetur distinctio sit explicabo totam rerum. Tempore dignissimos voluptatem aut vitae repellendus natus ducimus omnis. Et quod sit voluptatem dolore. Reiciendis nam mollitia ut corrupti sed tempora.
In qui officia culpa voluptatem modi consequatur nesciunt. Nostrum repellendus maxime animi. Aspernatur aliquam rem officia vero non nulla rerum sequi. Praesentium et molestiae aliquam. Optio corrupti minus rerum asperiores. Rerum laudantium itaque rerum minima nisi sit. Consequatur dolor vel enim nostrum esse aut accusamus.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...