setting cell value to 1 based on 2 conditions

[d3ad3y3]

Hi everyone, i need help regarding the formula i try to use, i use 3 columns, Date, Activity and Validity. Validity should be set to 1 at the end of the last same date if and only if there is atleast 1 Service each date, see example below

date	activity	validity
1-Oct	Deliver
1-Oct	Service
1-Oct	Service	1
2-Oct	Pickup
2-Oct	Deliver
3-Oct	Service	1
4-Oct	Service	1

tried to use countif but if the first activity of the day is not Service, the validity wont be set to 1.

 

[etaf]

try
=IF(AND(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service"),A2<>A3),1,"")

Will the dates always be in the correct order - if not this will not work

Book1
	A	B	C	D
1	date	activity	validity	Formula
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1
8	4-Oct	Service	1	1
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IF(AND(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service"),A2<>A3),1,"")

Will service evee come before items like pickup or delivery on the same date - or will it always be last entry as shown in example
ONLY i added another item same date example 4-oct but with delivery in as last entry - still has service on the 4-oct just not the last item - and so shows a 1 against the last item ONLY where there is a service anywhere on the same date

Book1
	A	B	C	D
1	date	activity	validity	Formula
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1
8	4-Oct	Service	1
9	4-Oct	Deliver		1
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(AND(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service"),A2<>A3),1,"")

 

[d3ad3y3]

thank you for the solution, regarding your questions:
Will the dates always be in the correct order - if not this will not work?
yes, the dates will always be on the ascending order, sometimes it will skip a day though like october 1 then october 3 on the next row

Will service come before pickup or delivery on the same date - or will it always be last as shown
the service will appear in either before or after, there are more activities listed on the excel file i use, i only provided 3 types on this example.

thank you very much for your help, been trying to figure this out for days XD

 

[d3ad3y3]

though to be clean, can we make it that the 1 will appear on the last Service of the day?

 

[etaf]

i think this is overcomplicated a formula - seems to work - but i have not fully tested

=IF(AND(B2="service",COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service"),MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,"service")) =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

needs to be tried out on all possible entries
it would be much simplier if you did NOT have service more than once in a date - but you show service entry twice in 1-oct
and I assume you only want a 1 against the last 1 of those service entries and NOT on both

Book1
	A	B	C	D	E
1	date	activity	validity	Formula	Show on service
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1	1
8	4-Oct	Service	1		1
9	4-Oct	Deliver		1
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(AND(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service"),A2<>A3),1,"")
E2:E9	E2	=IF(AND(B2="service",COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service"),MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,"service")) =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

 

[d3ad3y3]

yes, the service will most likely appear 1 or more per day on the database I am working on, ill test the updated code if it will be correct on every scenario. thank you again.

 

[etaf]

you only need to use

=IF(AND(B2="service",MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,"service")) =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

as we want a 1 against service - then we test B2 to see if it contains the word service
then we just need to see if that is the lastest entry of service for that date
hence the countifs
HOW many Services are in that range for that date COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") - and then see if that particulary entry is the MAX for that date MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,"service"))

we dont need the other countif in to see if there is service in that date range - as we are already testing the cell for service B2="service"

Book1
	A	B	C	D
1	date	activity	validity	formula reduced
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1
8	4-Oct	Service	1	1
9	4-Oct	Deliver
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(AND(B2="service",MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,"service")) =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

if you just want it to appear against service - even if it appears more than once on a date - its simply be IF( B2="service",1,"")

 

[etaf]

on reflection, looking at another problem
I realised you dont need the MAX either
=IF(AND(B2="service",COUNTIFS($A$2:A2,A2,$B$2:B2,"service") =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

Book4
	A	B	C	D
1	date	activity	validity	formula reduced
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1
8	4-Oct	Service	1	1
9	4-Oct	Deliver
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(AND(B2="service",COUNTIFS($A$2:A2,A2,$B$2:B2,"service") =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

 

[d3ad3y3]

on reflection, looking at another problem
I realised you dont need the MAX either
=IF(AND(B2="service",COUNTIFS($A$2:A2,A2,$B$2:B2,"service") =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

Book4
	A	B	C	D
1	date	activity	validity	formula reduced
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1
8	4-Oct	Service	1	1
9	4-Oct	Deliver
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(AND(B2="service",COUNTIFS($A$2:A2,A2,$B$2:B2,"service") =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")

alright, as of now i dont experience any unusual using the last long formula with max(), ill try to replace the formula with this shorten one, i think can shorten it more by replacing the 2nd and 3rd "service" string with B2 instead. thanks for your help again.

 

[Peter_SSs]

I might have missed something in the thread but wouldn't this simpler formula in col E do the same thing?

24 11 05.xlsm
	A	B	C	D	E
1	date	activity	validity	formula reduced	formula reduced
2	1-Oct	Deliver
3	1-Oct	Service
4	1-Oct	Service	1	1	1
5	2-Oct	Pickup
6	2-Oct	Deliver
7	3-Oct	Service	1	1	1
8	4-Oct	Service	1	1	1
9	4-Oct	Deliver
Sheet2 (2)

Cell Formulas
Range	Formula
D2:D9	D2	=IF(AND(B2="service",COUNTIFS($A$2:A2,A2,$B$2:B2,"service") =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"service") ),1,"")
E2:E9	E2	=IF(AND(B2="service",COUNTIFS(A2:A$100,A2,B2:B$100,B2)=1),1,"")