Filtering a database by both rows and columns

{32 Comments}

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

RSS 32 Comments…

 Share your views
  1. HI
    Sir , the code attached in the work file in not working. its giving error.

  2. Just downloading the file and clickng on the button on the third sheet named Missing Observations.

    Compile error
    Cant file the project 0r library.

  3. 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.

  4. 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)

  5. Sir I have tried this on office 2007 and 2010 versions same issue. What might be the issue?

  6. Sub missing_observations()

  7. Sir i have requested to my friends as well. same error.

  8. First line is becoming yellow Sub missing_observations() and it is highlighting n.

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. 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.

  14. 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.

  15. How i can change the condition of missing observations i-e. 11.

  16. 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.

Leave a Comment

Your email address will not be published.

*