EXTRAPOLATE DATA FROM A TABLE!!!

Mandeep23

New Member
Joined
Nov 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi Guys,

I have some generic data that I need to sort but I'm finding it extremely difficult! Any help would be much appreciated!

I have the following criteria:
Find all customer that has order for the first time; within the last week that no previously data history
Find all customer that has ordered more than once within 6 months; within the last week
Find all customer that has ordered once after 6 months; within the last week
Find all the customers that haven't ordered in the last 12 months within the last week

I have pasted the mini-sheet below that doesn't go that far back, but would appreciate any formula or methods so I can easily pull this data!!

Criteria.xlsx
ABCD
1OrderOrdered DateNameTotal
21Sun 7th Nov, 22:13Ann Vicens-Morton104.9
32Sun 7th Nov, 21:15David Yates364.8
43Sun 7th Nov, 21:09Aisha Patel104
54Sun 7th Nov, 21:01frankie o'shea74.95
65Sun 7th Nov, 20:35Tracy Pickles139.85
76Sun 7th Nov, 20:10John Braid91.88
87Sun 7th Nov, 19:53Marc Gilly114.85
98Sun 7th Nov, 19:50Jeremy Kirk110
109Sun 7th Nov, 19:38Jonathan Pearson 120.6
1110Sun 7th Nov, 19:24K Fowler125.9
1211Sun 7th Nov, 19:20JOHN GRIMMETT124.85
1312Sun 7th Nov, 18:52Joe Kemm145.8
1413Sun 7th Nov, 18:51Anthony Yaskin140
1514Sun 7th Nov, 18:50Mark Downing102.44
1615Sun 7th Nov, 18:46Aaron Denton186.9
1716Sun 7th Nov, 18:45Sam Hurrell-wills70.05
1817Sun 7th Nov, 18:43Elaine Campbell204.95
1918Sun 7th Nov, 18:32Cameron Troops31.85
2019Sun 7th Nov, 18:23Lyndsay Traherne-Williams64.9
2120Sun 7th Nov, 17:59Gareth Lawrence80
2221Sun 7th Nov, 17:46Jade Pennick39.8
2322Sun 7th Nov, 17:36Sam Horner105.8
2423Sun 7th Nov, 17:31Robert Tarn103.75
2524Sun 7th Nov, 17:01Tahir Riaz203.15
2625Sun 7th Nov, 16:59Toby Miller161.85
2726Sun 7th Nov, 16:57Annmarie Wilson106.6
2827Sun 7th Nov, 16:44Chris Teasdale84.95
2928Sun 7th Nov, 16:38Joss Gale148.95
3029Sun 7th Nov, 16:30Noel Wallenberg120.85
3130Sun 7th Nov, 16:29Matt thomas109.9
3231Sun 7th Nov, 16:28edward richert242.9
3332Sun 7th Nov, 16:10Michael de Vyver93.85
3433Sun 7th Nov, 15:48Hayden Foster149.95
3534Sun 7th Nov, 15:47Arthur Hamilton124.85
3635Sun 7th Nov, 15:42Ronan Gilbert109.9
3736Sun 7th Nov, 15:27Yvonne Jacobs114.95
3837Sun 7th Nov, 15:14Matthew Li Kam Wa113.85
3938Sun 7th Nov, 15:00Rod Love108.75
4039Sun 7th Nov, 14:58margaret Hone85.56
4140Sun 7th Nov, 14:42Raiyan Awan154.46
4241Sun 7th Nov, 14:29Peter Murty64.9
4342Sun 7th Nov, 14:29Debbie Taylor80.64
4443Sun 7th Nov, 14:08Peter Blemings84.68
4544Sun 7th Nov, 14:06federico cincotta130.75
4645Sun 7th Nov, 14:04alastair Conway119.95
4746Sun 7th Nov, 13:47Tom Sirett161.5
4847Sun 7th Nov, 13:39Robert Thomas110.8
4948Sun 7th Nov, 13:39Andrew Nourish186.83
5049Sun 7th Nov, 13:32Sam Bateman109.9
5150Sun 7th Nov, 13:32Julian Seifert64.9
5251Sun 7th Nov, 13:25Luke Burnett110.8
5352Sun 7th Nov, 13:24Charles Fraser64.9
5453Sun 7th Nov, 13:18Laurence Herzberg139.76
5554Sun 7th Nov, 13:08Lorna Earley102.7
5655Sun 7th Nov, 12:58TONY WELLS212.75
5756Sun 7th Nov, 12:53PETER EARDLEY120
5857Sun 7th Nov, 12:50jack bithell77.87
5958Sun 7th Nov, 12:44Sarah-jane Legge54.9
6059Sun 7th Nov, 12:33Paul Edwards138.48
6160Sun 7th Nov, 12:32Mark James89.95
6261Sun 7th Nov, 12:27michael ansell114.95
6362Sun 7th Nov, 12:17Aidan Sinclair 123.8
6463Sun 7th Nov, 12:12Vera Mutimer163.85
6564Sun 7th Nov, 12:11Iain Marr64.9
6665Sun 7th Nov, 11:42steve moloney64.95
6766Sun 7th Nov, 08:55Chris Baker58.85
6867Sun 7th Nov, 01:55Scott Big on beauty64.9
6968Sat 6th Nov, 22:43EMMA XIE69.9
7069Sat 6th Nov, 22:40Loutfig Murfitt59.9
7170Sat 6th Nov, 22:20Jan Hoong Ho111.9
7271Sat 6th Nov, 21:14Rob Torres83.92
7372Sat 6th Nov, 20:36IVAN WOODHOUSE299.5
7473Sat 6th Nov, 20:27paul turner283.95
7574Sat 6th Nov, 20:21Kay Leach107.96
7675Sat 6th Nov, 20:15Wladyslaw Tabis109.9
7776Sat 6th Nov, 18:31Marcus Hennessey140
7877Sat 6th Nov, 18:00Emily Ridley39.9
7978Sat 6th Nov, 17:22Steve Hance123.9475
8079Sat 6th Nov, 17:14Henry Worth49.9
8180Sat 6th Nov, 17:02Mike Difrancesco66.63
8281Sat 6th Nov, 16:36Jonathan Desler100
8382Sat 6th Nov, 16:18Roxanne Smith69.95
8483Sat 6th Nov, 16:12Fran Martinez74.95
8584Sat 6th Nov, 15:08Simeonas Kozemiakinas299.95
8685Sat 6th Nov, 14:38Jayson Soriano121.51
8786Sat 6th Nov, 13:49Andy O’Boyle104.85
8887Sat 6th Nov, 13:40ian kendal99.33
8988Sat 6th Nov, 13:23Michael Leung80
9089Sat 6th Nov, 12:45Kaylum Ball80
9190Sat 6th Nov, 12:34Chris Sheridan110
9291Sat 6th Nov, 06:58Gunay Oksar118
9392Sat 6th Nov, 06:47Gunay Oksar100
9493Fri 5th Nov, 23:52Yoan Wong321.9
9594Fri 5th Nov, 23:50Gavin Walley100
9695Fri 5th Nov, 23:06Ahmed Omran79.85
9796Fri 5th Nov, 22:51Dean Rochester115.65
9897Fri 5th Nov, 22:14Graham peel59.9
9998Fri 5th Nov, 21:03Andrew Saville143.85
10099Fri 5th Nov, 20:58Jon Curtis39.9
101100Fri 5th Nov, 19:40Omar Paracha130.7
102101Fri 5th Nov, 19:29Simon Kirby80
103102Fri 5th Nov, 19:05Penny Cawley0
104103Fri 5th Nov, 18:29Mark Green110.5
105104Fri 5th Nov, 18:22Kwok Lee118.85
106105Fri 5th Nov, 17:53Simon Hurst140
107106Fri 5th Nov, 17:27David Cox159.8
108107Fri 5th Nov, 16:41Jackie Barton91.8
109108Fri 5th Nov, 16:38John Warren 165
110109Fri 5th Nov, 16:24Anton Richter669.95
111110Fri 5th Nov, 15:50Jonathan Truman100
112111Fri 5th Nov, 15:20Ciaran Clemmow180
113112Fri 5th Nov, 15:01Lily Wong80
114113Fri 5th Nov, 14:51James Gilruth319.95
115114Fri 5th Nov, 14:49Svava Davies50
116115Fri 5th Nov, 13:45Wai Tung Lam113.52
117116Fri 5th Nov, 13:44Matthew Marsden263.5
118117Fri 5th Nov, 13:37Ellie Lott69.9
119118Fri 5th Nov, 12:58simon evans80
120119Fri 5th Nov, 12:30Mandy Ireland45.8
121120Fri 5th Nov, 12:19Abdur Gearay57.75
122121Fri 5th Nov, 12:02Satnam Gill259.95
123122Fri 5th Nov, 11:50John Dutton144.95
124123Fri 5th Nov, 11:41Michael McGill114.9
125124Fri 5th Nov, 11:14Imran Qureshi165.59
126125Fri 5th Nov, 11:04Deborah Hamlyn79.75
127126Fri 5th Nov, 10:55Howard Grant300
128127Fri 5th Nov, 10:48Miguel Perez Guerra845
129128Fri 5th Nov, 10:31Dan Beimborn80
130129Fri 5th Nov, 09:04miranda rowland69.9
131130Fri 5th Nov, 08:06Shelley Watson50.85
132131Fri 5th Nov, 07:53Monica Font Del Amo160
133132Fri 5th Nov, 00:28Rosanne Ching80
134133Thu 4th Nov, 23:52Pierluigi Chiusolo80
135134Thu 4th Nov, 23:18Darrell Pinkney245.55
136135Thu 4th Nov, 20:54Simon O'Brien124.9
137136Thu 4th Nov, 20:26Nick Munday54.95
138137Thu 4th Nov, 20:15Jon Rokk80
139138Thu 4th Nov, 19:54Sean Pedrosa80
140139Thu 4th Nov, 19:48Alen Corak100
141140Thu 4th Nov, 19:04Henry Maria212.75
142141Thu 4th Nov, 18:57Michel Frey74.95
143142Thu 4th Nov, 18:47Niall Maher80
144143Thu 4th Nov, 18:46Benjamin Gill80
145144Thu 4th Nov, 18:44Vic Wei80
146145Thu 4th Nov, 18:35Robert Saunders80
147146Thu 4th Nov, 18:28Giovanni Scarcelli105.46
148147Thu 4th Nov, 18:23Alan Newton80
149148Thu 4th Nov, 17:59Gideon Holdway61.85
150149Thu 4th Nov, 17:37Tom Barker97.8
151150Thu 4th Nov, 17:21Tom Barker94.9
152151Thu 4th Nov, 16:36Josh Harcourt165.9
153152Thu 4th Nov, 16:02Georgios Becatoros319.9
154153Thu 4th Nov, 15:29Julian Evans50.7
155154Thu 4th Nov, 14:42Giles Forster220
156155Thu 4th Nov, 14:26denis artunse380
157156Thu 4th Nov, 14:17Douglas McMahon29.9
158157Thu 4th Nov, 13:57Dr. M.S. Amin104.85
159158Thu 4th Nov, 13:33Simon Ward131.55
160159Thu 4th Nov, 13:05Sam Cunningham-king59.9
161160Thu 4th Nov, 13:03ZANONE GIANCARLO154.95
162161Thu 4th Nov, 12:57David Wood0
163162Thu 4th Nov, 12:48Ercan Gurlek104.85
164163Thu 4th Nov, 12:29Hasan Hashwani279.65
165164Thu 4th Nov, 12:15ANDREW MCMASTER79.95
166165Thu 4th Nov, 12:11Jon Bentley0
167166Thu 4th Nov, 11:58MICHAEL lawson103.96
168167Thu 4th Nov, 11:41Philip Chi Ho152.75
169168Thu 4th Nov, 11:09Michael Roberts0
170169Thu 4th Nov, 10:56Alex Wisbey65.12
171170Thu 4th Nov, 10:55Gavin Rowlands27.9
172171Thu 4th Nov, 09:17Marta Lima263.5
173172Thu 4th Nov, 09:08Lauren Cloud69.85
174173Thu 4th Nov, 08:36Sean Phillips34.95
175174Thu 4th Nov, 08:31Paul Argent69.85
176175Thu 4th Nov, 06:43Anam Choudrey64.8
177176Wed 3rd Nov, 22:07Juan Carlos Gallardo109.75
178177Wed 3rd Nov, 21:16Khadeja Nayed39.9
179178Wed 3rd Nov, 20:56Roberto Barcellona139.95
180179Wed 3rd Nov, 20:26Chris Roberts169.9
181180Wed 3rd Nov, 20:22joel loader55.85
182181Wed 3rd Nov, 19:48Stephen Cotterill89.95
183182Wed 3rd Nov, 19:23Andrew Derbyshire44.9
184183Wed 3rd Nov, 19:23Matthew Hepworth89.95
185184Wed 3rd Nov, 19:13Carlos De Aniceto124.85
186185Wed 3rd Nov, 16:11Edward Davies129.67
187186Wed 3rd Nov, 15:55Mike Wort109.95
188187Wed 3rd Nov, 15:27Paul Atherton90
189188Wed 3rd Nov, 14:54Mark Telford69.85
190189Wed 3rd Nov, 14:48Vanessa Hetherington84.8
191190Wed 3rd Nov, 14:45Stacey Leigh Spalding103.96
192191Wed 3rd Nov, 14:43Peter Sheath0
193192Wed 3rd Nov, 14:33Neil White320
194193Wed 3rd Nov, 14:21Andrew Jefferies100
195194Wed 3rd Nov, 14:14Paul Klaiber108.95
196195Wed 3rd Nov, 13:59shaun gibson130
197196Wed 3rd Nov, 13:57DARREN BLAKE119.65
198197Wed 3rd Nov, 13:56Garry Smith104.9
199198Wed 3rd Nov, 13:11June Li247.88
200199Wed 3rd Nov, 12:10Martin Miller116.9
201200Wed 3rd Nov, 12:08oscar stevenson89.95
202201Wed 3rd Nov, 11:56Jamie Eagell198
203202Wed 3rd Nov, 11:20Jonathan Rivers0
204203Wed 3rd Nov, 11:09Chris Wiggins97.12
205204Wed 3rd Nov, 11:03Chris Socratous120
206205Wed 3rd Nov, 10:57Umair Saeed199.95
207206Wed 3rd Nov, 10:50Maureen Bennett0
208207Wed 3rd Nov, 10:25Jas Rajasansir46.85
209208Wed 3rd Nov, 10:25Nick Day49.9
210209Wed 3rd Nov, 10:18Akeel Karim111.88
211210Wed 3rd Nov, 09:33Tony Yuill29.9
212211Wed 3rd Nov, 08:26Andrew Smellie139.95
213212Wed 3rd Nov, 08:25andrew murrin79.95
214213Wed 3rd Nov, 08:09Sylvia Chai225.9
215214Wed 3rd Nov, 06:36Tim Tharratt94.95
216215Wed 3rd Nov, 06:07Carl Kirby100.75
217216Wed 3rd Nov, 04:23SHAMEEL SUHAIL51.9
218217Tue 2nd Nov, 22:13Werda Botan59.85
219218Tue 2nd Nov, 22:08Angeline Heffer283.72
220219Tue 2nd Nov, 22:07Andrew Steel125
221220Tue 2nd Nov, 21:40Barry Haggerty160
222221Tue 2nd Nov, 21:23Yiling Zhu80
223222Tue 2nd Nov, 21:20Ryan Nicholls89.95
224223Tue 2nd Nov, 21:12Angela Garner144.95
225224Tue 2nd Nov, 21:10Ioannis Tanes160
226225Tue 2nd Nov, 21:06Pavel Dubrouski355.79
227226Tue 2nd Nov, 20:26Rhys Jones117.9
228227Tue 2nd Nov, 20:24Justin Mycroft99.95
229228Tue 2nd Nov, 19:50Paul McDermid101.58
230229Tue 2nd Nov, 19:48SAVA TIHOV199.12
231230Tue 2nd Nov, 19:33Ryan Goff44.9
232231Tue 2nd Nov, 19:24Victoria Hatch113
233232Tue 2nd Nov, 19:17Paul White110.85
234233Tue 2nd Nov, 18:51Daniel Mclaughlin109.95
235234Tue 2nd Nov, 18:51Mandy Davis107.95
236235Tue 2nd Nov, 18:48alun dyde124.85
237236Tue 2nd Nov, 18:15Rachel Guest181.36
238237Tue 2nd Nov, 18:11Tom Byford116.95
239238Tue 2nd Nov, 17:41MD Belal258.9
240239Tue 2nd Nov, 17:13Amir Sayes57.07
241240Tue 2nd Nov, 17:05Antonio Munoz Bailey157.17
242241Tue 2nd Nov, 16:45Graham Beales219.85
243242Tue 2nd Nov, 16:45David Amber84.9
244243Tue 2nd Nov, 16:43Suleyman Simsek60.83
245244Tue 2nd Nov, 16:38Spencer Riddle114.95
246245Tue 2nd Nov, 16:02M MOROS108
247246Tue 2nd Nov, 16:01David Williams174.65
248247Tue 2nd Nov, 15:35Connor Mchugh79.95
249248Tue 2nd Nov, 15:07christopher busby60.8
250249Tue 2nd Nov, 15:02Jackie Farrands61.95
251250Tue 2nd Nov, 14:52David Blair101.85
252251Tue 2nd Nov, 14:39Lorna Willgress100.52
253252Tue 2nd Nov, 14:34Farouq Sheikh359.04
254253Tue 2nd Nov, 14:27Lucas Rocha83.7
255254Tue 2nd Nov, 13:57M Ahmed19.95
256255Tue 2nd Nov, 13:35Nick Goodfellow104.9
257256Tue 2nd Nov, 13:24Paul Richardson119.95
258257Tue 2nd Nov, 13:22Przemyslaw Krajcer107.95
259258Tue 2nd Nov, 13:15kingsleigh hammond153.85
260259Tue 2nd Nov, 13:11Shohib Tariq130
261260Tue 2nd Nov, 13:07Abraham DAHAN BENAIM182.92
262261Tue 2nd Nov, 12:46David Armstrong 106.7
263262Tue 2nd Nov, 12:43Calli Amiras112.25
264263Tue 2nd Nov, 12:11Eddy Trivett130
265264Tue 2nd Nov, 11:33Sabrina Haque-Amin102.8
266265Tue 2nd Nov, 11:32Kate Tulloch38.9
267266Tue 2nd Nov, 11:29Konrad Stanczyk49.9
268267Tue 2nd Nov, 10:46Paul Ashcroft55
269268Tue 2nd Nov, 10:30Darren Fairweather49.85
270269Tue 2nd Nov, 10:30Sarah Burgess102.85
271270Tue 2nd Nov, 10:27Yinka Okunade159.9
272271Tue 2nd Nov, 10:22Nigel Neath0
273272Tue 2nd Nov, 10:06samuel smith253.65
274273Tue 2nd Nov, 10:04Ben Halleybone49.9
275274Tue 2nd Nov, 09:53Rachel Denning176.8
276275Tue 2nd Nov, 09:52Jayne Ankers107.55
277276Tue 2nd Nov, 09:48Tristram Allen450
278277Tue 2nd Nov, 09:47Chris Socratous89.75
279278Tue 2nd Nov, 09:35Rob Linton100.85
280279Tue 2nd Nov, 08:46Carol Petrie169.9
281280Tue 2nd Nov, 07:18Robert Gunn39.9
282281Tue 2nd Nov, 07:03Nikki Dicbonas144.95
283282Tue 2nd Nov, 06:41Iain Russell84.8
284283Tue 2nd Nov, 06:01Luke Cameron109.9
285284Tue 2nd Nov, 03:11Daniel Chen104.35
286285Mon 1st Nov, 23:37Peter Arch95.8
287286Mon 1st Nov, 23:19Daniel Hadley59.9
288287Mon 1st Nov, 23:19CRISTIAN FRANKIC119.8
289288Mon 1st Nov, 23:19Mohsen Mansouri104.85
290289Mon 1st Nov, 23:12Daniel Quiney121.9
291290Mon 1st Nov, 23:04christine mills64.95
292291Mon 1st Nov, 23:00Joanne Bradbury80
293292Mon 1st Nov, 22:46Ahmed Abdel-Jaber139.75
294293Mon 1st Nov, 22:45Shayla Ali35.41
295294Mon 1st Nov, 22:35Miguel Segarra167.84
296295Mon 1st Nov, 22:28Jeff Yuen136
297296Mon 1st Nov, 22:23Stephen Neale112.9
298297Mon 1st Nov, 22:00Sharon Lai80
299298Mon 1st Nov, 21:54Richard Collins 104.9
300299Mon 1st Nov, 21:44Allison Ward99.75
301300Mon 1st Nov, 21:29Ewen Scott43.8
302301Mon 1st Nov, 21:24David Jackson156.48
303302Mon 1st Nov, 21:12Zeshan Alam100
304303Mon 1st Nov, 21:06B Chowdhury41.85
305304Mon 1st Nov, 21:03Ali Dhanji259.95
306305Mon 1st Nov, 21:00Robert Tarn80
307306Mon 1st Nov, 20:57Rudy Kathiravel117.99
308307Mon 1st Nov, 20:53Anna Deacon827.04
309308Mon 1st Nov, 20:46Ahmed Aboushelbaya287.6
310309Mon 1st Nov, 20:45Matthew David Gibbs67.43
311310Mon 1st Nov, 20:43Graham Allison130.85
312311Mon 1st Nov, 20:34ANN MARTIN146.8
313312Mon 1st Nov, 20:31mobeen ahmed111.96
314313Mon 1st Nov, 20:30Shabeena Maricar108.8
315314Mon 1st Nov, 20:12Adam Iqbal49.85
316315Mon 1st Nov, 20:11Shaheen Choudhry118.65
317316Mon 1st Nov, 20:01Alan Rudkin114.67
318317Mon 1st Nov, 20:00David Roberts80
319318Mon 1st Nov, 19:55Zohaib Siddiqui119.85
320319Mon 1st Nov, 19:54Robert Soultanaev89.95
321320Mon 1st Nov, 19:38Alex Nitka49.85
322321Mon 1st Nov, 19:36Robert Pickstock72.7
323322Mon 1st Nov, 19:30Imran Chohan452.65
324323Mon 1st Nov, 19:25P White29.9
325324Mon 1st Nov, 19:24Patricia Clements49.95
326325Mon 1st Nov, 19:23David Nolte143.45
327326Mon 1st Nov, 19:20Niall McKinney155.3
328327Mon 1st Nov, 19:20Ahmed Rawi105.7
329328Mon 1st Nov, 19:13Muhammad Hussain59.85
330329Mon 1st Nov, 19:08Geoff Brewis-Levie29.9
331330Mon 1st Nov, 19:07Mikhail Hessin120
332331Mon 1st Nov, 19:06Mark Rowe29.9
333332Mon 1st Nov, 19:00Andrew Mills80
334333Mon 1st Nov, 19:00Gerry Duffey102.9
335334Mon 1st Nov, 18:58Ejaz Ahmed109.95
336335Mon 1st Nov, 18:57Croz Croswell93.75
337336Mon 1st Nov, 18:56Simon Lace29.9
338337Mon 1st Nov, 18:56Gary Morris100
339338Mon 1st Nov, 18:51Leonardo Gonzalez70.8
340339Mon 1st Nov, 18:50Chris Branscombe Ling116.95
341340Mon 1st Nov, 18:49Gary Harrison29.9
342341Mon 1st Nov, 18:47Gary Morris184.95
343342Mon 1st Nov, 18:46carl mccaffrey49.85
344343Mon 1st Nov, 18:45Athif Hussain128.75
345344Mon 1st Nov, 18:40Elizabeth Mitchell49.85
346345Mon 1st Nov, 18:38Mohamed Hanif Omar89.9
347346Mon 1st Nov, 18:36Andrew Lucas90
348347Mon 1st Nov, 18:36Hugh Orange102.95
349348Mon 1st Nov, 18:18Zayba Sheikh49.85
350349Mon 1st Nov, 18:17David Charlesworth115
351350Mon 1st Nov, 18:00John Head80
352351Mon 1st Nov, 17:55Paul Elliott250
353352Mon 1st Nov, 17:55Heinz Forster120
354353Mon 1st Nov, 17:55Austin Straub35.85
355354Mon 1st Nov, 17:49Matthew Miller1239.9
356355Mon 1st Nov, 17:29Aidan Lundy123.21
357356Mon 1st Nov, 17:27James Joyce144.95
358357Mon 1st Nov, 17:00Paul Graham80
359358Mon 1st Nov, 16:45Sara Wells164.01
360359Mon 1st Nov, 16:38Karveh Cavalirie60.9
361360Mon 1st Nov, 16:31Patrick Mooney139.95
362361Mon 1st Nov, 16:15Robert Worters185.95
363362Mon 1st Nov, 16:06Danielle Graham49.91
364363Mon 1st Nov, 16:00Suryan Wirya Simunovic80
365364Mon 1st Nov, 15:57John Winder89.95
366365Mon 1st Nov, 15:50Simon Sankey114.95
367366Mon 1st Nov, 15:44David Benfell60.8
368367Mon 1st Nov, 15:19Toby Rainbow164.95
369368Mon 1st Nov, 15:09Graham Lewis69.95
370369Mon 1st Nov, 15:00Jobe Ferguson80
371370Mon 1st Nov, 14:30Amir Iqbal159.85
372371Mon 1st Nov, 14:29Sarah Elhatow-Senkuviene61.87
373372Mon 1st Nov, 14:26Giles Stafford114.95
374373Mon 1st Nov, 14:04Sahal Bhamjee 100
375374Mon 1st Nov, 13:49Jonathan McCrory100
376375Mon 1st Nov, 13:33Niall Stack41.91
377376Mon 1st Nov, 13:12Ori Reiss182.75
378377Mon 1st Nov, 13:10Marion Buchan100
379378Mon 1st Nov, 11:35Peter Davies0
380379Mon 1st Nov, 11:25Chris Ansell0
381380Mon 1st Nov, 11:22Lee Starmer0
382381Mon 1st Nov, 11:19Nick March0
383382Mon 1st Nov, 11:18M Ahmed110.42
384383Mon 1st Nov, 11:11Stanley Davies89.04
385384Mon 1st Nov, 10:46Peter Kwok340
386385Mon 1st Nov, 10:17Gareth Lazell199.8
387386Mon 1st Nov, 10:06Simon Fletcher89.0471
388387Mon 1st Nov, 09:58Garry Moore69.85
389388Mon 1st Nov, 09:38Toby Topcott174.85
390389Mon 1st Nov, 08:45Philip Tucker125
391390Mon 1st Nov, 03:04Tak Chi Choy108.7
392391Sun 31st Oct, 23:47Wayne Forster113.9
393392Sun 31st Oct, 23:13Omar Al-Haffar70.8
394393Sun 31st Oct, 22:34vincent masnou123.17
395394Sun 31st Oct, 22:28David Hill109.9
396395Sun 31st Oct, 22:03Ed Farrell74.95
397396Sun 31st Oct, 21:46Ignacio Farga Ninoles101.85
398397Sun 31st Oct, 20:31Ian Anderson65.9
399398Sun 31st Oct, 19:58Thomas Simmons37.75
400399Sun 31st Oct, 19:54Daniel Taylor103.85
401400Sun 31st Oct, 19:49Jon Bentley532.87
402401Sun 31st Oct, 19:45Claudiu Tirsolea109.9
403402Sun 31st Oct, 19:40Jeremy Cooke240
404403Sun 31st Oct, 19:14Alison Porter105.8
405404Sun 31st Oct, 18:54Julie Mahmoud100
406405Sun 31st Oct, 18:37Hoa Huynh59.9
407406Sun 31st Oct, 18:36Darren Clarke110.85
408407Sun 31st Oct, 18:02ALUN MARRIOTT61.8
409408Sun 31st Oct, 18:02Saif Alomani39.9
410409Sun 31st Oct, 17:59Temuulen Nanzad87.8
411410Sun 31st Oct, 17:44Roger Taslaq146.9
412411Sun 31st Oct, 17:36Phil Bird20.9
413412Sun 31st Oct, 17:32Paul Cerisola44.9
414413Sun 31st Oct, 17:29Dimitrios Skopelitis760
415414Sun 31st Oct, 17:07Liz Glover50.85
416415Sun 31st Oct, 16:52Angela Bird110.85
417416Sun 31st Oct, 16:48Melissa Shaw101.56
418417Sun 31st Oct, 16:45Munadia Lauria182.75
419418Sun 31st Oct, 16:35Rob Lovelock92.85
420419Sun 31st Oct, 16:26John Harrhy69.95
421420Sun 31st Oct, 16:25Andy Gosheron71.8
422421Sun 31st Oct, 16:19Stephen Hamer96.8583
423422Sun 31st Oct, 15:37Dawn von Bergen120
424423Sun 31st Oct, 14:38Maryam ALI39.9
425424Sun 31st Oct, 14:29Daniel Drinkwater54.95
426425Sun 31st Oct, 14:27RUSSELL KINGSNORTH180
427426Sun 31st Oct, 14:26Glen Binns130
428427Sun 31st Oct, 14:24Patrik Burstrom113.75
429428Sun 31st Oct, 13:43Gareth Keegan132
430429Sun 31st Oct, 13:40Anthony Walters135
431430Sun 31st Oct, 13:33Isa Mohammed60.95
432431Sun 31st Oct, 13:15polina zabelina150
433432Sun 31st Oct, 12:56Shakera Tayub170
434433Sun 31st Oct, 12:46Grant Read69.95
435434Sun 31st Oct, 12:46carl mccaffrey69.85
436435Sun 31st Oct, 12:43Andrew Parraco113.8
437436Sun 31st Oct, 12:42Susan Clarke55.75
438437Sun 31st Oct, 12:35Mark Harris158.95
439438Sun 31st Oct, 12:29Jon Rokk101.7
440439Sun 31st Oct, 12:20Ian Roberts69.95
441440Sun 31st Oct, 12:19Mandy Dunn152.6
442441Sun 31st Oct, 12:19Sami Agush69.95
443442Sun 31st Oct, 12:12fraser crichton69.95
444443Sun 31st Oct, 10:29Yi Yang134.95
445444Sun 31st Oct, 09:28Tatiana Livesey320.9
446445Sun 31st Oct, 09:26Jared Tovee63.4
447446Sun 31st Oct, 09:00matthew Francis43.4
448447Sun 31st Oct, 08:52Y Nazir89.85
449448Sun 31st Oct, 07:43H Suggate69.85
450449Sun 31st Oct, 07:24Martin McNally89.95
451450Sat 30th Oct, 22:01Arshad Waheed243.8
452451Sat 30th Oct, 21:50Fay Gao249.8
453452Sat 30th Oct, 21:43David Mensik179.95
454453Sat 30th Oct, 21:01Helen Brownlees68.9
455454Sat 30th Oct, 20:58Brendon Jiang164.85
456455Sat 30th Oct, 19:53Juliet O'Mahony172.86
457456Sat 30th Oct, 18:00Ross Lynam-Carter80
458457Sat 30th Oct, 17:10James Silber104.75
459458Sat 30th Oct, 15:59Simon Cotmore100
460459Sat 30th Oct, 15:09Simon Inchley122.95
461460Sat 30th Oct, 14:25Tracy Lower85.91
462461Sat 30th Oct, 13:58Joe Hankin115.95
463462Sat 30th Oct, 13:42Emma Fish103.95
464463Sat 30th Oct, 13:37Gareth Lewis154.95
465464Sat 30th Oct, 13:12GEORGE DIAMOND180
466465Sat 30th Oct, 13:05Richard Claypole106.86
467466Sat 30th Oct, 13:03Angela Scattergood30.85
468467Sat 30th Oct, 13:00Paul Allan80
469468Sat 30th Oct, 12:22Trisha Campbell85
470469Sat 30th Oct, 12:22Stephen Smith100.8
471470Sat 30th Oct, 12:06Rob FLOYD130
472471Sat 30th Oct, 12:00Nick March80
473472Sat 30th Oct, 11:54Abdul Mehmood189.95
474473Sat 30th Oct, 11:43geoff hayllar104.75
475474Sat 30th Oct, 11:41Nick Ekendahl161.38
476475Sat 30th Oct, 10:50Ilya Belyaev100
477476Sat 30th Oct, 10:45David Carr71.25
478477Sat 30th Oct, 10:33Jonathan Wilkinson128.95
479478Sat 30th Oct, 10:25Xin Jiang114.9
480479Sat 30th Oct, 08:33P J White110
481480Sat 30th Oct, 08:02Julie Mahmoud100
482481Sat 30th Oct, 01:37LAI LIU149.85
483482Sat 30th Oct, 00:10peter sheath49.95
484483Fri 29th Oct, 23:13Shane Duffield128.19
485484Fri 29th Oct, 22:51Oliver Webb218.2
486485Fri 29th Oct, 22:34Del Flint102.75
487486Fri 29th Oct, 22:10George Tebbutt86.45
488487Fri 29th Oct, 21:42Laurel Powers-Freeling62.5
489488Fri 29th Oct, 21:29Rasheed Mosaheb109.95
490489Fri 29th Oct, 21:20craig renilson120
491490Fri 29th Oct, 21:00Amardeep Chima80
492491Fri 29th Oct, 20:59Dr. M.S. Amin119.85
493492Fri 29th Oct, 20:47Nic Lee180
494493Fri 29th Oct, 20:46Julie Mahmoud110
495494Fri 29th Oct, 20:07sajid khaliq69.8
496495Fri 29th Oct, 19:42Jason Nowell215.85
497496Fri 29th Oct, 19:41Daniel Goldfarb48.8
498497Fri 29th Oct, 19:20gavin hutcheson69.8
499498Fri 29th Oct, 19:19Guy Keng95.8
500499Fri 29th Oct, 19:19FIONA SAVORY116.74
order-breakdown-table-2021-11-0
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,435
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum.\

Could you please re-post that data after you re-format the date column so that the each cell is an official Excel Date Serial number? Like: 11/7/2021 14:51
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
453
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Having trouble with Columns I and J
MrExcelPlayground4.xlsx
ABCDEFGHIJKLMN
1OrderOrdered DateNameTotalNew customers in the last weekCustomers in the last week that ordered at least once in the previous 6 monthsCustomers in the last week that haven't ordered in the last 12 months priorCustomers in the last week that ordered more than once in the previous 6 monthsCustomers in the last week that ordered exactly once in the previous 6 monthsMore than onceexactly once
2111/7/21 22:13Ann Vicens-Morton104.9Ann Vicens-MortonRobert TarnAnn Vicens-Morton#CALC!Robert Tarn00Werda Botan
3211/7/21 21:15David Yates364.8David YatesJon RokkDavid YatesJon Rokk00Angeline Heffer
4311/7/21 21:09Aisha Patel104Aisha PatelDr. M.S. AminAisha PatelDr. M.S. Amin00Andrew Steel
5411/7/21 21:01frankie o'shea74.95frankie o'sheaJon Bentleyfrankie o'sheaJon Bentley00Barry Haggerty
6511/7/21 20:35Tracy Pickles139.85Tracy PicklesPeter SheathTracy PicklesPeter Sheath00Yiling Zhu
7611/7/21 20:10John Braid91.88John BraidChris SocratousJohn BraidChris Socratous00Ryan Nicholls
8711/7/21 19:53Marc Gilly114.85Marc GillyMarc Gilly00Angela Garner
9811/7/21 19:50Jeremy Kirk110Jeremy KirkJeremy Kirk00Ioannis Tanes
10911/7/21 19:38Jonathan Pearson 120.6Jonathan Pearson Jonathan Pearson 00Pavel Dubrouski
111011/7/21 19:24K Fowler125.9K FowlerK Fowler00Rhys Jones
121111/7/21 19:20JOHN GRIMMETT124.85JOHN GRIMMETTJOHN GRIMMETT00Justin Mycroft
131211/7/21 18:52Joe Kemm145.8Joe KemmJoe Kemm00Paul McDermid
141311/7/21 18:51Anthony Yaskin140Anthony YaskinAnthony Yaskin00SAVA TIHOV
151411/7/21 18:50Mark Downing102.44Mark DowningMark Downing00Ryan Goff
161511/7/21 18:46Aaron Denton186.9Aaron DentonAaron Denton00Victoria Hatch
171611/7/21 18:45Sam Hurrell-wills70.05Sam Hurrell-willsSam Hurrell-wills00Paul White
181711/7/21 18:43Elaine Campbell204.95Elaine CampbellElaine Campbell00Daniel Mclaughlin
191811/7/21 18:32Cameron Troops31.85Cameron TroopsCameron Troops00Mandy Davis
201911/7/21 18:23Lyndsay Traherne-Williams64.9Lyndsay Traherne-WilliamsLyndsay Traherne-Williams00alun dyde
212011/7/21 17:59Gareth Lawrence80Gareth LawrenceGareth Lawrence00Rachel Guest
222111/7/21 17:46Jade Pennick39.8Jade PennickJade Pennick00Tom Byford
232211/7/21 17:36Sam Horner105.8Sam HornerSam Horner00MD Belal
242311/7/21 17:31Robert Tarn103.75Tahir RiazTahir Riaz088Amir Sayes
252411/7/21 17:01Tahir Riaz203.15Toby MillerToby Miller00Antonio Munoz Bailey
262511/7/21 16:59Toby Miller161.85Annmarie WilsonAnnmarie Wilson00Graham Beales
272611/7/21 16:57Annmarie Wilson106.6Chris TeasdaleChris Teasdale00David Amber
282711/7/21 16:44Chris Teasdale84.95Joss GaleJoss Gale00Suleyman Simsek
292811/7/21 16:38Joss Gale148.95Noel WallenbergNoel Wallenberg00Spencer Riddle
302911/7/21 16:30Noel Wallenberg120.85Matt thomasMatt thomas00M MOROS
313011/7/21 16:29Matt thomas109.9edward richertedward richert00David Williams
323111/7/21 16:28edward richert242.9Michael de VyverMichael de Vyver00Connor Mchugh
333211/7/21 16:10Michael de Vyver93.85Hayden FosterHayden Foster00christopher busby
343311/7/21 15:48Hayden Foster149.95Arthur HamiltonArthur Hamilton00Jackie Farrands
353411/7/21 15:47Arthur Hamilton124.85Ronan GilbertRonan Gilbert00David Blair
363511/7/21 15:42Ronan Gilbert109.9Yvonne JacobsYvonne Jacobs00Lorna Willgress
373611/7/21 15:27Yvonne Jacobs114.95Matthew Li Kam WaMatthew Li Kam Wa00Farouq Sheikh
383711/7/21 15:14Matthew Li Kam Wa113.85Rod LoveRod Love00Lucas Rocha
393811/7/21 15:00Rod Love108.75margaret Honemargaret Hone00M Ahmed
403911/7/21 14:58margaret Hone85.56Raiyan AwanRaiyan Awan00Nick Goodfellow
414011/7/21 14:42Raiyan Awan154.46Peter MurtyPeter Murty00Paul Richardson
424111/7/21 14:29Peter Murty64.9Debbie TaylorDebbie Taylor00Przemyslaw Krajcer
434211/7/21 14:29Debbie Taylor80.64Peter BlemingsPeter Blemings00kingsleigh hammond
444311/7/21 14:08Peter Blemings84.68federico cincottafederico cincotta00Shohib Tariq
454411/7/21 14:06federico cincotta130.75alastair Conwayalastair Conway00Abraham DAHAN BENAIM
464511/7/21 14:04alastair Conway119.95Tom SirettTom Sirett00David Armstrong
474611/7/21 13:47Tom Sirett161.5Robert ThomasRobert Thomas00Calli Amiras
484711/7/21 13:39Robert Thomas110.8Andrew NourishAndrew Nourish00Eddy Trivett
494811/7/21 13:39Andrew Nourish186.83Sam BatemanSam Bateman00Sabrina Haque-Amin
504911/7/21 13:32Sam Bateman109.9Julian SeifertJulian Seifert00Kate Tulloch
515011/7/21 13:32Julian Seifert64.9Luke BurnettLuke Burnett00Konrad Stanczyk
Sheet36
Cell Formulas
RangeFormula
F2:F209F2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,B2:B500<TODAY()-7)),0),0)=0)
G2:G7G2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),0),0)>0)
H2:H209H2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-366))),0),0)=0)
I2I2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),L2#>0)
J2:J7J2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),M2#>0)
L2:L215L2=IFNA(MATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),FILTER(UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),COUNTIFS(N2#,UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))))>1),0),0)
M2:M215M2=IFNA(MATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),FILTER(UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),COUNTIFS(N2#,UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))))=1),0),0)
N2:N284N2=FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))
Dynamic array formulas.
 

Mandeep23

New Member
Joined
Nov 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Amazing! Although I don't know how its done aha. What formula did you use to convert the date to the right format?
The last two criteria will be hardest to work out. Would you be able to explain what you have done to get the results please?
 

Mandeep23

New Member
Joined
Nov 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

Welcome to the forum.\

Could you please re-post that data after you re-format the date column so that the each cell is an official Excel Date Serial number? Like: 11/7/2021 14:51
Thank you, I'm having trouble converting the date as well :(
 

Mandeep23

New Member
Joined
Nov 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Welcome to the forum.\

Could you please re-post that data after you re-format the date column so that the each cell is an official Excel Date Serial number? Like: 11/7/2021 14:51
Hi, I managed to change the date!! Please see the below.

Data.xlsx
ABCD
1OrderOrdered DateNameTotal
2107/11/2021Ann Vicens-Morton104.9
3207/11/2021David Yates364.8
4307/11/2021Aisha Patel104
5407/11/2021frankie o'shea74.95
6507/11/2021Tracy Pickles139.85
7607/11/2021John Braid91.88
8707/11/2021Marc Gilly114.85
9807/11/2021Jeremy Kirk110
10907/11/2021Jonathan Pearson 120.6
111007/11/2021K Fowler125.9
121107/11/2021JOHN GRIMMETT124.85
131207/11/2021Joe Kemm145.8
141307/11/2021Anthony Yaskin140
151407/11/2021Mark Downing102.44
161507/11/2021Aaron Denton186.9
171607/11/2021Sam Hurrell-wills70.05
181707/11/2021Elaine Campbell204.95
191807/11/2021Cameron Troops31.85
201907/11/2021Lyndsay Traherne-Williams64.9
212007/11/2021Gareth Lawrence80
222107/11/2021Jade Pennick39.8
232207/11/2021Sam Horner105.8
242307/11/2021Robert Tarn103.75
252407/11/2021Tahir Riaz203.15
262507/11/2021Toby Miller161.85
272607/11/2021Annmarie Wilson106.6
282707/11/2021Chris Teasdale84.95
292807/11/2021Joss Gale148.95
302907/11/2021Noel Wallenberg120.85
313007/11/2021Matt thomas109.9
323107/11/2021edward richert242.9
333207/11/2021Michael de Vyver93.85
343307/11/2021Hayden Foster149.95
353407/11/2021Arthur Hamilton124.85
363507/11/2021Ronan Gilbert109.9
373607/11/2021Yvonne Jacobs114.95
383707/11/2021Matthew Li Kam Wa113.85
393807/11/2021Rod Love108.75
403907/11/2021margaret Hone85.56
414007/11/2021Raiyan Awan154.46
424107/11/2021Peter Murty64.9
434207/11/2021Debbie Taylor80.64
444307/11/2021Peter Blemings84.68
454407/11/2021federico cincotta130.75
464507/11/2021alastair Conway119.95
474607/11/2021Tom Sirett161.5
484707/11/2021Robert Thomas110.8
494807/11/2021Andrew Nourish186.83
504907/11/2021Sam Bateman109.9
515007/11/2021Julian Seifert64.9
525107/11/2021Luke Burnett110.8
535207/11/2021Charles Fraser64.9
545307/11/2021Laurence Herzberg139.76
555407/11/2021Lorna Earley102.7
565507/11/2021TONY WELLS212.75
575607/11/2021PETER EARDLEY120
585707/11/2021jack bithell77.87
595807/11/2021Sarah-jane Legge54.9
605907/11/2021Paul Edwards138.48
616007/11/2021Mark James89.95
626107/11/2021michael ansell114.95
636207/11/2021Aidan Sinclair 123.8
646307/11/2021Vera Mutimer163.85
656407/11/2021Iain Marr64.9
666507/11/2021steve moloney64.95
676607/11/2021Chris Baker58.85
686707/11/2021Scott Big on beauty64.9
696806/11/2021EMMA XIE69.9
706906/11/2021Loutfig Murfitt59.9
717006/11/2021Jan Hoong Ho111.9
727106/11/2021Rob Torres83.92
737206/11/2021IVAN WOODHOUSE299.5
747306/11/2021paul turner283.95
757406/11/2021Kay Leach107.96
767506/11/2021Wladyslaw Tabis109.9
777606/11/2021Marcus Hennessey140
787706/11/2021Emily Ridley39.9
797806/11/2021Steve Hance123.9475
807906/11/2021Henry Worth49.9
818006/11/2021Mike Difrancesco66.63
828106/11/2021Jonathan Desler100
838206/11/2021Roxanne Smith69.95
848306/11/2021Fran Martinez74.95
858406/11/2021Simeonas Kozemiakinas299.95
868506/11/2021Jayson Soriano121.51
878606/11/2021Andy O’Boyle104.85
888706/11/2021ian kendal99.33
898806/11/2021Michael Leung80
908906/11/2021Kaylum Ball80
919006/11/2021Chris Sheridan110
929106/11/2021Gunay Oksar118
939206/11/2021Gunay Oksar100
949305/11/2021Yoan Wong321.9
959405/11/2021Gavin Walley100
969505/11/2021Ahmed Omran79.85
979605/11/2021Dean Rochester115.65
989705/11/2021Graham peel59.9
999805/11/2021Andrew Saville143.85
1009905/11/2021Jon Curtis39.9
10110005/11/2021Omar Paracha130.7
10210105/11/2021Simon Kirby80
10310205/11/2021Penny Cawley0
10410305/11/2021Mark Green110.5
10510405/11/2021Kwok Lee118.85
10610505/11/2021Simon Hurst140
10710605/11/2021David Cox159.8
10810705/11/2021Jackie Barton91.8
10910805/11/2021John Warren 165
11010905/11/2021Anton Richter669.95
11111005/11/2021Jonathan Truman100
11211105/11/2021Ciaran Clemmow180
11311205/11/2021Lily Wong80
11411305/11/2021James Gilruth319.95
11511405/11/2021Svava Davies50
11611505/11/2021Wai Tung Lam113.52
11711605/11/2021Matthew Marsden263.5
11811705/11/2021Ellie Lott69.9
11911805/11/2021simon evans80
12011905/11/2021Mandy Ireland45.8
12112005/11/2021Abdur Gearay57.75
12212105/11/2021Satnam Gill259.95
12312205/11/2021John Dutton144.95
12412305/11/2021Michael McGill114.9
12512405/11/2021Imran Qureshi165.59
12612505/11/2021Deborah Hamlyn79.75
12712605/11/2021Howard Grant300
12812705/11/2021Miguel Perez Guerra845
12912805/11/2021Dan Beimborn80
13012905/11/2021miranda rowland69.9
13113005/11/2021Shelley Watson50.85
13213105/11/2021Monica Font Del Amo160
13313205/11/2021Rosanne Ching80
13413304/11/2021Pierluigi Chiusolo80
13513404/11/2021Darrell Pinkney245.55
13613504/11/2021Simon O'Brien124.9
13713604/11/2021Nick Munday54.95
13813704/11/2021Jon Rokk80
13913804/11/2021Sean Pedrosa80
14013904/11/2021Alen Corak100
14114004/11/2021Henry Maria212.75
14214104/11/2021Michel Frey74.95
14314204/11/2021Niall Maher80
14414304/11/2021Benjamin Gill80
14514404/11/2021Vic Wei80
14614504/11/2021Robert Saunders80
14714604/11/2021Giovanni Scarcelli105.46
14814704/11/2021Alan Newton80
14914804/11/2021Gideon Holdway61.85
15014904/11/2021Tom Barker97.8
15115004/11/2021Tom Barker94.9
15215104/11/2021Josh Harcourt165.9
15315204/11/2021Georgios Becatoros319.9
15415304/11/2021Julian Evans50.7
15515404/11/2021Giles Forster220
15615504/11/2021denis artunse380
15715604/11/2021Douglas McMahon29.9
15815704/11/2021Dr. M.S. Amin104.85
15915804/11/2021Simon Ward131.55
16015904/11/2021Sam Cunningham-king59.9
16116004/11/2021ZANONE GIANCARLO154.95
16216104/11/2021David Wood0
16316204/11/2021Ercan Gurlek104.85
16416304/11/2021Hasan Hashwani279.65
16516404/11/2021ANDREW MCMASTER79.95
16616504/11/2021Jon Bentley0
16716604/11/2021MICHAEL lawson103.96
16816704/11/2021Philip Chi Ho152.75
16916804/11/2021Michael Roberts0
17016904/11/2021Alex Wisbey65.12
17117004/11/2021Gavin Rowlands27.9
17217104/11/2021Marta Lima263.5
17317204/11/2021Lauren Cloud69.85
17417304/11/2021Sean Phillips34.95
17517404/11/2021Paul Argent69.85
17617504/11/2021Anam Choudrey64.8
17717603/11/2021Juan Carlos Gallardo109.75
17817703/11/2021Khadeja Nayed39.9
17917803/11/2021Roberto Barcellona139.95
18017903/11/2021Chris Roberts169.9
18118003/11/2021joel loader55.85
18218103/11/2021Stephen Cotterill89.95
18318203/11/2021Andrew Derbyshire44.9
18418303/11/2021Matthew Hepworth89.95
18518403/11/2021Carlos De Aniceto124.85
18618503/11/2021Edward Davies129.67
18718603/11/2021Mike Wort109.95
18818703/11/2021Paul Atherton90
18918803/11/2021Mark Telford69.85
19018903/11/2021Vanessa Hetherington84.8
19119003/11/2021Stacey Leigh Spalding103.96
19219103/11/2021Peter Sheath0
19319203/11/2021Neil White320
19419303/11/2021Andrew Jefferies100
19519403/11/2021Paul Klaiber108.95
19619503/11/2021shaun gibson130
19719603/11/2021DARREN BLAKE119.65
19819703/11/2021Garry Smith104.9
19919803/11/2021June Li247.88
20019903/11/2021Martin Miller116.9
20120003/11/2021oscar stevenson89.95
20220103/11/2021Jamie Eagell198
20320203/11/2021Jonathan Rivers0
20420303/11/2021Chris Wiggins97.12
20520403/11/2021Chris Socratous120
20620503/11/2021Umair Saeed199.95
20720603/11/2021Maureen Bennett0
20820703/11/2021Jas Rajasansir46.85
20920803/11/2021Nick Day49.9
21020903/11/2021Akeel Karim111.88
21121003/11/2021Tony Yuill29.9
21221103/11/2021Andrew Smellie139.95
21321203/11/2021andrew murrin79.95
21421303/11/2021Sylvia Chai225.9
21521403/11/2021Tim Tharratt94.95
21621503/11/2021Carl Kirby100.75
21721603/11/2021SHAMEEL SUHAIL51.9
21821702/11/2021Werda Botan59.85
21921802/11/2021Angeline Heffer283.72
22021902/11/2021Andrew Steel125
22122002/11/2021Barry Haggerty160
22222102/11/2021Yiling Zhu80
22322202/11/2021Ryan Nicholls89.95
22422302/11/2021Angela Garner144.95
22522402/11/2021Ioannis Tanes160
22622502/11/2021Pavel Dubrouski355.79
22722602/11/2021Rhys Jones117.9
22822702/11/2021Justin Mycroft99.95
22922802/11/2021Paul McDermid101.58
23022902/11/2021SAVA TIHOV199.12
23123002/11/2021Ryan Goff44.9
23223102/11/2021Victoria Hatch113
23323202/11/2021Paul White110.85
23423302/11/2021Daniel Mclaughlin109.95
23523402/11/2021Mandy Davis107.95
23623502/11/2021alun dyde124.85
23723602/11/2021Rachel Guest181.36
23823702/11/2021Tom Byford116.95
23923802/11/2021MD Belal258.9
24023902/11/2021Amir Sayes57.07
24124002/11/2021Antonio Munoz Bailey157.17
24224102/11/2021Graham Beales219.85
24324202/11/2021David Amber84.9
24424302/11/2021Suleyman Simsek60.83
24524402/11/2021Spencer Riddle114.95
24624502/11/2021M MOROS108
24724602/11/2021David Williams174.65
24824702/11/2021Connor Mchugh79.95
24924802/11/2021christopher busby60.8
25024902/11/2021Jackie Farrands61.95
25125002/11/2021David Blair101.85
25225102/11/2021Lorna Willgress100.52
25325202/11/2021Farouq Sheikh359.04
25425302/11/2021Lucas Rocha83.7
25525402/11/2021M Ahmed19.95
25625502/11/2021Nick Goodfellow104.9
25725602/11/2021Paul Richardson119.95
25825702/11/2021Przemyslaw Krajcer107.95
25925802/11/2021kingsleigh hammond153.85
26025902/11/2021Shohib Tariq130
26126002/11/2021Abraham DAHAN BENAIM182.92
26226102/11/2021David Armstrong 106.7
26326202/11/2021Calli Amiras112.25
26426302/11/2021Eddy Trivett130
26526402/11/2021Sabrina Haque-Amin102.8
26626502/11/2021Kate Tulloch38.9
26726602/11/2021Konrad Stanczyk49.9
26826702/11/2021Paul Ashcroft55
26926802/11/2021Darren Fairweather49.85
27026902/11/2021Sarah Burgess102.85
27127002/11/2021Yinka Okunade159.9
27227102/11/2021Nigel Neath0
27327202/11/2021samuel smith253.65
27427302/11/2021Ben Halleybone49.9
27527402/11/2021Rachel Denning176.8
27627502/11/2021Jayne Ankers107.55
27727602/11/2021Tristram Allen450
27827702/11/2021Chris Socratous89.75
27927802/11/2021Rob Linton100.85
28027902/11/2021Carol Petrie169.9
28128002/11/2021Robert Gunn39.9
28228102/11/2021Nikki Dicbonas144.95
28328202/11/2021Iain Russell84.8
28428302/11/2021Luke Cameron109.9
28528402/11/2021Daniel Chen104.35
28628501/11/2021Peter Arch95.8
28728601/11/2021Daniel Hadley59.9
28828701/11/2021CRISTIAN FRANKIC119.8
28928801/11/2021Mohsen Mansouri104.85
29028901/11/2021Daniel Quiney121.9
29129001/11/2021christine mills64.95
29229101/11/2021Joanne Bradbury80
29329201/11/2021Ahmed Abdel-Jaber139.75
29429301/11/2021Shayla Ali35.41
29529401/11/2021Miguel Segarra167.84
29629501/11/2021Jeff Yuen136
29729601/11/2021Stephen Neale112.9
29829701/11/2021Sharon Lai80
29929801/11/2021Richard Collins 104.9
30029901/11/2021Allison Ward99.75
30130001/11/2021Ewen Scott43.8
30230101/11/2021David Jackson156.48
30330201/11/2021Zeshan Alam100
30430301/11/2021B Chowdhury41.85
30530401/11/2021Ali Dhanji259.95
30630501/11/2021Robert Tarn80
30730601/11/2021Rudy Kathiravel117.99
30830701/11/2021Anna Deacon827.04
30930801/11/2021Ahmed Aboushelbaya287.6
31030901/11/2021Matthew David Gibbs67.43
31131001/11/2021Graham Allison130.85
31231101/11/2021ANN MARTIN146.8
31331201/11/2021mobeen ahmed111.96
31431301/11/2021Shabeena Maricar108.8
31531401/11/2021Adam Iqbal49.85
31631501/11/2021Shaheen Choudhry118.65
31731601/11/2021Alan Rudkin114.67
31831701/11/2021David Roberts80
31931801/11/2021Zohaib Siddiqui119.85
32031901/11/2021Robert Soultanaev89.95
32132001/11/2021Alex Nitka49.85
32232101/11/2021Robert Pickstock72.7
32332201/11/2021Imran Chohan452.65
32432301/11/2021P White29.9
32532401/11/2021Patricia Clements49.95
32632501/11/2021David Nolte143.45
32732601/11/2021Niall McKinney155.3
32832701/11/2021Ahmed Rawi105.7
32932801/11/2021Muhammad Hussain59.85
33032901/11/2021Geoff Brewis-Levie29.9
33133001/11/2021Mikhail Hessin120
33233101/11/2021Mark Rowe29.9
33333201/11/2021Andrew Mills80
33433301/11/2021Gerry Duffey102.9
33533401/11/2021Ejaz Ahmed109.95
33633501/11/2021Croz Croswell93.75
33733601/11/2021Simon Lace29.9
33833701/11/2021Gary Morris100
33933801/11/2021Leonardo Gonzalez70.8
34033901/11/2021Chris Branscombe Ling116.95
34134001/11/2021Gary Harrison29.9
34234101/11/2021Gary Morris184.95
34334201/11/2021carl mccaffrey49.85
34434301/11/2021Athif Hussain128.75
34534401/11/2021Elizabeth Mitchell49.85
34634501/11/2021Mohamed Hanif Omar89.9
34734601/11/2021Andrew Lucas90
34834701/11/2021Hugh Orange102.95
34934801/11/2021Zayba Sheikh49.85
35034901/11/2021David Charlesworth115
35135001/11/2021John Head80
35235101/11/2021Paul Elliott250
35335201/11/2021Heinz Forster120
35435301/11/2021Austin Straub35.85
35535401/11/2021Matthew Miller1239.9
35635501/11/2021Aidan Lundy123.21
35735601/11/2021James Joyce144.95
35835701/11/2021Paul Graham80
35935801/11/2021Sara Wells164.01
36035901/11/2021Karveh Cavalirie60.9
36136001/11/2021Patrick Mooney139.95
36236101/11/2021Robert Worters185.95
36336201/11/2021Danielle Graham49.91
36436301/11/2021Suryan Wirya Simunovic80
36536401/11/2021John Winder89.95
36636501/11/2021Simon Sankey114.95
36736601/11/2021David Benfell60.8
36836701/11/2021Toby Rainbow164.95
36936801/11/2021Graham Lewis69.95
37036901/11/2021Jobe Ferguson80
37137001/11/2021Amir Iqbal159.85
37237101/11/2021Sarah Elhatow-Senkuviene61.87
37337201/11/2021Giles Stafford114.95
37437301/11/2021Sahal Bhamjee 100
37537401/11/2021Jonathan McCrory100
37637501/11/2021Niall Stack41.91
37737601/11/2021Ori Reiss182.75
37837701/11/2021Marion Buchan100
37937801/11/2021Peter Davies0
38037901/11/2021Chris Ansell0
38138001/11/2021Lee Starmer0
38238101/11/2021Nick March0
38338201/11/2021M Ahmed110.42
38438301/11/2021Stanley Davies89.04
38538401/11/2021Peter Kwok340
38638501/11/2021Gareth Lazell199.8
38738601/11/2021Simon Fletcher89.0471
38838701/11/2021Garry Moore69.85
38938801/11/2021Toby Topcott174.85
39038901/11/2021Philip Tucker125
39139001/11/2021Tak Chi Choy108.7
39239131/10/2021Wayne Forster113.9
39339231/10/2021Omar Al-Haffar70.8
39439331/10/2021vincent masnou123.17
39539431/10/2021David Hill109.9
39639531/10/2021Ed Farrell74.95
39739631/10/2021Ignacio Farga Ninoles101.85
39839731/10/2021Ian Anderson65.9
39939831/10/2021Thomas Simmons37.75
40039931/10/2021Daniel Taylor103.85
40140031/10/2021Jon Bentley532.87
40240131/10/2021Claudiu Tirsolea109.9
40340231/10/2021Jeremy Cooke240
40440331/10/2021Alison Porter105.8
40540431/10/2021Julie Mahmoud100
40640531/10/2021Hoa Huynh59.9
40740631/10/2021Darren Clarke110.85
40840731/10/2021ALUN MARRIOTT61.8
40940831/10/2021Saif Alomani39.9
41040931/10/2021Temuulen Nanzad87.8
41141031/10/2021Roger Taslaq146.9
41241131/10/2021Phil Bird20.9
41341231/10/2021Paul Cerisola44.9
41441331/10/2021Dimitrios Skopelitis760
41541431/10/2021Liz Glover50.85
41641531/10/2021Angela Bird110.85
41741631/10/2021Melissa Shaw101.56
41841731/10/2021Munadia Lauria182.75
41941831/10/2021Rob Lovelock92.85
42041931/10/2021John Harrhy69.95
42142031/10/2021Andy Gosheron71.8
42242131/10/2021Stephen Hamer96.8583
42342231/10/2021Dawn von Bergen120
42442331/10/2021Maryam ALI39.9
42542431/10/2021Daniel Drinkwater54.95
42642531/10/2021RUSSELL KINGSNORTH180
42742631/10/2021Glen Binns130
42842731/10/2021Patrik Burstrom113.75
42942831/10/2021Gareth Keegan132
43042931/10/2021Anthony Walters135
43143031/10/2021Isa Mohammed60.95
43243131/10/2021polina zabelina150
43343231/10/2021Shakera Tayub170
43443331/10/2021Grant Read69.95
43543431/10/2021carl mccaffrey69.85
43643531/10/2021Andrew Parraco113.8
43743631/10/2021Susan Clarke55.75
43843731/10/2021Mark Harris158.95
43943831/10/2021Jon Rokk101.7
44043931/10/2021Ian Roberts69.95
44144031/10/2021Mandy Dunn152.6
44244131/10/2021Sami Agush69.95
44344231/10/2021fraser crichton69.95
44444331/10/2021Yi Yang134.95
44544431/10/2021Tatiana Livesey320.9
44644531/10/2021Jared Tovee63.4
44744631/10/2021matthew Francis43.4
44844731/10/2021Y Nazir89.85
44944831/10/2021H Suggate69.85
45044931/10/2021Martin McNally89.95
45145030/10/2021Arshad Waheed243.8
45245130/10/2021Fay Gao249.8
45345230/10/2021David Mensik179.95
45445330/10/2021Helen Brownlees68.9
45545430/10/2021Brendon Jiang164.85
45645530/10/2021Juliet O'Mahony172.86
45745630/10/2021Ross Lynam-Carter80
45845730/10/2021James Silber104.75
45945830/10/2021Simon Cotmore100
46045930/10/2021Simon Inchley122.95
46146030/10/2021Tracy Lower85.91
46246130/10/2021Joe Hankin115.95
46346230/10/2021Emma Fish103.95
46446330/10/2021Gareth Lewis154.95
46546430/10/2021GEORGE DIAMOND180
46646530/10/2021Richard Claypole106.86
46746630/10/2021Angela Scattergood30.85
46846730/10/2021Paul Allan80
46946830/10/2021Trisha Campbell85
47046930/10/2021Stephen Smith100.8
47147030/10/2021Rob FLOYD130
47247130/10/2021Nick March80
47347230/10/2021Abdul Mehmood189.95
47447330/10/2021geoff hayllar104.75
47547430/10/2021Nick Ekendahl161.38
47647530/10/2021Ilya Belyaev100
47747630/10/2021David Carr71.25
47847730/10/2021Jonathan Wilkinson128.95
47947830/10/2021Xin Jiang114.9
48047930/10/2021P J White110
48148030/10/2021Julie Mahmoud100
48248130/10/2021LAI LIU149.85
48348230/10/2021peter sheath49.95
48448329/10/2021Shane Duffield128.19
48548429/10/2021Oliver Webb218.2
48648529/10/2021Del Flint102.75
48748629/10/2021George Tebbutt86.45
48848729/10/2021Laurel Powers-Freeling62.5
48948829/10/2021Rasheed Mosaheb109.95
49048929/10/2021craig renilson120
49149029/10/2021Amardeep Chima80
49249129/10/2021Dr. M.S. Amin119.85
49349229/10/2021Nic Lee180
49449329/10/2021Julie Mahmoud110
49549429/10/2021sajid khaliq69.8
49649529/10/2021Jason Nowell215.85
49749629/10/2021Daniel Goldfarb48.8
49849729/10/2021gavin hutcheson69.8
49949829/10/2021Guy Keng95.8
50049929/10/2021FIONA SAVORY116.74
50150029/10/2021Andy Wheatley-Rae120.9
50250129/10/2021Randall Stransky120
50350229/10/2021Nigel Grant60.6274
50450329/10/2021Helen Irving50
50550429/10/2021philipp sjostrom214.32
50650529/10/2021Suzanne Osman69.83
50750629/10/2021Oran Barak0
50850729/10/2021Lee Stolworthy127.29
50950829/10/2021Bashar Al-Damen120
51050929/10/2021Gary Hilton109.8
51151029/10/2021Max Card52.8583
51251129/10/2021Ben Watson63.25
51351229/10/2021andrev pukstys103.9
51451329/10/2021Najib Murad159.55
51551429/10/2021Agnieszka Spychalska111.2
51651529/10/2021Reece Morgan93.46
51751629/10/2021Matt Stent95.9
51851729/10/2021Lan Tan114.95
51951828/10/2021Mark Brown102.9
52051928/10/2021Ashley Grewal594.9
52152028/10/2021Andrew Sutcliffe174.95
52252128/10/2021Andrew Skinner294.9
52352228/10/2021Gemma Cuff45.85
52452328/10/2021Damian Johnson560.83
52552428/10/2021Razvan Carata101.92
52652528/10/2021Ian Roberts69.95
52752628/10/2021Sophie Blair34.9
52852728/10/2021Marcin Maslowski123.17
52952828/10/2021Penny Leonard50
53052928/10/2021Jessica Crews29.95
53153028/10/2021Craig Davies210
53253128/10/2021Shane Reeves84.9
53353228/10/2021Jonathan O Toole203.8
53453328/10/2021Mary Trussler49.9
53553428/10/2021Ben Hopkins95.84
53653528/10/2021Adam Barnie167.88
53753628/10/2021Janice Kohut110
53853728/10/2021Kenton Simms11.95
53953828/10/2021Guy Orr279.9
54053928/10/2021Diego Rivera89.17
54154028/10/2021Huw Jones144.95
54254128/10/2021greg krieger79.95
54354228/10/2021Andrew Lazarou111.85
54454328/10/2021Kenton Simms51.85
54554428/10/2021Farouq Sheikh415
54654527/10/2021oscar stevenson59.9
54754627/10/2021Rupert Shield116.95
54854727/10/2021Rogier Graeve354.95
54954827/10/2021Hasna Kahlalech107.85
55054927/10/2021Darren Shiels342.995
55155027/10/2021Chris Knight332.28
55255127/10/2021Daniel Cowan48.8
55355227/10/2021aaron probert96.78
55455327/10/2021Lee Starmer 155.47
55555427/10/2021Matthew Green87.96
55655527/10/2021Lawrence Wareham129.95
55755627/10/2021Gideon Holdway59.47
55855727/10/2021Arran Hill44.9
55955827/10/2021David Armstrong 129.6
56055927/10/2021Tim Burrows27.9
56156027/10/2021Glen Bagshaw44.9
56256127/10/2021PAT HULME90
56356227/10/2021Gerald Donegan214
56456327/10/2021Farhana Nabi69.95
56556427/10/2021Rafik Bedair104.85
56656527/10/2021Will Edwards164.95
56756627/10/2021Christopher Waites114.67
56856727/10/2021Asad Khalil70.8
56956827/10/2021The Dude204.8083
57056927/10/2021Paul Morrison120
57157027/10/2021Kevin Hassell85
57257127/10/2021Dimitri Vvedensky37.75
57357227/10/2021Govinda Chana180
57457327/10/2021shahid patel170
57557427/10/2021Andy George220
57657527/10/2021Steven Mulrain54.9
57757627/10/2021Constantine Folkes93.46
57857727/10/2021Victoria Browning135
57957827/10/2021Martyn Harrison79.95
58057927/10/2021Mustafa Terzi179.85
58158027/10/2021Nick March271.92
58258127/10/2021John Young139.88
58358226/10/2021Ian McManus100.7
58458326/10/2021Naoko Boys64.9
58558426/10/2021Jeffrey Lee60
58658526/10/2021Robert McGregor50
58758626/10/2021Amina Begum95.88
58858726/10/2021Steve Fisher64.9
58958826/10/2021Daniel Edwards200
59058926/10/2021Chelmen Liwen Chang100.6
59159026/10/2021Matt Harrington109.9
59259126/10/2021Graham Hogan59.9
59359226/10/2021Paul Cooper109.85
59459326/10/2021dao phung104.85
59559426/10/2021Darryn ****aday 139.75
59659526/10/2021Sebastian Orton88.28
59759626/10/2021Damien Jackman60.95
59859726/10/2021Danny Wood104.9
59959826/10/2021Alex Basmas206.24
60059926/10/2021Darren Rowlands127.6
60160026/10/2021Christina Holloway152.65
Sheet3
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
453
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Amazing! Although I don't know how its done aha. What formula did you use to convert the date to the right format?
The last two criteria will be hardest to work out. Would you be able to explain what you have done to get the results please?
When I construct this kind of thing, I'll make 'bite sized' columns of data that do some of the subtasks, but always with dynamic array formulas (with the # operator) so I can reconstruct it into one formula at the end. I couldn't figure out why columns I and J weren't allowing that to work correctly. Here is my spreadsheet showing those extra columns and how they were used. It gets complex and isn't easy to explain, but maybe easier to show my work:

MrExcelPlayground4.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1OrderOrdered DateNameTotalNew customers in the last weekCustomers in the last week that ordered at least once in the previous 6 monthsCustomers in the last week that haven't ordered in the last 12 months priorCustomers in the last week that ordered more than once in the previous 6 monthsCustomers in the last week that ordered exactly once in the previous 6 monthsMore than onceexactly oncePeople ordering more than 7 days, but less than 181Ordered in the last week:Ordered more than 1 week agomatch of people ordering this week and if they ordered in the prior 6 months
2111/7/21 22:13Ann Vicens-Morton104.9Ann Vicens-MortonRobert TarnAnn Vicens-Morton#CALC!Robert Tarn00Pierluigi ChiusoloAnn Vicens-MortonAnn Vicens-MortonPierluigi Chiusolo01Jon RokkPierluigi ChiusoloPierluigi Chiusolo
3211/7/21 21:15David Yates364.8David YatesDavid Yates00Darrell PinkneyDavid YatesDavid YatesDarrell Pinkney01Tom BarkerDarrell PinkneyDarrell Pinkney
4311/7/21 21:09Aisha Patel104Aisha PatelAisha Patel00Simon O'BrienAisha PatelAisha PatelSimon O'Brien01Dr. M.S. AminSimon O'BrienSimon O'Brien
5411/7/21 21:01frankie o'shea74.95frankie o'sheafrankie o'shea00Nick Mundayfrankie o'sheafrankie o'sheaNick Munday01Jon BentleyNick MundayNick Munday
6511/7/21 20:35Tracy Pickles139.85Tracy PicklesTracy Pickles00Jon RokkTracy PicklesTracy PicklesJon Rokk02Peter SheathSean PedrosaJon Rokk
7611/7/21 20:10John Braid91.88John BraidJohn Braid00Sean PedrosaJohn BraidJohn BraidSean Pedrosa01Chris SocratousAlen CorakSean Pedrosa
8711/7/21 19:53Marc Gilly114.85Marc GillyMarc Gilly00Alen CorakMarc GillyMarc GillyAlen Corak01M AhmedHenry MariaAlen Corak
9811/7/21 19:50Jeremy Kirk110Jeremy KirkJeremy Kirk00Henry MariaJeremy KirkJeremy KirkHenry Maria01Gary MorrisMichel FreyHenry Maria
10911/7/21 19:38Jonathan Pearson 120.6Jonathan Pearson Jonathan Pearson 00Michel FreyJonathan Pearson Jonathan Pearson Michel Frey01carl mccaffreyNiall MaherMichel Frey
111011/7/21 19:24K Fowler125.9K FowlerK Fowler00Niall MaherK FowlerK FowlerNiall Maher01Nick MarchBenjamin GillNiall Maher
121111/7/21 19:20JOHN GRIMMETT124.85JOHN GRIMMETTJOHN GRIMMETT00Benjamin GillJOHN GRIMMETTJOHN GRIMMETTBenjamin Gill01Julie MahmoudVic WeiBenjamin Gill
131211/7/21 18:52Joe Kemm145.8Joe KemmJoe Kemm00Vic WeiJoe KemmJoe KemmVic Wei01Robert SaundersVic Wei
141311/7/21 18:51Anthony Yaskin140Anthony YaskinAnthony Yaskin00Robert SaundersAnthony YaskinAnthony YaskinRobert Saunders01Giovanni ScarcelliRobert Saunders
151411/7/21 18:50Mark Downing102.44Mark DowningMark Downing00Giovanni ScarcelliMark DowningMark DowningGiovanni Scarcelli01Alan NewtonGiovanni Scarcelli
161511/7/21 18:46Aaron Denton186.9Aaron DentonAaron Denton00Alan NewtonAaron DentonAaron DentonAlan Newton01Gideon HoldwayAlan Newton
171611/7/21 18:45Sam Hurrell-wills70.05Sam Hurrell-willsSam Hurrell-wills00Gideon HoldwaySam Hurrell-willsSam Hurrell-willsGideon Holdway01Josh HarcourtGideon Holdway
181711/7/21 18:43Elaine Campbell204.95Elaine CampbellElaine Campbell00Tom BarkerElaine CampbellElaine CampbellTom Barker02Georgios BecatorosTom Barker
191811/7/21 18:32Cameron Troops31.85Cameron TroopsCameron Troops00Tom BarkerCameron TroopsCameron TroopsJosh Harcourt01Julian EvansTom Barker
201911/7/21 18:23Lyndsay Traherne-Williams64.9Lyndsay Traherne-WilliamsLyndsay Traherne-Williams00Josh HarcourtLyndsay Traherne-WilliamsLyndsay Traherne-WilliamsGeorgios Becatoros01Giles ForsterJosh Harcourt
212011/7/21 17:59Gareth Lawrence80Gareth LawrenceGareth Lawrence00Georgios BecatorosGareth LawrenceGareth LawrenceJulian Evans01denis artunseGeorgios Becatoros
222111/7/21 17:46Jade Pennick39.8Jade PennickJade Pennick00Julian EvansJade PennickJade PennickGiles Forster01Douglas McMahonJulian Evans
232211/7/21 17:36Sam Horner105.8Sam HornerSam Horner00Giles ForsterSam HornerSam Hornerdenis artunse01Simon WardGiles Forster
242311/7/21 17:31Robert Tarn103.75Tahir RiazTahir Riaz0164denis artunseTahir RiazRobert TarnDouglas McMahon1711Sam Cunningham-kingdenis artunse
252411/7/21 17:01Tahir Riaz203.15Toby MillerToby Miller00Douglas McMahonToby MillerTahir RiazDr. M.S. Amin02ZANONE GIANCARLODouglas McMahon
Sheet36
Cell Formulas
RangeFormula
F2:F131F2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,B2:B500<TODAY()-7)),0),0)=0)
G2G2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),0),0)>0)
H2:H131H2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-366))),0),0)=0)
I2I2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),L2#>0)
J2J2=FILTER(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),M2#>0)
L2:L132L2=IFNA(MATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),FILTER(UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),COUNTIFS(N2#,UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))))>1),0),0)
M2:M132M2=IFNA(MATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),FILTER(UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),COUNTIFS(N2#,UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))))=1),0),0)
N2:N368N2=FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))
O2:O131O2=FILTER(P2#,R2#=0)
P2:P132P2=UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7))
Q2:Q356Q2=UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181)))
R2:R132R2=IFNA(XMATCH(UNIQUE(FILTER(C2:C500,B2:B500>=TODAY()-7)),UNIQUE(FILTER(C2:C500,B2:B500<TODAY()-7)),0),0)
S2:S356S2=COUNTIFS(N2#,Q2#)
U2:U12U2=FILTER(UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))),COUNTIFS(W2#,UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))))>1)
V2:V345V2=FILTER(Q2#,S2#=1)
W2:W368W2=FILTER(C2:C500,B2:B500<TODAY()-7,UNIQUE(FILTER(C2:C500,(B2:B500<TODAY()-7)*(B2:B500>TODAY()-181))))
Dynamic array formulas.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,036
Messages
5,767,775
Members
425,431
Latest member
Sayson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top