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!

 

[MrSak87]

This is what we should get

[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]Unit[/TD]
[TD="width: 64"]Lith[/TD]
[TD="width: 64"]Sample[/TD]
[TD="width: 64"]Sub[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl29, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl28"]P1[/TD]
[TD="class: xl27"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl26"]P1B1[/TD]
[TD="class: xl33"]B1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P1Q1[/TD]
[TD="class: xl25"]Q1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P1U1[/TD]
[TD="class: xl25"]U1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P1B2[/TD]
[TD="class: xl25"]B2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P2[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P2B1[/TD]
[TD="class: xl25"]B1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P2B2[/TD]
[TD="class: xl25"]B2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P2Q1[/TD]
[TD="class: xl25"]Q1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P3[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P3B1[/TD]
[TD="class: xl25"]B1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P3Q1[/TD]
[TD="class: xl25"]Q1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P3U1[/TD]
[TD="class: xl25"]U1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P4[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P4B1[/TD]
[TD="class: xl25"]B1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P4Q1[/TD]
[TD="class: xl25"]Q1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P4Q2[/TD]
[TD="class: xl25"]Q2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P5[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P5B1[/TD]
[TD="class: xl25"]B1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P5U1[/TD]
[TD="class: xl25"]U1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P5Q1[/TD]
[TD="class: xl25"]Q1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P5B2[/TD]
[TD="class: xl25"]B2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P6[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P6B1[/TD]
[TD="class: xl25"]B1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P6Q1[/TD]
[TD="class: xl25"]Q1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P6B2[/TD]
[TD="class: xl25"]B2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P7[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl35"]SAND[/TD]
[TD="class: xl24"]P7B1[/TD]
[TD="class: xl25"]B1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P7B2[/TD]
[TD="class: xl25"]B2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3[/TD]
[TD="class: xl34"]CLAY[/TD]
[TD="class: xl24"]P7U1[/TD]
[TD="class: xl25"]U1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 320"]
<TBODY>[TR]
[TD="width: 64"]Unit
[/TD]
[TD="width: 64"]Lith
[/TD]
[TD="width: 64"]Sample
[/TD]
[TD="width: 64"]Sub
[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl29, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl28"]P1
[/TD]
[TD="class: xl27"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl26"]P1B1
[/TD]
[TD="class: xl33"]B1
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P1Q1
[/TD]
[TD="class: xl25"]Q1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P1U1
[/TD]
[TD="class: xl25"]U1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P1B2
[/TD]
[TD="class: xl25"]B2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P2
[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P2B1
[/TD]
[TD="class: xl25"]B1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl30, align: right"]1
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P2B2
[/TD]
[TD="class: xl25"]B2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P2Q1
[/TD]
[TD="class: xl25"]Q1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P3
[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P3B1
[/TD]
[TD="class: xl25"]B1
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P3Q1
[/TD]
[TD="class: xl25"]Q1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P3U1
[/TD]
[TD="class: xl25"]U1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P4
[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P4B1
[/TD]
[TD="class: xl25"]B1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P4Q1
[/TD]
[TD="class: xl25"]Q1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P4Q2
[/TD]
[TD="class: xl25"]Q2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P5
[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl31, align: right"]2
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P5B1
[/TD]
[TD="class: xl25"]B1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P5U1
[/TD]
[TD="class: xl25"]U1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P5Q1
[/TD]
[TD="class: xl25"]Q1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P5B2
[/TD]
[TD="class: xl25"]B2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P6
[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P6B1
[/TD]
[TD="class: xl25"]B1
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P6Q1
[/TD]
[TD="class: xl25"]Q1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P6B2
[/TD]
[TD="class: xl25"]B2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P7
[/TD]
[TD="class: xl25"][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl35"]SAND
[/TD]
[TD="class: xl24"]P7B1
[/TD]
[TD="class: xl25"]B1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P7B2
[/TD]
[TD="class: xl25"]B2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="class: xl32, align: right"]3
[/TD]
[TD="class: xl34"]CLAY
[/TD]
[TD="class: xl24"]P7U1
[/TD]
[TD="class: xl25"]U1
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</TBODY>[/TABLE]

D2, copied down:

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