Identify presence of difference texts across DIFFERENT cells, per group

[Corp_exceller]

I have the below data and want to see how I can achieve the last column.

Basically wanting to check for text down a column and per code so for example, I'm not looking for cells that contain "A" and "B" in the one cell, I'm looking for "A" and "B" across cells i.e. groups of codes, so that I can identify whether e.g. Code 1 has gone from A to B (presence of A and B in levels), or whether it's just stayed at A (presence of just A in levels), or whether the code has skipped level A and gone to B (presence of only Bs in level).

Apologies if I've been unclear.

Code	Level	Progression
1	A	A TO B
1	A	A TO B
1	A	A TO B
1	A	A TO B
1	B	A TO B
2	B	B ONLY
2	B	B ONLY
3	A	A ONLY
4	A	A TO B
4	B	A TO B
4	B	A TO B
5	A	A ONLY
5	A	A ONLY

 

[mumps]

Try:

Sub IdentifyDiff()
    Application.ScreenUpdating = False
    Dim i As Long, v As Variant, val As String, x As Long, cnt As Long
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            If Not .Exists(v(i, 1)) Then
                val = v(i, 1) & "|" & v(i, 2)
                .Add v(i, 1), Nothing
                x = i + 1
                cnt = 1
            Else
                If v(i, 1) & "|" & v(i, 2) <> val Then
                    Range("C" & x & ":C" & i + 1) = Split(val, "|")(1) & " to " & v(i, 2)
                Else
                    Range("C" & x & ":C" & i + 1) = Split(val, "|")(1) & " ONLY"
                End If
            End If
            If cnt = 1 Then Range("C" & x & ":C" & i + 1) = Split(val, "|")(1) & " ONLY"
            cnt = 0
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[Corp_exceller]

Thanks for the response. I’m back to work tomorrow so will check this VBA out and let you know how I get on!

 

[Peter_SSs]

Is this what you want?

22 09 01.xlsm
	A	B	C
1	Code	Level	Progression
2	1	A	A TO B
3	1	A	A TO B
4	1	A	A TO B
5	1	A	A TO B
6	1	B	A TO B
7	2	B	B ONLY
8	2	B	B ONLY
9	3	A	A ONLY
10	4	A	A TO B
11	4	B	A TO B
12	4	B	A TO B
13	5	A	A ONLY
14	5	A	A ONLY
Progression

Cell Formulas
Range	Formula
C2:C14	C2	=IF(A2=A1,C1,B2&IF(INDEX(B:B,AGGREGATE(14,6,ROW(B2:B$100)/(A2:A$100=A2),1))=B2," ONLY"," TO "&INDEX(B:B,AGGREGATE(14,6,ROW(B2:B$100)/(A2:A$100=A2),1))))

 

[Peter_SSs]

.. or perhaps this shorter one may also do what you want.

22 09 01.xlsm
	A	B	C
1	Code	Level	Progression
2	1	A	A TO B
3	1	A	A TO B
4	1	A	A TO B
5	1	A	A TO B
6	1	B	A TO B
7	2	B	B ONLY
8	2	B	B ONLY
9	3	A	A ONLY
10	4	A	A TO B
11	4	B	A TO B
12	4	B	A TO B
13	5	A	A ONLY
14	5	A	A ONLY
Progression (2)

Cell Formulas
Range	Formula
C2:C14	C2	=IF(A2=A1,C1,SUBSTITUTE(B2&" TO "&INDEX(B:B,AGGREGATE(14,6,ROW(B2:B$100)/(A2:A$100=A2),1)),"TO "&B2,"ONLY"))

 

[Corp_exceller]

Thanks Peter, that seems to have cracked it. I only have one other question which could change things. I'm now wanting this on google sheets which does not allow the aggregate function. Do you know of an alternative? I've tried filter but seems to have issues with the number of columns v rows.

@mumps Thanks very much for your response as it does work for me. A formula is more what I'm after so have gone with the formula response as being solution. Nevertheless, thank you for your correct answer!

 

[Peter_SSs]

I'm now wanting this on google sheets which does not allow the aggregate function. Do you know of an alternative?

I don't answer Google Sheets questions. I suggest that you start a new thread in the General Discussion & Other Applications forum which is the correct forum for Google Sheets questions. In that thread you could put a link back to this one for reference.

 

[Corp_exceller]

Anyone have a Google sheets solution for this? AGGREGATE function doesn’t exist on Google sheets. See new thread below.

Google sheets: Identify presence of difference texts across DIFFERENT cells, per group

I have the below data and want to see how I can achieve the last column, in google sheets. I was given an excel solution in a different thread but unfortuunatelt the AGGREGATE function is non existent in google sheets. The excel solution given is: =IF(A2=A1,C1,SUBSTITUTE(B2&" TO...

www.mrexcel.com

 

[Corp_exceller]

@Peter_SSs would it be possible to explain how that formula works? If I can understand it, I can maybe replicate win Google sheets. No probs if it’s overly complicated though!

 

[Peter_SSs]

Here is a version that does not use AGGREGATE. Perhaps you can adapt that? This is an array formula so you need to confirm it with Ctrl+Shift+Enter, not just Enter.

22 09 01.xlsm
	A	B	C
1	Code	Level	Progression
2	1	A	A TO B
3	1	A	A TO B
4	1	A	A TO B
5	1	A	A TO B
6	1	B	A TO B
7	2	B	B ONLY
8	2	B	B ONLY
9	3	A	A ONLY
10	4	A	A TO B
11	4	B	A TO B
12	4	B	A TO B
13	5	A	A ONLY
14	5	A	A ONLY
Progression (3)

Cell Formulas
Range	Formula
C2:C14	C2	=IF(A2=A1,C1,SUBSTITUTE(B2&" TO "&INDEX(B:B,LARGE(IF(A2:A$100=A2,ROW(B2:B$100)),1)),"TO "&B2,"ONLY"))
Press CTRL+SHIFT+ENTER to enter array formulas.