{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Dataset","Filter Option","COUNTBLANK","Filter","IF","INDEX","COUNT","VBA","OFFSET Function"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":4,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Dataset","index":0,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,1,1,5],[1,14,1,18]]}
[]
[null,"Dataset Over view",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE","ME","CE",null,null,null,null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,84,83,null,null,null,null,null,null,null,null,"Jan","S2001",74,84,83]
[null,"Bob","S2003",78,85,78,null,null,null,null,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,90,78,null,null,null,null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",90,71,85,null,null,null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,76,70,null,null,null,null,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,88,72,null,null,null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,85,71,null,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"Filter Option","index":1,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,1,1,5],[1,14,1,18]]}
[]
[null,"Use of Filter Option",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE","ME","CE",null,null,null,null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,84,83,null,null,null,null,null,null,null,null,"Jan","S2001",74,84,83]
[null,"Bob","S2003",78,85,78,null,null,null,null,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,90,78,null,null,null,null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",90,71,85,null,null,null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,76,70,null,null,null,null,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,88,72,null,null,null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,85,71,null,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"COUNTBLANK","index":2,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,14,1,18],[1,1,1,6]]}
[]
[null,"Utilizing COUNTBLANK Function",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE","ME","CE","Skipped Cells",null,null,null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,84,83,{"$f":"COUNTBLANK(B5:F5)","v":0},null,null,null,null,null,null,null,"Jan","S2001",74,84,83]
[null,"Bob","S2003",null,85,78,{"$f":"COUNTBLANK(B6:F6)","v":1},null,null,null,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,90,78,0,null,null,null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",null,null,85,2,null,null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,76,70,0,null,null,null,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,88,72,0,null,null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,null,71,1,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"Filter","index":3,"n_rows":11,"n_cols":17,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,14,1,18],[1,1,1,7]]}
[]
[null,"Applying FILTER Function",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE",null,"Name","ID","EEE",null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,null,{"$f":"_xlfn._xlws.FILTER(B5:D11,(B5:B11<>\"\")*(C5:C11<>\"\")*(D5:D11<>\"\"))","v":"Smith"},"S2001",74,null,null,null,null,"Jan","S2001",74,84,83]
[null,"Bob",null,null,null,"Mark","S2004",88,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,null,"Cathy","S2007",73,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",null,null,"Milan","S2008",87,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,null,"Edward","S2010",88,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,null,null,null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,null,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"IF","index":4,"n_rows":11,"n_cols":17,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,1,1,5],[1,12,1,16]]}
[]
[null,"Using a Combination of IF, AND & ISBLANK Functions",null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE","ME","Remarks",null,null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,84,{"$f":"IF(AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),\"Blank\", \"Not-Blank\")","v":"Not-Blank"},null,null,null,null,null,null,"Jan","S2001",74,84,83]
[null,null,null,null,null,{"$f":"IF(AND(ISBLANK(B6), ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),\"Blank\", \"Not-Blank\")","v":"Blank"},null,null,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,90,"Not-Blank",null,null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",90,71,"Not-Blank",null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,72,"Not-Blank",null,null,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,88,"Not-Blank",null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,85,"Not-Blank",null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"INDEX","index":5,"n_rows":11,"n_cols":17,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,14,1,18],[1,1,1,7]]}
[]
[null,"Combining IFERROR, INDEX, SMALL, IF, ROW & ROWS Functions",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE",null,"Name","ID","EEE",null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,null,{"$f":"IFERROR(INDEX(B:B,SMALL(IF(B$5:B$11<>\"\",ROW(B$5:B$11)), ROWS(B$5:B5))), \"\")","v":"Smith"},{"$f":"IFERROR(INDEX(C:C,SMALL(IF(C$5:C$11<>\"\",ROW(C$5:C$11)), ROWS(C$5:C5))), \"\")","v":"S2001"},{"$f":"IFERROR(INDEX(D:D,SMALL(IF(D$5:D$11<>\"\",ROW(D$5:D$11)), ROWS(D$5:D5))), \"\")","v":74},null,null,null,null,"Jan","S2001",74,84,83]
[null,"Bob","S2003",78,null,{"$f":"IFERROR(INDEX(B:B,SMALL(IF(B$5:B$11<>\"\",ROW(B$5:B$11)), ROWS(B$5:B6))), \"\")","v":"Bob"},{"$f":"IFERROR(INDEX(C:C,SMALL(IF(C$5:C$11<>\"\",ROW(C$5:C$11)), ROWS(C$5:C6))), \"\")","v":"S2003"},{"$f":"IFERROR(INDEX(D:D,SMALL(IF(D$5:D$11<>\"\",ROW(D$5:D$11)), ROWS(D$5:D6))), \"\")","v":78},null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,null,{"$f":"IFERROR(INDEX(B:B,SMALL(IF(B$5:B$11<>\"\",ROW(B$5:B$11)), ROWS(B$5:B7))), \"\")","v":"Mark"},{"$f":"IFERROR(INDEX(C:C,SMALL(IF(C$5:C$11<>\"\",ROW(C$5:C$11)), ROWS(C$5:C7))), \"\")","v":"S2004"},{"$f":"IFERROR(INDEX(D:D,SMALL(IF(D$5:D$11<>\"\",ROW(D$5:D$11)), ROWS(D$5:D7))), \"\")","v":88},null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",90,null,{"$f":"IFERROR(INDEX(B:B,SMALL(IF(B$5:B$11<>\"\",ROW(B$5:B$11)), ROWS(B$5:B8))), \"\")","v":"Emma"},{"$f":"IFERROR(INDEX(C:C,SMALL(IF(C$5:C$11<>\"\",ROW(C$5:C$11)), ROWS(C$5:C8))), \"\")","v":"S2005"},{"$f":"IFERROR(INDEX(D:D,SMALL(IF(D$5:D$11<>\"\",ROW(D$5:D$11)), ROWS(D$5:D8))), \"\")","v":90},null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,null,{"$f":"IFERROR(INDEX(B:B,SMALL(IF(B$5:B$11<>\"\",ROW(B$5:B$11)), ROWS(B$5:B9))), \"\")","v":"Cathy"},{"$f":"IFERROR(INDEX(C:C,SMALL(IF(C$5:C$11<>\"\",ROW(C$5:C$11)), ROWS(C$5:C9))), \"\")","v":"S2007"},{"$f":"IFERROR(INDEX(D:D,SMALL(IF(D$5:D$11<>\"\",ROW(D$5:D$11)), ROWS(D$5:D9))), \"\")","v":73},null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,null,{"$f":"IFERROR(INDEX(B:B,SMALL(IF(B$5:B$11<>\"\",ROW(B$5:B$11)), ROWS(B$5:B10))), \"\")","v":"Milan"},{"$f":"IFERROR(INDEX(C:C,SMALL(IF(C$5:C$11<>\"\",ROW(C$5:C$11)), ROWS(C$5:C10))), \"\")","v":"S2008"},{"$f":"IFERROR(INDEX(D:D,SMALL(IF(D$5:D$11<>\"\",ROW(D$5:D$11)), ROWS(D$5:D10))), \"\")","v":87},null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,null,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"COUNT","index":6,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,14,1,18],[1,1,1,7]]}
[]
[null,"Merging IF, ROWS, COUNT, INDEX, SMALL, ROW & ROWS Functions",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE",null,"Name","ID","EEE",null,null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,null,{"$f":"IF(ROWS(B$5:B5)>COUNTA(B:B),\"\",INDEX(B:B,SMALL(IF(B$5:B$11<>\"\", ROW(B$5:B$11)),ROWS(B$5:B5))))","v":"Smith"},{"$f":"IF(ROWS(C$5:C5)>COUNTA(C:C),\"\",INDEX(C:C,SMALL(IF(C$5:C$11<>\"\", ROW(C$5:C$11)),ROWS(C$5:C5))))","v":"S2001"},{"$f":"IF(ROWS(D$5:D5)>COUNTA(D:D),\"\",INDEX(D:D,SMALL(IF(D$5:D$11<>\"\", ROW(D$5:D$11)),ROWS(D$5:D5))))","v":74},null,null,null,null,null,null,"Jan","S2001",74,84,83]
[null,null,null,null,null,{"$f":"IF(ROWS(B$5:B6)>COUNTA(B:B),\"\",INDEX(B:B,SMALL(IF(B$5:B$11<>\"\", ROW(B$5:B$11)),ROWS(B$5:B6))))","v":"Mark"},{"$f":"IF(ROWS(C$5:C6)>COUNTA(C:C),\"\",INDEX(C:C,SMALL(IF(C$5:C$11<>\"\", ROW(C$5:C$11)),ROWS(C$5:C6))))","v":"S2004"},{"$f":"IF(ROWS(D$5:D6)>COUNTA(D:D),\"\",INDEX(D:D,SMALL(IF(D$5:D$11<>\"\", ROW(D$5:D$11)),ROWS(D$5:D6))))","v":88},null,null,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark","S2004",88,null,{"$f":"IF(ROWS(B$5:B7)>COUNTA(B:B),\"\",INDEX(B:B,SMALL(IF(B$5:B$11<>\"\", ROW(B$5:B$11)),ROWS(B$5:B7))))","v":"Emma"},{"$f":"IF(ROWS(C$5:C7)>COUNTA(C:C),\"\",INDEX(C:C,SMALL(IF(C$5:C$11<>\"\", ROW(C$5:C$11)),ROWS(C$5:C7))))","v":"S2005"},{"$f":"IF(ROWS(D$5:D7)>COUNTA(D:D),\"\",INDEX(D:D,SMALL(IF(D$5:D$11<>\"\", ROW(D$5:D$11)),ROWS(D$5:D7))))","v":90},null,null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",90,null,{"$f":"IF(ROWS(B$5:B8)>COUNTA(B:B),\"\",INDEX(B:B,SMALL(IF(B$5:B$11<>\"\", ROW(B$5:B$11)),ROWS(B$5:B8))))","v":"Cathy"},{"$f":"IF(ROWS(C$5:C8)>COUNTA(C:C),\"\",INDEX(C:C,SMALL(IF(C$5:C$11<>\"\", ROW(C$5:C$11)),ROWS(C$5:C8))))","v":"S2007"},{"$f":"IF(ROWS(D$5:D8)>COUNTA(D:D),\"\",INDEX(D:D,SMALL(IF(D$5:D$11<>\"\", ROW(D$5:D$11)),ROWS(D$5:D8))))","v":73},null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy","S2007",73,null,{"$f":"IF(ROWS(B$5:B9)>COUNTA(B:B),\"\",INDEX(B:B,SMALL(IF(B$5:B$11<>\"\", ROW(B$5:B$11)),ROWS(B$5:B9))))","v":"Milan"},{"$f":"IF(ROWS(C$5:C9)>COUNTA(C:C),\"\",INDEX(C:C,SMALL(IF(C$5:C$11<>\"\", ROW(C$5:C$11)),ROWS(C$5:C9))))","v":"S2008"},{"$f":"IF(ROWS(D$5:D9)>COUNTA(D:D),\"\",INDEX(D:D,SMALL(IF(D$5:D$11<>\"\", ROW(D$5:D$11)),ROWS(D$5:D9))))","v":87},null,null,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan","S2008",87,null,{"$f":"IF(ROWS(B$5:B10)>COUNTA(B:B),\"\",INDEX(B:B,SMALL(IF(B$5:B$11<>\"\", ROW(B$5:B$11)),ROWS(B$5:B10))))","v":"Edward"},{"$f":"IF(ROWS(C$5:C10)>COUNTA(C:C),\"\",INDEX(C:C,SMALL(IF(C$5:C$11<>\"\", ROW(C$5:C$11)),ROWS(C$5:C10))))","v":"S2010"},{"$f":"IF(ROWS(D$5:D10)>COUNTA(D:D),\"\",INDEX(D:D,SMALL(IF(D$5:D$11<>\"\", ROW(D$5:D$11)),ROWS(D$5:D10))))","v":88},null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,null,null,null,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"VBA","index":7,"n_rows":9,"n_cols":19,"rows_kept":9,"cut":false,"hidden":false,"merged":[[1,1,1,5],[1,14,1,18]]}
[]
[null,"Applying VBA Code ",null,null,null,null,null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ID","EEE","ME","CE",null,null,null,null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith","S2001",74,84,83,null,null,null,null,null,null,null,null,"Jan","S2001",74,84,83]
[null,"Mark","S2004",88,90,78,null,null,null,null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma","S2005",90,71,85,null,null,null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Milan","S2008",87,88,72,null,null,null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward","S2010",88,85,71,null,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]

{"sheet":"OFFSET Function","index":8,"n_rows":11,"n_cols":15,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,1,1,4],[1,10,1,14]]}
[]
[null,"Using OFFSET Function",null,null,null,null,null,null,null,null,"Do It Yourself"]
[]
[null,"Name","ME",null,"Skipped Cells",null,null,null,null,null,"Name","ID","EEE","ME","CE"]
[null,"Smith",84,null,{"$f":"OFFSET($B$5,ROW(D1)*2-1,0)","v":"Bob"},null,null,null,null,null,"Jan","S2001",74,84,83]
[null,"Bob",85,null,{"$f":"OFFSET($B$5,ROW(D2)*2-1,0)","v":"Emma"},null,null,null,null,null,"Pam","S2003",78,85,78]
[null,"Mark",90,null,"Milan",null,null,null,null,null,"Jim","S2004",88,90,78]
[null,"Emma",71,null,null,null,null,null,null,null,"Sheldon","S2005",90,71,85]
[null,"Cathy",76,null,null,null,null,null,null,null,"Michael","S2007",73,76,70]
[null,"Milan",88,null,null,null,null,null,null,null,"Ron","S2008",87,88,72]
[null,"Edward",85,null,null,null,null,null,null,null,"Deisy","S2010",88,85,71]