In range A1:BG2185 of this workbook, there is macro economic and demographic data for years from 1984 to 2009 (26 years) for 84 countries . Furthermore, there are 57 parameters being tracked for each of the 26 years for all 84 countries.
There are missing observations for some years across many parameters and many countries. If any country has more than 11 missing observations in any one parameter, then they should be depicted in the sheet named "Missing Observations". The "Missing Observations" sheet should show three columns:
1. Country Name
2. Parameter with more than 11 missing observations
3. Observations missing
HI
Sir , the code attached in the work file in not working. its giving error.
Hi,
It is working absolutely fine. I just downloaded the file and clicked on the blue button on the third sheet.
What different activity are you doing?
Just downloading the file and clickng on the button on the third sheet named Missing Observations.
Compile error
Cant file the project 0r library.
Hi,
I created the file in Excel 2013 but it should work on all Excel versions - it is a usual .xlsm file. Please try to open it on another computer of another version of MS Excel.
Hi,
I saved the file in compatibility mode (.xls format) and the macro still runs absolutely fine. Request someone else to download the file and check.
Sir I have checked it on other computers and with different office versions , but i am getting the same problem. and sir what about the data selection. countries having 11 obs missing should be deleted and the data on remaning countries should be selected. plz consider the second part as well.
Hi,
I cannot help now. It is working absolutely fine for me. Ask some colleague of yours to download the file from this link and run the macro.
Sir What about the Second Part
Please consider that as well.
Let's first try to resolve this. I am quite surprised that it is not working for you. I saved the file as an Excel 1997-2003 version file and the macro ran just fine (with the same speed and no errors)
Sir I have tried this on office 2007 and 2010 versions same issue. What might be the issue?
Hi,
On the VBA error message box, if there is a Debug button, click on it and let me know which line highlights in yellow.
Sub missing_observations()
Hi,
In the VBA code window, press F8 to step through every line of the code. On which line does it error out.
Sir i have requested to my friends as well. same error.
First line is becoming yellow Sub missing_observations() and it is highlighting n.
Sir I go the error. in the VBA , tolls menus , reference , i have have unchecked the one opition.
Now its working.
Plz consider the second part.
Hi,
Good to hear that it is working. First please confirm whether the solution is working absolutely fine. Post back problems, if any.
Sir the results are not as desired.
look the attached workfile with 4 countries with 6 variables.
https://skydrive.live.com/redir?resid=DD0BC148DF66DAC4!159&authkey=!AJ7uWMhxk0xzQoQ
in this workfile , for Armenia and Albania 11 observarions are missing so must the variables on which the number od observariosn are missing . but is is only listing Armenia and saying that 22 obs are missing.
Sir i thing I am unable to tell what i want , firstly i want to know how many observations are missing on any variable for every country.
wheather it is greather than 11 or less than 11.
and then if any variable have missing observations greater than 11 delete those countries . and this 11 may change from data to data.
Hi,
I am not proceeding on this any further unless you share your file with desired result for 3-4 countries.
Take your time - do not be in a hurry to post back. Show me the exact result you want.
in the sheet named Missing observations i have listed the missing observation of each variable according to country.
in the next sheet "Data Remaining after del 11 obs" I have data of remaining countries after deleting the countries with 11 observations. last sheet counts the number of countries selected in the sheet "Data Remaining after del 11 obs".
as in sheet "Data Remaining after del 11 obs" I have data on 2 countries . so it list 2 countries.
This is what i want.
Link of sample file
https://skydrive.live.com/redir?resid=DD0BC148DF66DAC4!159&authkey=!AJ7uWMhxk0xzQoQ
Hi,
You ay refer to my solution at this link
Refer to the Question and Dashboard sheet.
Thanks Sir for your reply. But there is error in the code , in sample workfile 11 observations are missing for the Albania on each variable. where 11 and 12 obs are missing for variables N and CE for Armenia. where as the new code is saying that 11 and 12 for another country.
Country Name Key Performance Indicators Observations missing
Albania N 11
Albania Y 11
Albania CE 11
Albania I 11
Albania G 11
Albania K 11
Argentina N 11
Argentina CE 12
Armenia N 22
Armenia Y 22
Armenia CE 22
Armenia I 22
Armenia G 22
Armenia K 22
Why this confusion. In the file which I just uploaded, there are no variables as N, Y or CE. I have run all the three macros on the file which I just uploaded and it is working just fine.
Just download the file and run all three macros and before you post back, recheck thoroughly.
Sir , To Check whether the code is working fine or not . I am run this on the sample of four countries.
The results are not OK for that.
Do the check on the file which I have uploaded - check those result for a sample of four countries
How i can change the condition of missing observations i-e. 11.
I will share that with you later - first check the result in the file which I uploaded.
The results are OK in the attached workfile but why they are giving error in the sample file. can you please tell me how to change the condition of missing obs i-e. 11 to any other number.
Hi.
Some changes will have to be made to the sample workfile. Share that file and I will also make a provision to change 11 to any number.
Link of sample file
https://skydrive.live.com/redir?resid=DD0BC148DF66DAC4!159&authkey=!AJ7uWMhxk0xzQoQ.
Hi,
Here is the solution on the file which you shared. In cell P7 of the Dashboard sheet, enter the "Threshold of missing observations for removing countries".
All macros are now running fine.