I want to be able to group a number of values in a column by matching there first n characters where n is the lengh-2
So for 123456 the length is 6 so trying to find a match for the first 4 characters in the cell above.
example below shows Column
A with the list of values and the column
B is my desired results.
Hopefully this makes sense..
RTL2051 | RTL2051
RTL2052 | RTL2051
RTL2050 | RTL2051
RTL9051 | RTL9051
RTL9052 | RTL9051
RTL9050 | RTL9051
I was trying to achieve this using OFFSET but the result is that its only looking at the one 1 row above and I want it to group them all until it doesn't match the first n characters.
=IF(LEFT(A1, LEN(A1)-2)= (LEFT(OFFSET(A1, -1,0,1,1), LEN(Sheet1!A1)-2)), A1, A2)
If your list of data is in colA (from A2 down), you can enter this in B2 and drag down to fill