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
[TABLE="width: 224"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P1B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P1Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P1U1[/TD]
[TD]U1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P1B2[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P2B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SAND[/TD]
[TD]P2B2[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P2Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P3B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P3Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P3U1[/TD]
[TD]U1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P4B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P4Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P4Q2[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CLAY[/TD]
[TD]P5B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P5U1[/TD]
[TD]U1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P5Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P5B2[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P6B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P6Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P6B2[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]SAND[/TD]
[TD]P7B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P7B2[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P7U1[/TD]
[TD]U1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P7Q1[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CLAY[/TD]
[TD]P7B3[/TD]
[TD]B3[/TD]
[/TR]
</tbody>[/TABLE]


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?

[TABLE="width: 240"]
<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]unit[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]x[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]z[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P1B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P1Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P1U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P1B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P2B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P2B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P2Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P3B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P3Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P3U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P4[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P4B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P4Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P4Q2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P5[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P5B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P5U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P5Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P5B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P6[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P6B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P6Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P6B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P7[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SAND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P7B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P7B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P7U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]U1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P7Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Q1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CLAY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]P7B3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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"