Pull letters and validating cells below to see if the letters are same

**Rickage** · 07-15-2010, 07:25 PM

I have been trying to make a field that selects the first 4 letters of a cell and then will see if the cell below has the same first 4 letters and so on until it doesn't.

For example

lets say
A1 = AHJJ
A2 = AHJJ
A3 = AHJJ
A4 =AHKK

I would like it to select the first three and not the fourth because A4 doesnt validate

each one of these are followed by numbers for example

A1 = AHJJ10
A2 = AHJJ11
A3 = AHJJ12

I would like it to select the top cell each time it finds a new letter sequence and display it in that cell as I need the one ith the lowest value which will also be the top reference.

I hope that is clear.

Thank you

Rick

**Rickage** · 07-15-2010, 07:36 PM

So far I have LEFT (A6,4)

This is grabbing the first 4 letters but then I ould like it to see if the cell below has the same first 4 letters ad keep running a loop or something to check the cells underneath until one doesnt identify.

**martindwilson** · 07-15-2010, 08:09 PM

are they unique ie the same 6 char group does not appear further down the list ?
if so
=IF(COUNTIF($A$1:A1,LEFT(A1,4)&"*")=1,A1,"")

**Rickage** · 07-15-2010, 08:13 PM

Yes they are all unique
The first 4 letters can be the same but the following numbers are all unique as client codes

I have been trying to use the IF and LEFT function but I can't get it to work properly not sure if there is a better function I could be using or if I am missing a few things

**martindwilson** · 07-15-2010, 08:20 PM

see my last post

**Rickage** · 07-15-2010, 08:21 PM

wow Thankyou very much!
It works but one thing I dont understand is if its referring to the A1 in every formula why does it still work?
Because I would have thought soon as the first four letters change it wouldn't use it anymore?

Thanks so much

**martindwilson** · 07-15-2010, 08:25 PM

hm as its dragged down it should change to
=IF(COUNTIF($A$1:A2,LEFT(A2,4)&"*")=1,A2,"")
.
.
.
=IF(COUNTIF($A$1:A100,LEFT(A100,4)&"*")=1,A100,"")

**Rickage** · 07-15-2010, 08:32 PM

Ok thankyou I understood that part just not the $A$1 part but that doesn't matter.

I was also wondering for the parts that are false is it possible for it to refer to last cell above that was true and display that?

Just figured it out

=IF(COUNTIF($A$1:A11,LEFT(A11,4)&"*")=1,A11,H10)

Thank you again for all your help

**martindwilson** · 07-15-2010, 08:42 PM

so you want top and bottom values?
=IF(COUNTIF($A$1:A1,LEFT(A1,4)&"*")=1,A1,IF(COUNTIF($A$1:$A$1000,LEFT(A1,4)&"*")=COUNTIF($A$1:A1,LEFT(A1,4)&"*"),A1,""))

**Rickage** · 07-15-2010, 08:54 PM

I only needed to display the last true field in each of the fields that were false until anther field became true again

I just made it if it was false use the column above as that answer is true which means it displayed the correct client.

Then when it is true again it would display a different value
so if the value afte that was false is would display the value above which was true and so on if that makes sense.

Thanks