{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Title","Question","Sheet7","BillTry","MikeTry","BillTry2","MikeDataModelDiffWorkbook","BillQuery","WrapUp"],"not_rendered":{"charts":0,"images":6,"macros":0,"external_links":1,"defined_names":7,"comments":0,"pivot_tables":2},"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":"Title","index":0,"n_rows":7,"n_cols":13,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,188]

{"sheet":"Question","index":1,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,"North America",null,null,"June","Gigi","Fred","Irene"]
["June",368,"North America",null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,"South America",null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,"South America",null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606,"South America",null,"North America"]
["Chin",1418,"South America",null,"South America"]
["Abdi",1110,"South America"]
["Sandi",1489,"North America"]
["June",175,"North America"]
["Shelia",1354,"South America"]
["Pham",132,"North America"]
["Gigi",449,"South America"]
["Mo",1333,"North America"]
["Sandi",1571,"North America"]
["Chin",1553,"North America"]
["Tyrone",1196,"South America"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"Sheet7","index":2,"n_rows":7,"n_cols":6,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Sum of Sales","Team"]
["Region","Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4","Grand Total"]
["North America",5301,8162,6211,2499,22173]
["South America",4307,5469,2929,2025,14730]
["Grand Total",9608,13631,9140,4524,36903]

{"sheet":"BillTry","index":3,"n_rows":45,"n_cols":6,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,"Team","Sales Rep"]
["Kip",457,"North America",null,"Sales Team 1","June"]
["June",368,"North America",null,"Sales Team 1","Sioux"]
["Gigi",725,"South America",null,"Sales Team 1","Poppi"]
["Mel",736,"South America",null,"Sales Team 1","Tyrone"]
["Tyrone",606,"South America",null,"Sales Team 2","Gigi"]
["Chin",1418,"South America",null,"Sales Team 2","Chin"]
["Abdi",1110,"South America",null,"Sales Team 2","Sandi"]
["Sandi",1489,"North America",null,"Sales Team 2","Shelia"]
["June",175,"North America",null,"Sales Team 3","Fred"]
["Shelia",1354,"South America",null,"Sales Team 3","Kip"]
["Pham",132,"North America",null,"Sales Team 3","Abdi"]
["Gigi",449,"South America",null,"Sales Team 3","Pham"]
["Mo",1333,"North America",null,"Sales Team 4","Irene"]
["Sandi",1571,"North America",null,"Sales Team 4","Al"]
["Chin",1553,"North America",null,"Sales Team 4","Mel"]
["Tyrone",1196,"South America",null,"Sales Team 4","Mo"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"MikeTry","index":4,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,"North America",null,null,"June","Gigi","Fred","Irene"]
["June",368,"North America",null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,"South America",null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,"South America",null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606,"South America",null,"North America",{"$f":"SUMPRODUCT(SUMIFS($B$4:$B$45,$A$4:$A$45,F$4:F$7,$C$4:$C$45,$E8))","v":5301},{"$f":"SUMPRODUCT(SUMIFS($B$4:$B$45,$A$4:$A$45,G$4:G$7,$C$4:$C$45,$E8))","v":8162},6211,2499]
["Chin",1418,"South America",null,"South America",{"$f":"SUMPRODUCT(SUMIFS($B$4:$B$45,$A$4:$A$45,F$4:F$7,$C$4:$C$45,$E9))","v":4307},5469,2929,2025]
["Abdi",1110,"South America"]
["Sandi",1489,"North America"]
["June",175,"North America"]
["Shelia",1354,"South America"]
["Pham",132,"North America"]
["Gigi",449,"South America"]
["Mo",1333,"North America"]
["Sandi",1571,"North America"]
["Chin",1553,"North America"]
["Tyrone",1196,"South America"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"BillTry2","index":5,"n_rows":45,"n_cols":13,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region","Sales Team",null,"Sales Rep","Sales Team"]
["Kip",457,"North America",{"$f":"VLOOKUP(A4,$F$4:$G$19,2,FALSE)","v":"Sales Team 3"},null,"June","Sales Team 1"]
["June",368,"North America",{"$f":"VLOOKUP(A5,$F$4:$G$19,2,FALSE)","v":"Sales Team 1"},null,"Sioux","Sales Team 1",null,"Sum of Sales",null,"Region"]
["Gigi",725,"South America","Sales Team 2",null,"Poppi","Sales Team 1",null,"Sales Team","Sales Rep","North America","South America","Grand Total"]
["Mel",736,"South America","Sales Team 4",null,"Tyrone","Sales Team 1",null,"Sales Team 1",null,5301,4307,9608]
["Tyrone",606,"South America","Sales Team 1",null,"Gigi","Sales Team 2",null,"Sales Team 2",null,8162,5469,13631]
["Chin",1418,"South America","Sales Team 2",null,"Chin","Sales Team 2",null,"Sales Team 3",null,6211,2929,9140]
["Abdi",1110,"South America","Sales Team 3",null,"Sandi","Sales Team 2",null,"Sales Team 4",null,2499,2025,4524]
["Sandi",1489,"North America","Sales Team 2",null,"Shelia","Sales Team 2",null,"Grand Total",null,22173,14730,36903]
["June",175,"North America","Sales Team 1",null,"Fred","Sales Team 3"]
["Shelia",1354,"South America","Sales Team 2",null,"Kip","Sales Team 3"]
["Pham",132,"North America","Sales Team 3",null,"Abdi","Sales Team 3"]
["Gigi",449,"South America","Sales Team 2",null,"Pham","Sales Team 3"]
["Mo",1333,"North America","Sales Team 4",null,"Irene","Sales Team 4"]
["Sandi",1571,"North America","Sales Team 2",null,"Al","Sales Team 4"]
["Chin",1553,"North America","Sales Team 2",null,"Mel","Sales Team 4"]
["Tyrone",1196,"South America","Sales Team 1",null,"Mo","Sales Team 4"]
["June",679,"North America","Sales Team 1"]
["Pham",221,"South America","Sales Team 3"]
["Pham",1097,"North America","Sales Team 3"]
["Gigi",473,"North America","Sales Team 2"]
["Tyrone",573,"South America","Sales Team 1"]
["Gigi",1160,"South America","Sales Team 2"]
["June",1268,"North America","Sales Team 1"]
["Sandi",1635,"North America","Sales Team 2"]
["Mel",152,"North America","Sales Team 4"]
["Poppi",603,"North America","Sales Team 1"]
["Irene",458,"North America","Sales Team 4"]
["Sioux",741,"South America","Sales Team 1"]
["Fred",486,"North America","Sales Team 3"]
["Tyrone",1227,"North America","Sales Team 1"]
["Poppi",981,"North America","Sales Team 1"]
["Fred",798,"North America","Sales Team 3"]
["Sioux",1191,"South America","Sales Team 1"]
["Gigi",363,"South America","Sales Team 2"]
["Abdi",1332,"North America","Sales Team 3"]
["Mo",1289,"South America","Sales Team 4"]
["Fred",1625,"North America","Sales Team 3"]
["Pham",1261,"South America","Sales Team 3"]
["Mo",556,"North America","Sales Team 4"]
["Sandi",1441,"North America","Sales Team 2"]
["Fred",337,"South America","Sales Team 3"]
["Fred",284,"North America","Sales Team 3"]

{"sheet":"MikeDataModelDiffWorkbook","index":6,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,"In Other Workbbok File ==>> MrExcelExcelisfunTrick188.xlsm"]

{"sheet":"BillQuery","index":7,"n_rows":45,"n_cols":6,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,"Sales Rep","Sales Team"]
["Kip",457,"North America",null,"June","Sales Team 1"]
["June",368,"North America",null,"Sioux","Sales Team 1"]
["Gigi",725,"South America",null,"Poppi","Sales Team 1"]
["Mel",736,"South America",null,"Tyrone","Sales Team 1"]
["Tyrone",606,"South America",null,"Gigi","Sales Team 2"]
["Chin",1418,"South America",null,"Chin","Sales Team 2"]
["Abdi",1110,"South America",null,"Sandi","Sales Team 2"]
["Sandi",1489,"North America",null,"Shelia","Sales Team 2"]
["June",175,"North America",null,"Fred","Sales Team 3"]
["Shelia",1354,"South America",null,"Kip","Sales Team 3"]
["Pham",132,"North America",null,"Abdi","Sales Team 3"]
["Gigi",449,"South America",null,"Pham","Sales Team 3"]
["Mo",1333,"North America",null,"Irene","Sales Team 4"]
["Sandi",1571,"North America",null,"Al","Sales Team 4"]
["Chin",1553,"North America",null,"Mel","Sales Team 4"]
["Tyrone",1196,"South America",null,"Mo","Sales Team 4"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"WrapUp","index":8,"n_rows":14,"n_cols":2,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Build a Sales Report by Region and Team"]
[]
[null,"Original data has sales rep and region"]
[null,"A second (badly shaped) table organizes the sales reps in to teams"]
[null,"Bill method 1: Re-shape the team hierarchy data. Make both ranges into Ctrl+T tables"]
[null,"Create a pivot table, adding the data to the data model. Pull Team from second table."]
[null,"Create a relationship"]
[null,"Mike Method2:  Build a SUMIFS where the Criteria2 field is an array!"]
[null,"Pass the SUMIFS in to the SUMPRODUCT function"]
[null,"Bill Method 3: Rearrange the hierarchy table so sales rep is on the left."]
[null,"Add a VLOOKUP to the original data"]
[null,"Build a pivot table"]
[null,"Mike Method 4: Use Power Query to join the tables"]