Looping through columns

[HXIO]

Hi,

I kinda stuck on how to get this to work. I want to loop through column C to check for matches, but if it appears more then 2 times ignore it.

If it matches within the column C then check if column D also matches if it does concat column B and "1C" at the end. (1C = 1 colour)

If it doesn't match then concat column B and "2C" at the end. (2C = 2 colours)

If it doesn't match in column C then concat column B with column D but only the first 2 letters of column D.

I'd attached an example workbook of before.

Thanks

[Pepe Le Mokko]

What does "col" mean in your thread title?

[HXIO]

@ Pepe Le Mokko

It’s means column I forgot to reword it back to column.

[Pepe Le Mokko]

Perhaps adapt your thread title then?

[Marc L]

(removed 'cause of post #10 as the initial attachment not matches the initial explanation ‼ As usually in that case I do … nothing !)

[HXIO]

@ Marc L

Thanks for the help if possible can you go through the code with me. There's quite a few things I don't quite understand in the code.

Thanks

[Marc L]

Thanks for the rep' !

If you don't find enough information in VBA inner help (as almost all is there) so ask for any specific point …

[sintek]

Hi HXIO

but if it appears more then 2 times ignore it.

If this is the case then why is your output as such...This number appears 4 times in Col C

Untitled.png

Surely they must be blank...

Please Login or Register  to view this content.

[Marc L]

This number appears 4 times in Col C

Yes I saw that too & had the same thought as yours but with the attachment I did not keep that rule …

[HXIO]

@ sintek

Thanks for pointing this out, and yes you are right it should be blank.

Because I want any values in Column C appear more than 2 times on another columns with different rules.

I will upload a workbook later with other rules.

[HXIO]

@Marc L and sintek

Both codes does the job and works great is there any chance to incorporate this into the exiting codes?

If I want to include column C values appearing more than 2 times as well in a different column with these rules below.

If values appear 3 or more with same colour concat column B and add “1C” at the end same as the rules with values appearing 2 times. However, if the first 4-5 string before the first “-“ is different then that will be concat as well.

If values appear 3 or more with different colours for example if there’s 2 colours then add “2C” or "3C" and "4C" at the end and if it’s 3 or 4 it will be the same rules but there will be more combinations. If values appear more than 4 times it will be ignore.

Pretty much whenever the values increase in column C, then there will be more combinations added on.

I’d include another workbook with different combinations.

[sintek]

Your outputs in After sheet are incorrect as per original requirement...Please correct all outputs as to requirement and upload correct resultant file...
P919-76-15 0020344849 RED P919-76-15+P919C-56-02-1C

[HXIO]

@sintek

Apologised for the late reply, please see the amended example.

[Marc L]

Still errors in last attachment (rows #13 & 26) so check & correct !

[HXIO]

@Marc L

Thanks for pointing it out I'd correct line 13 as for line 26 it should be correct.

[Marc L]

Have you this time well think about your need like you must before creating any thread ?
'Cause it will be my last code …

[HXIO]

@Marc L

Yes this is what I needed there won't be anymore changes. I will make sure to think well next time before I starting any new thread nest time. Thanks for being patient with me.

[Marc L]

So according to your post #15 attachment, the best is to paste this code to the worksheet module :

Sub Demo2() 
   Const D = "SUMPRODUCT(1/COUNTIF(D#:D¤,D#:D¤))", M = "-", P = "+" 
     Dim R&, T$, B$(), C(), F&, L&, S$, V, W() 
         R = 1 
         Application.ScreenUpdating = False 
    With [A1].CurrentRegion 
        .Columns("E:F").Clear 
        .Sort [C1], xlAscending, Header:=xlYes 
    End With 
         T = [B2].Value2 
    Do 
            R = R + 1 
        If Cells(R + 1, 3).Value2 = Cells(R, 3).Value2 Then 
                ReDim B(1 To 1), C(1 To 1) 
                F = R 
                L = InStrRev(T, M) 
                B(1) = Left$(T, L) 
                C(1) = Array(Mid$(T, L + 1)) 
            Do 
                    R = R + 1 
                    T = Cells(R, 2).Value2 
                    L = InStrRev(T, M) 
                    S = Left$(T, L) 
                    T = Mid$(T, L + 1) 
                    V = Application.Match(S, B, 0) 
                If IsError(V) Then 
                    L = UBound(B) + 1 
                    ReDim Preserve B(1 To L), C(1 To L) 
                    B(L) = S 
                    C(L) = Array(T) 
                ElseIf IsError(Application.Match(T, C(V), 0)) Then 
                    W = C(V) 
                    ReDim Preserve W(UBound(W) + 1) 
                    W(UBound(W)) = T 
                    C(V) = W 
                End If 
            Loop While Cells(R + 1, 3).Value2 = Cells(R, 3).Value2 
                For L = 1 To UBound(B):  B(L) = B(L) & Join(C(L), P):  Next 
                Cells(F, 5 - (R - F > 1)).Value2 = Join(B, P) & M & Evaluate(Replace$(Replace$(D, "#", F), "¤", R)) & "C" 
        Else 
            Cells(R, 5).Value2 = T & M & Left$(Cells(R, 4).Value2, 2) 
        End If 
               T = Cells(R + 1, 2).Value2 
    Loop Until T = "" 
         Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !