Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-01-2013, 01:16 AM
maxtymo maxtymo is offline Search for date and then apply mutliple search criteria in huge dataset Windows 7 64bit Search for date and then apply mutliple search criteria in huge dataset Office 2010 64bit
Novice
Search for date and then apply mutliple search criteria in huge dataset
 
Join Date: Dec 2013
Posts: 3
maxtymo is on a distinguished road
Default Search for date and then apply mutliple search criteria in huge dataset

Hi,


I am trying since couple of hours to construct a formula. However, unsuccessful. Do you guys have a solution? Itís for my Master Thesis.


My Problem:
I have 1000 firms that engaged in M&A activities. For those firms I need a Benchmark-Firm selected via Size (Market Capitalization) and Book-to-Market Value (BTMV) in the respective month in which the initial company engaged in M&A. All of those 1000 active Companies were active in different months spanning from 1997 to 2012.



In other words, I have 1000 firms and I would like to find another company for each initial company, which best fulfills two criteria in a specified month.



Criterion 1: The size (market capitalization) of the benchmark-firm needs to represent 70% to 130% of the size of the active firm. This criterion leads to a pre-selection, however there are still various potential benchmark-firms left.



Criterion 2: Out of the Benchmark-firms which are 70% to 130% of the size of the active firm, the final single benchmark-firm is found. This final selection is performed by choosing the benchmark-firm with the closest BTMV-ratio to the active firm.



From a datasets a pre-selection regarding Size is performed and in the next step the final selection is performed according to the BTMV ratio from another dataset. Both criterions need to be representing the month of the respective M&A activity. The matching between those two datasets (worksheets) can be performed by company name or a identifying code.


Short example:
Firm A is active in March 2001 and has a size of 100 and a BTMV ratio of 2 in March 2001. The size of the firms B,C,D is in the range of 70% to 130% in March 2001. Of these firms, firm D has the closest BTMV with 2,2 in March 2001.



The sample consists of 1000 active M&A companies and there are 2000 potential benchmark-firms, with values for each month from 1997 to 2012.
The size and the BTMV are given in two different worksheets with two common fields (firm name and firm code). In the end I require for further analysis just the name of the benchmark firm for each of the active firms.
It might be helpful to actually see the data, hence:

dropbox.com/s/h7bwcjrvjys5022/Forum.xlsx



To be fair, I wanted to mention that I have posted this problem in other forums as well. As I am a bit under time pressure and do not really have a lot of expereince with forums in general.
http://www.mrexcel.com/forum/excel-q...e-dataset.html
http://excelexperts.com/search-date-...arch-criterias
http://www.excelguru.ca/forums/showt...0351#post10351


Thank you guys so much!!!!
Max

Last edited by maxtymo; 12-01-2013 at 04:48 AM.
Reply With Quote
  #2  
Old 12-01-2013, 01:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Search for date and then apply mutliple search criteria in huge dataset Windows 7 64bit Search for date and then apply mutliple search criteria in huge dataset Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Please add links to your cross posts (see why in my sig) and upload your file to the forum. External links are potentially unsafe
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 12-01-2013, 04:52 AM
maxtymo maxtymo is offline Search for date and then apply mutliple search criteria in huge dataset Windows 7 64bit Search for date and then apply mutliple search criteria in huge dataset Office 2010 64bit
Novice
Search for date and then apply mutliple search criteria in huge dataset
 
Join Date: Dec 2013
Posts: 3
maxtymo is on a distinguished road
Default

ok I posted the links...However i dont know how to upload the excel file plus it might be to large anyways (6mb)(?)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search for date and then apply mutliple search criteria in huge dataset Display if search criteria is not met meppwc Excel 2 03-23-2013 10:48 AM
Looking for Windows Search app with ability to search by content gopher_everett Office 1 02-28-2013 09:23 PM
Search Folder sent to criteria markstro Outlook 0 12-20-2011 02:47 PM
Search and Replace - Clear Search box JostClan Word 1 05-04-2010 08:46 PM
Criteria in Outlook Search Nancy Grams Outlook 0 11-12-2009 03:03 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:57 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft