Database Analysis VBA

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