IF functions slightly too complicated for my tiny brain

[MrSak87]

Hey guys,

Literally had no idea of what to put for the title of this one but here it goes. So in a separate column say column E I want a formula that returns "true" when the following condition is met:

B1 occurs for the first time in a unit but not any other time unless the unit changes. Units are from A1 downwards (numbers 1,2 and 3 in this example) If B1 occurs again but the unit hasn't changed then I don't want "true" to appear.

So in the example below E1 would = false, E2 would equal true, Then false all the way to E10, true for E11 etc.

unit

1	SAND	P1
1	SAND	P1B1	B1
1	SAND	P1Q1	Q1
1	SAND	P1U1	U1
1	SAND	P1B2	B2
1	SAND	P2
1	SAND	P2B1	B1
1	SAND	P2B2	B2
2	CLAY	P2Q1	Q1
2	CLAY	P3
2	CLAY	P3B1	B1
2	CLAY	P3Q1	Q1
2	CLAY	P3U1	U1
2	CLAY	P4
2	CLAY	P4B1	B1
2	CLAY	P4Q1	Q1
2	CLAY	P4Q2	Q2
2	CLAY	P5
2	CLAY	P5B1	B1
3	CLAY	P5U1	U1
3	CLAY	P5Q1	Q1
3	CLAY	P5B2	B2
3	CLAY	P6
3	CLAY	P6B1	B1
3	CLAY	P6Q1	Q1
3	CLAY	P6B2	B2
3	CLAY	P7
3	SAND	P7B1	B1
3	CLAY	P7B2	B2
3	CLAY	P7U1	U1
3	CLAY	P7Q1	Q1
3	CLAY	P7B3	B3

<tbody>
</tbody>

Sorry if the explanation is rubbish but I think you'll get the idea. Cheers!

 

[MrSak87]

This is what we should get

Unit	Lith	Sample	Sub
1	SAND	P1		FALSE
1	SAND	P1B1	B1	TRUE
1	SAND	P1Q1	Q1	FALSE
1	SAND	P1U1	U1	FALSE
1	SAND	P1B2	B2	FALSE
1	SAND	P2		FALSE
1	SAND	P2B1	B1	FALSE
1	SAND	P2B2	B2	FALSE
2	CLAY	P2Q1	Q1	FALSE
2	CLAY	P3		FALSE
2	CLAY	P3B1	B1	TRUE
2	CLAY	P3Q1	Q1	FALSE
2	CLAY	P3U1	U1	FALSE
2	CLAY	P4		FALSE
2	CLAY	P4B1	B1	FALSE
2	CLAY	P4Q1	Q1	FALSE
2	CLAY	P4Q2	Q2	FALSE
2	CLAY	P5		FALSE
2	CLAY	P5B1	B1	FALSE
3	CLAY	P5U1	U1	FALSE
3	CLAY	P5Q1	Q1	FALSE
3	CLAY	P5B2	B2	FALSE
3	CLAY	P6		FALSE
3	CLAY	P6B1	B1	TRUE
3	CLAY	P6Q1	Q1	FALSE
3	CLAY	P6B2	B2	FALSE
3	CLAY	P7		FALSE
3	SAND	P7B1	B1	FALSE
3	CLAY	P7B2	B2	FALSE
3	CLAY	P7U1	U1	FALSE

<tbody>
</tbody>

 

[CharlesChuckieCharles]

reply mines works but the web site treats the Less than symbol as something else when i post it

=IF(AND(D2="B1", COUNTIF($E$1:E1,TRUE) lessthan A2),TRUE,FALSE)


what it does is say if current D row = "B1" and Current count of trues above is less than the A column number then its true otherwise false

 

[MrSak87]

Charles you beauty!!!!!!!!!!!!!!!!!!!!!! Works spot on. Thank you so much

 

[Marcelo Branco]

Sorry non of these solutions appear to work. With Marcelos I get #NAME? on every "B1" and the other formula Charles' formula is incomplete. If I complete it I get a 0 on every "B1"

What version of Excel are you using?

M.

 

[Aladin Akyurek]

This is what we should get

Unit	Lith	Sample	Sub
1	SAND	P1		FALSE
1	SAND	P1B1	B1	TRUE
1	SAND	P1Q1	Q1	FALSE
1	SAND	P1U1	U1	FALSE
1	SAND	P1B2	B2	FALSE
1	SAND	P2		FALSE
1	SAND	P2B1	B1	FALSE
1	SAND	P2B2	B2	FALSE
2	CLAY	P2Q1	Q1	FALSE
2	CLAY	P3		FALSE
2	CLAY	P3B1	B1	TRUE
2	CLAY	P3Q1	Q1	FALSE
2	CLAY	P3U1	U1	FALSE
2	CLAY	P4		FALSE
2	CLAY	P4B1	B1	FALSE
2	CLAY	P4Q1	Q1	FALSE
2	CLAY	P4Q2	Q2	FALSE
2	CLAY	P5		FALSE
2	CLAY	P5B1	B1	FALSE
3	CLAY	P5U1	U1	FALSE
3	CLAY	P5Q1	Q1	FALSE
3	CLAY	P5B2	B2	FALSE
3	CLAY	P6		FALSE
3	CLAY	P6B1	B1	TRUE
3	CLAY	P6Q1	Q1	FALSE
3	CLAY	P6B2	B2	FALSE
3	CLAY	P7		FALSE
3	SAND	P7B1	B1	FALSE
3	CLAY	P7B2	B2	FALSE
3	CLAY	P7U1	U1	FALSE

<TBODY>
</TBODY>

D2, copied down:

=AND($D2="B1",COUNTIFS($A$2:A2,A2,$D$2:D2,"B1")=1)