Filtering a database by both rows and columns

{ 34 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

Leave a Comment Cancel reply

Your email address will not be published.

*

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

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

    • 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 go the error. in the VBA , tolls menus , reference , i have have unchecked the one opition.
    Now its working.

    Plz consider the second part.

  • 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

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

  • 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,

      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.

  • Hi Ashish,
    In the linked file, Firstly, I want to filter the subject codes based on the dropdown selection in X1. the selected code is not found, nothing will show.

    Secondly, I will select a Session from the W1 dropdown, eg, 2015-2016, then the already filtered data (of code 101) will be refiltered according to session selection.
    File Link
    https://1drv.ms/x/s!ApksyGEt2vElhwcQD6-3O5yLWsYP

    Regards
    Fazlul

    • Hi,

      Your data is not well structured to answer your questions. While columns A:J should stay as is, columns K:W should be transformed into 2 columns with all subjects in one column and subject codes in another. Once the data is in that form, you can easily filter the dataset and get your result. Use the “Unpivot other columns” feature of Power Query to reorganize your data.