When working with databases I find the INDIRECT, MATCH and INDEX helpful. But using the MATCH and INDEX can be difficult if there are many tests. For example you may want to find the second occurrence rather than the first occurance. In addition when you read some data data from websites like marketwatch, you may want to convert 12B to 12,000,000,000 etc. The files below include functions that resolve these and other issues. The files that delete rows, columns and read in data are in the generic macro files. If you want access to the database files, send me an email at edwardbodmer@gmail.com and I will send you the resource library.
This page includes a number of techniques and examples for working with large data files.
| Subject | Excel File | Notes | Video Link | Chapter Reference | Page Reference | |||||||
| Macros for Data Analysis | Macro to Delete Sheets | Nigeria Broadcast 2015.xls | When there are many sheets | https://www.youtube.com/watch?v=QD6phfxUyDY | ||||||||
| Macros for Data Analysis | Finding Second Lookup Value with UDF Match | Nigeria Broadcast 2015.xls | Creating UDF with cells | https://www.youtube.com/watch?v=lK6PTpkisiY | ||||||||
| Macros for Data Analysis | Dropdown Box and Macro to Go to sheet | Nigeria Broadcast 2015.xls | Good when Many Sheets | https://www.youtube.com/watch?v=0Y2CUJxbBp4 | ||||||||
| Macros for Data Analysis | Macro to Read Files Quickly | Nigeria Broadcast 2015.xls | Must have only one file open | https://www.youtube.com/watch?v=MBWAYpMBdpA | ||||||||
| Adjusting Match for Data Analysis | Indirect with MATCH and INDEX | Match Functions.xlsm | Use of Indirect with Multiple Sheets | https://www.youtube.com/watch?v=35_nrmrwjAw | ||||||||
| Adjusting Match for Data Analysis | User Defined Match Function with Space in Sheet Names | Match Functions.xlsm | Dense Data Analysis | https://www.youtube.com/watch?v=AgK3idHcyak | ||||||||
| Adjusting Match for Data Analysis | Changing Date Formats when Dates in String | Match Functions.xlsm | Not the Date Format | https://www.youtube.com/watch?v=ZZNpvAVa98A | ||||||||
| Adjusting Match for Data Analysis | Creating Match Function to Work with Different Sheet | Match Functions.xlsm | Long Video (data analysis) | https://www.youtube.com/watch?v=KWdoD-wt4vM | ||||||||
| Adjusting Match for Data Analysis | Match Function with Truncated Name | Match Functions.xlsm | Works with Different Sheet | https://www.youtube.com/watch?v=B-7tBEM20PY | ||||||||
| Nigeria Daily Broadcast | Use of Offset together with Match and Index | Nigeria Summary Analysis | Flexible Range Names with Offset | https://www.youtube.com/watch?v=VkD2mLYPYyA | ||||||||
| Nigeria Daily Broadcast | Duplicating Graphs and Analysis with Match and Index | Nigeria Summary Analysis | Different Timing, Series, Dates | https://www.youtube.com/watch?v=s6puxvXF1SQ | ||||||||
| EIA 923 | Supply and Demand Analysis with Demand Curve | EIA 923.xlsm | ||||||||||
| EIA 923 | Simple Screening Analysis | EIA 923.xlsm | ||||||||||
| EIA 923 | Long-term Marginal Cost with Multiple Technologies | EIA 923.xlsm | ||||||||||
| EIA 923 | Capacity Price Value | EIA 923.xlsm | ||||||||||
| EIA 923 | Capacity Price Analysis | EIA 923.xlsm | ||||||||||
| EIA 923 | Working with EIA 923 Data Base | EIA 923.xlsm | ||||||||||
| EIA 923 | Merchant Plant Project Finance Model | EIA 923.xlsm | Discusses Cash Waterfall | https://www.youtube.com/watch?v=ONrb0f2851Y | ||||||||
| EIA 923 | Roll-up to Annual and Quarterly | EIA 923.xlsm | Not too long | https://www.youtube.com/watch?v=0rjzbLGzXq4 | ||||||||
| EIA 923 | Scenario and Tornado Diagram | EIA 923.xlsm | Uses VBA | |||||||||
| ……………………………………………………. | … | …………………………………………………………………………………………. | … | …………………………………………………………… | … | …………………………………………………. | …. | ………………………………………………………………………………………. | … | ……………………………………. | … | ………………………………… |
I am going to post these with explanations of the functions that you can make in excel to improve the database capabilities. If you would like the files in the meantime, send me an e-mail to edwardbodmer@gmail.com and request the resources.
Nigeria Analysis.xlsm
Daily Broadcast 2014 Fixed.xlsm
Nigeria Daily Statistics from 2012.xlsm
Daily Broadcast 2015.xlsm
Daily Broadcast 2014.xlsm
Nigeria Daily Statistics From 2010.xlsm
Daily Broadcast 2015 Fixed.xlsm