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!

 

[JimM]

As long as you don't have the same unit number against more than 1 item in col B


=IF(COUNTIF($B$1:B1,B1)=2,"TRUE","FALSE")

 

[Aladin Akyurek]

Your references are confusing. What is the exact range for this data, A:D with headers in row 1?

 

[MrSak87]

OK so A,B,C,D across the top and the values start at 2

 

[MrSak87]

Get#name? error messages for every B1

 

[MrSak87]

Oh no it didnt. None have worked so far

 

[Marcelo Branco]

Try this (edited)

E1
=IF(D1="B1",IF(COUNTIFS(A$1:A1,A1,D$1:D1,"B1")=1,TRUE,FALSE),FALSE)

copy down

M.

 

[CharlesChuckieCharles]

try in column E


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


followed by lessthan symbol + "A2) ",TRUE,FALSE)"
<A2),TRUE,FALSE)< html>

keeps disappearing

 

[Aladin Akyurek]

OK so A,B,C,D across the top and the values start at 2

Ok, is this what we should get?

unit	x	y	z
1	SAND	P1		TRUE
1	SAND	P1B1	B1	FALSE
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	TRUE
2	CLAY	P3		FALSE
2	CLAY	P3B1	B1	FALSE
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	TRUE
3	CLAY	P5Q1	Q1	FALSE
3	CLAY	P5B2	B2	FALSE
3	CLAY	P6		FALSE
3	CLAY	P6B1	B1	FALSE
3	CLAY	P6Q1	Q1	FALSE
3	CLAY	P6B2	B2	FALSE
3	CLAY	P7		FALSE
3	SAND	P7B1	B1	TRUE
3	CLAY	P7B2	B2	TRUE
3	CLAY	P7U1	U1	FALSE
3	CLAY	P7Q1	Q1	FALSE
3	CLAY	P7B3	B3	FALSE

<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>
</TBODY>

 

[MrSak87]

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"