Macro to Tell me if a customer is new old or existing?

[tonywatsonhelp]

Hi Everyone,

I have a list of sales going back to 1st jan 2017

I'm want to have a macro tell me the staus of that client according to what has happened up until that row,
so for example,

all cilents are listed down Column C,

All Dates are listed down column G the sheet is in date order old to new.

in column I i want to start at row 2 look at the clients name and see if they have ordered from us before by only looking up the rows so (row two bad example) but row 100, take name Dave, look for dave, if dave appears before this row he is an existing client and in column G goes "existing" if not he is new and in g goes "new" if he is existing but I also need to know if he ordered before 2018 so if the date is after 1/1/2018 then if dave ordered in 2017 I want column H to Say "Loyal"

also I want to rerun the macro but no need to change the data so can it be stat to only fill in from the last row of data in column G?

please help if you can

Tony

 

[Momentman]

Something like this maybe (with a formula)

Book1
	C	D	E	F	G	H	I
1	Customers				Date		Status
2	Dave				13-Mar-18
3	tony				16-Jun-18
4	Harry				7-Nov-15
5	Clarus				10-Mar-15
6	Armani				12-Jan-18
7	tarzan				17-Aug-15
8	James				1-Oct-18
9	sadiq				17-Aug-18
10	John				23-Jul-15
11	Harry				19-Apr-17		Loyal
12	william				28-Jan-16		NEW
13	Tony				11-Feb-16		OLD
14	Dave				28-Aug-18		OLD
15	Clarus				19-May-17		Loyal
16	Raheem				17-Feb-15		NEW
17	Tom				26-Feb-15		NEW
18	Bill				10-Mar-15		NEW
19	Michael				5-Oct-18		NEW
20	Cheikh				5-May-16		NEW
21	Sadiq				13-Aug-18		OLD
22	Donald				28-May-17		NEW
23	Lyndon				16-Apr-17		NEW
Sheet2

Cell Formulas
Range	Formula
I11	=IF(COUNTIF($C$2:$C$10,$C11)=0,"NEW",IF(COUNTIFS($C$2:$C10,$C11,$G$2:$G10,"<01/01/2018")>0,"Loyal","OLD"))