IF with LEFT Function

[Sultix]

Hello,

i cant seem to workaround this without making a new column..

A
1- 180522-10
2- 180522-20
3- 180523-10
4- 180523-20
5- 180523-30
6- 180524-10


=IF(LEFT(A1;6)=(LEFT(A2;6)*"DOUBLE";"OK")

meaning A1 should compared with A2, A2 compared with A3 and so on..

can you help me pls?

thank you

[DonkeyOte]

Please Login or Register  to view this content.

[Sultix]

Please Login or Register  to view this content.

thanks

works now with these changes aswell =if(left(a1;6)=left(a2;6)

i am really confused as commas are not accepted in excel 2007 and the * arent either..

thanks for ur help

[DonkeyOte]

Commas vs Semi-Colon -- will depend entirely upon your regional settings.

As for asterisk, in your formula this is not a valid operator... in what context were you trying to use it ?

[Sultix]

thanks for the clarifying..

dunno where i got the asterix, i just thought i had to enter it.

[martindwilson]

you might want to consider

=SUMPRODUCT(--(LEFT($A$1:$A$100,6)=LEFT(A1,6))) which would tell you how many of each in same column in case thet weren't next to each other

[DonkeyOte]

If you did want to do that you don't need to use an array approach, just use COUNTIF:

=COUNTIF(A1:A100,LEFT(A1,6)&"*")

[Sultix]

Not really, the last both formulas are only counting same cells within column.

The first Formula is what i needed... which i corrected thanks to DonkeyOte..