{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["Home","SalesData","UsingCF","UsingCOUNTIFS","UsingPivots","UsingAdvancedFilters","BasedOnACondition","RemoveDuplicates","UsingVBASingleColumn","UsingVBARange"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":1},"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":"Home","index":0,"n_rows":23,"n_cols":13,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Excel Tricks Tutorial"]
[null,"8 COOL WAYS TO IDENTIFY & REMOVE"]
[null,"DUPLICATES IN EXCEL"]
[]
[null,"http://datacycleanalytics.com/8-cool-ways-to-identify-and-remove-duplicates-in-excel/"]
[null,null,null,null,null,null,null,null,null,null,null,null,"About Data Cycle Analytics"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Our mission at DCA is to collaborate with individuals and organizations"]
[null,"When working with data in Excel, it is very likely you will encounter duplication. How do",null,null,null,null,null,null,null,null,null,null,"to enable them leverage on the business data at hand for decisions."]
[null,"you deal with duplicates in Excel? Dealing with duplicates in Excel is 2-stage process –first",null,null,null,null,null,null,null,null,null,null,"Through our public and tailored in-house courses, we focus on practical"]
[null,"identify if you have duplicates, and secondly remove the duplicate records once you",null,null,null,null,null,null,null,null,null,null,"ways to efficiently work with data in MS Excel, MS Power BI among other tools."]
[null,"ascertain they don’t need to be in your list. This article discusses 8 cool ways to identify and"]
[null,"remove duplicates in Excel.",null,null,null,null,null,null,null,null,null,null,"We move from \"click here\" kind of training to scenario based tutorials that"]
[null,null,null,null,null,null,null,null,null,null,null,null,"illustrate how you can apply the various functions and features in Excel."]
[]
[null,"KEY LEARNINGS",null,null,null,null,null,null,null,null,null,null,"Do you have data and you are wondering how best to analyze, present and"]
[null,"E","Using conditional formatting",null,null,null,null,null,null,null,null,null,"share? We provide Business Intelligence consultancy where we build customized"]
[null,"E","COUNTIFS",null,null,null,null,null,null,null,null,null,"business dashboards that management can rely on for decision making."]
[null,"E","Using Pivot Tables"]
[null,"E","Applying Advanced Filters",null,null,null,null,null,null,null,null,null,"Talk to us today: consulting@datacycleanalytics.com"]
[null,"E","Remove dupes based on other columns"]
[null,"E","VBAapproach to duplicates"]

{"sheet":"SalesData","index":1,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Date","Customer","Product","Amount"]
[644834,"2016-02-23","Top shop","Peach",395]
[144895163,"2016-02-22","BAC Stores","Mushrooms",618]
[23990986,"2016-02-20","Quick stop shop","Banana",389]
[24928766,"2016-02-25","Top shop","PawPaw",584]
[42366779,"2016-02-22","Quncy Top Shop","Asparagus",282]
[53108248,"2016-02-21","F&G Store","Lemon",608]
[55152212,"2016-02-24","BAC Stores","Low fat",392]
[63545154,"2016-02-24","B Fruit","Strawberries",255]
[103917196,"2016-02-23","Quncy Top Shop","Peach",434]
[71102611,"2016-02-24","Top shop","Peach",415]
[86973202,"2016-02-21","Wong-Kee & Sons","Asparagus",339]
[95047456,"2016-02-26","Country Food Store","Asparagus",379]
[144895163,"2016-02-22","BAC Stores","Mushrooms",618]
[124787633,"2016-02-23","BAC Stores","Peanuts",456]
[103917196,"2016-02-23","Quncy Top Shop","Peach",434]
[124402443,"2016-02-21","Top shop","Banana",733]
[126881633,"2016-02-24","Wong-Kee & Sons","Peanuts",771]
[53108248,"2016-02-21","F&G Store","Lemon",608]
[151438681,"2016-02-24","Quncy Top Shop","Peach",421]

{"sheet":"UsingCF","index":2,"n_rows":20,"n_cols":6,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Customer","Date","Product","Amount","Helper column"]
[644834,"Top shop","2016-02-23","Peach",395,{"$f":"CONCATENATE(A2,B2,C2,D2,E2)","v":"644834Top shop42423Peach395"}]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,{"$f":"CONCATENATE(A3,B3,C3,D3,E3)","v":"144895163BAC Stores42422Mushrooms618"}]
[23990986,"Quick stop shop","2016-02-20","Banana",389,"23990986Quick stop shop42420Banana389"]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,{"$f":"CONCATENATE(A5,B5,C5,D5,E5)","v":"103917196Quncy Top Shop42423Peach434"}]
[24928766,"Top shop","2016-02-25","PawPaw",584,"24928766Top shop42425PawPaw584"]
[42366779,"Quncy Top Shop","2016-02-22","Asparagus",282,"42366779Quncy Top Shop42422Asparagus282"]
[53108248,"F&G Store","2016-02-21","Lemon",608,"53108248F&G Store42421Lemon608"]
[55152212,"BAC Stores","2016-02-24","Low fat",392,"55152212BAC Stores42424Low fat392"]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,{"$f":"CONCATENATE(A10,B10,C10,D10,E10)","v":"144895163BAC Stores42422Mushrooms618"}]
[63545154,"B Fruit","2016-02-24","Strawberries",255,"63545154B Fruit42424Strawberries255"]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,"103917196Quncy Top Shop42423Peach434"]
[71102611,"Top shop","2016-02-24","Peach",415,"71102611Top shop42424Peach415"]
[86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339,"86973202Wong-Kee & Sons42421Asparagus339"]
[95047456,"Country Food Store","2016-02-26","Asparagus",379,"95047456Country Food Store42426Asparagus379"]
[124787633,"BAC Stores","2016-02-23","Peanuts",456,"124787633BAC Stores42423Peanuts456"]
[124402443,"Top shop","2016-02-21","Banana",733,"124402443Top shop42421Banana733"]
[126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771,"126881633Wong-Kee & Sons42424Peanuts771"]
[53108248,"F&G Store","2016-02-21","Lemon",608,"53108248F&G Store42421Lemon608"]
[151438681,"Quncy Top Shop","2016-02-24","Peach",421,"151438681Quncy Top Shop42424Peach421"]

{"sheet":"UsingCOUNTIFS","index":3,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Customer","Date","Product","Amount","Countif","Subsequent"]
[644834,"Top shop","2016-02-23","Peach",395,{"$f":"COUNTIFS($A$2:$A$20,A2)","v":1},{"$f":"COUNTIFS($A$2:A2,A2)","v":1}]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,{"$f":"COUNTIFS($A$2:$A$20,A3)","v":2},{"$f":"COUNTIFS($A$2:A3,A3)","v":1}]
[23990986,"Quick stop shop","2016-02-20","Banana",389,1,{"$f":"COUNTIFS($A$2:A4,A4)","v":1}]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,2,{"$f":"COUNTIFS($A$2:A5,A5)","v":1}]
[24928766,"Top shop","2016-02-25","PawPaw",584,1,{"$f":"COUNTIFS($A$2:A6,A6)","v":1}]
[42366779,"Quncy Top Shop","2016-02-22","Asparagus",282,1,{"$f":"COUNTIFS($A$2:A7,A7)","v":1}]
[53108248,"F&G Store","2016-02-21","Lemon",608,2,{"$f":"COUNTIFS($A$2:A8,A8)","v":1}]
[55152212,"BAC Stores","2016-02-24","Low fat",392,1,{"$f":"COUNTIFS($A$2:A9,A9)","v":1}]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,2,{"$f":"COUNTIFS($A$2:A10,A10)","v":2}]
[63545154,"B Fruit","2016-02-24","Strawberries",255,1,{"$f":"COUNTIFS($A$2:A11,A11)","v":1}]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,2,{"$f":"COUNTIFS($A$2:A12,A12)","v":2}]
[71102611,"Top shop","2016-02-24","Peach",415,1,{"$f":"COUNTIFS($A$2:A13,A13)","v":1}]
[86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339,1,{"$f":"COUNTIFS($A$2:A14,A14)","v":1}]
[95047456,"Country Food Store","2016-02-26","Asparagus",379,1,{"$f":"COUNTIFS($A$2:A15,A15)","v":1}]
[124787633,"BAC Stores","2016-02-23","Peanuts",456,1,{"$f":"COUNTIFS($A$2:A16,A16)","v":1}]
[124402443,"Top shop","2016-02-21","Banana",733,1,{"$f":"COUNTIFS($A$2:A17,A17)","v":1}]
[126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771,1,{"$f":"COUNTIFS($A$2:A18,A18)","v":1}]
[53108248,"F&G Store","2016-02-21","Lemon",608,2,{"$f":"COUNTIFS($A$2:A19,A19)","v":2}]
[151438681,"Quncy Top Shop","2016-02-24","Peach",421,1,{"$f":"COUNTIFS($A$2:A20,A20)","v":1}]

{"sheet":"UsingPivots","index":4,"n_rows":20,"n_cols":9,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Customer","Date","Product","Amount"]
[644834,"Top shop","2016-02-23","Peach",395,null,null,"Row Labels","Count of Customer"]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,null,null,644834,1]
[23990986,"Quick stop shop","2016-02-20","Banana",389,null,null,23990986,1]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,null,null,24928766,1]
[24928766,"Top shop","2016-02-25","PawPaw",584,null,null,42366779,1]
[42366779,"Quncy Top Shop","2016-02-22","Asparagus",282,null,null,53108248,2]
[53108248,"F&G Store","2016-02-21","Lemon",608,null,null,55152212,1]
[55152212,"BAC Stores","2016-02-24","Low fat",392,null,null,63545154,1]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,null,null,71102611,1]
[63545154,"B Fruit","2016-02-24","Strawberries",255,null,null,86973202,1]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,null,null,95047456,1]
[71102611,"Top shop","2016-02-24","Peach",415,null,null,103917196,2]
[86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339,null,null,124402443,1]
[95047456,"Country Food Store","2016-02-26","Asparagus",379,null,null,124787633,1]
[124787633,"BAC Stores","2016-02-23","Peanuts",456,null,null,126881633,1]
[124402443,"Top shop","2016-02-21","Banana",733,null,null,144895163,2]
[126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771,null,null,151438681,1]
[53108248,"F&G Store","2016-02-21","Lemon",608]
[151438681,"Quncy Top Shop","2016-02-24","Peach",421]

{"sheet":"UsingAdvancedFilters","index":5,"n_rows":20,"n_cols":12,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Customer","Date","Product","Amount",null,null,"Invoice#","Customer","Date","Product","Amount"]
[644834,"Top shop","2016-02-23","Peach",395,null,null,644834,"Top shop","2016-02-23","Peach",395]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,null,null,144895163,"BAC Stores","2016-02-22","Mushrooms",618]
[23990986,"Quick stop shop","2016-02-20","Banana",389,null,null,23990986,"Quick stop shop","2016-02-20","Banana",389]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,null,null,103917196,"Quncy Top Shop","2016-02-23","Peach",434]
[24928766,"Top shop","2016-02-25","PawPaw",584,null,null,24928766,"Top shop","2016-02-25","PawPaw",584]
[42366779,"Quncy Top Shop","2016-02-22","Asparagus",282,null,null,42366779,"Quncy Top Shop","2016-02-22","Asparagus",282]
[53108248,"F&G Store","2016-02-21","Lemon",608,null,null,53108248,"F&G Store","2016-02-21","Lemon",608]
[55152212,"BAC Stores","2016-02-24","Low fat",392,null,null,55152212,"BAC Stores","2016-02-24","Low fat",392]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618,null,null,63545154,"B Fruit","2016-02-24","Strawberries",255]
[63545154,"B Fruit","2016-02-24","Strawberries",255,null,null,71102611,"Top shop","2016-02-24","Peach",415]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434,null,null,86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339]
[71102611,"Top shop","2016-02-24","Peach",415,null,null,95047456,"Country Food Store","2016-02-26","Asparagus",379]
[86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339,null,null,124787633,"BAC Stores","2016-02-23","Peanuts",456]
[95047456,"Country Food Store","2016-02-26","Asparagus",379,null,null,124402443,"Top shop","2016-02-21","Banana",733]
[124787633,"BAC Stores","2016-02-23","Peanuts",456,null,null,126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771]
[124402443,"Top shop","2016-02-21","Banana",733,null,null,151438681,"Quncy Top Shop","2016-02-24","Peach",421]
[126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771]
[53108248,"F&G Store","2016-02-21","Lemon",608]
[151438681,"Quncy Top Shop","2016-02-24","Peach",421]

{"sheet":"BasedOnACondition","index":6,"n_rows":14,"n_cols":8,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Item","Quantity","Action",null,null,"Conditions to delete:"]
[null,"Item1",1450,{"$f":"IF((COUNTIFS($B$3:$B$14,B3)>1)*(C3>1000)=0,\"Keep\",\"Delete\")","v":"Keep"},null,null,null,"1. The Item column must be repeated"]
[null,"Item2",1380.3999999999999,"Delete",null,null,null,"2. The value in quantity column is >1000"]
[null,"Item2",580,"Keep"]
[null,"Item5",104.39999999999999,"Keep"]
[null,"Item5",1705.1999999999998,"Delete"]
[null,"Item2",3479.9999999999995,"Delete"]
[null,"Item5",69.599999999999994,"Keep"]
[null,"Item4",5800,"Delete"]
[null,"Item3",580,"Keep"]
[null,"Item4",988.31999999999994,"Keep"]
[null,"Item5",3479.9999999999995,"Delete"]
[null,"Item6",2999.7599999999998,"Keep"]

{"sheet":"RemoveDuplicates","index":7,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Date","Customer","Product","Amount"]
[644834,"Top shop","2016-02-23","Peach",395]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618]
[23990986,"Quick stop shop","2016-02-20","Banana",389]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434]
[24928766,"Top shop","2016-02-25","PawPaw",584]
[42366779,"Quncy Top Shop","2016-02-22","Asparagus",282]
[53108248,"F&G Store","2016-02-21","Lemon",608]
[55152212,"BAC Stores","2016-02-24","Low fat",392]
[144895163,"BAC Stores","2016-02-26","Lemon",618]
[63545154,"B Fruit","2016-02-24","Strawberries",255]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434]
[71102611,"Top shop","2016-02-24","Peach",415]
[86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339]
[95047456,"Country Food Store","2016-02-26","Asparagus",379]
[124787633,"BAC Stores","2016-02-23","Peanuts",456]
[124402443,"Top shop","2016-02-21","Banana",733]
[126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771]
[53108248,"F&G Store","2016-02-21","Lemon",608]
[151438681,"Quncy Top Shop","2016-02-24","Peach",421]

{"sheet":"UsingVBASingleColumn","index":8,"n_rows":20,"n_cols":1,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#"]
[644834]
[144895163]
[23990986]
[103917196]
[24928766]
[42366779]
[53108248]
[55152212]
[144895163]
[63545154]
[103917196]
[71102611]
[86973202]
[95047456]
[124787633]
[124402443]
[126881633]
[53108248]
[151438681]

{"sheet":"UsingVBARange","index":9,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Invoice#","Date","Customer","Product","Amount"]
[644834,"Top shop","2016-02-23","Peach",395]
[144895163,"BAC Stores","2016-02-22","Mushrooms",618]
[23990986,"Quick stop shop","2016-02-20","Banana",389]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434]
[24928766,"Top shop","2016-02-25","PawPaw",584]
[42366779,"Quncy Top Shop","2016-02-22","Asparagus",282]
[53108248,"F&G Store","2016-02-21","Lemon",608]
[55152212,"BAC Stores","2016-02-24","Low fat",392]
[144895163,"BAC Stores","2016-02-26","Lemon",618]
[63545154,"B Fruit","2016-02-24","Strawberries",255]
[103917196,"Quncy Top Shop","2016-02-23","Peach",434]
[71102611,"Top shop","2016-02-24","Peach",415]
[86973202,"Wong-Kee & Sons","2016-02-21","Asparagus",339]
[95047456,"Country Food Store","2016-02-26","Asparagus",379]
[124787633,"BAC Stores","2016-02-23","Peanuts",456]
[124402443,"Top shop","2016-02-21","Banana",733]
[126881633,"Wong-Kee & Sons","2016-02-24","Peanuts",771]
[53108248,"F&G Store","2016-02-21","Lemon",608]
[151438681,"Quncy Top Shop","2016-02-24","Peach",421]