Mark cells based on a requirement

[KorvusK]

Hello everybody

Im having some work with excel that is very time consuming, and I was wonder if there is possible to do it faster with macros. I have try with my little knoledge but I cant find the solution. So, lets explain.

I have 2 columns that represent every posible case of the combinatios from 1 to 5, that is:

1 2
1 3
1 4
1 5
2 3
2 4
2 5
3 4
3 5
4 5

On other sheet I have the cases a participant has made, in 2 columns again, that mark the combination that appeared, for example:

1 2
1 3
3 4
4 5

What I am trying to do is, on the first sheet where I have all the posible combinations, it is possible to mark with 1 and 0 on the next column the cases that I have in the other sheet?

The final product would be something like this:

1 2 1
1 3 1
1 4 0
1 5 0
2 3 0
2 4 0
2 5 0
3 4 1
3 5 0
4 5 1


Maybe I am overcomplicating it trying to do macros but I need help on this if you know any easier way.

Thank you!

[carlmon]

1 to 5, 1 to 5

has 25 permutations, 5x5

Do you want to represent what you're showing instead?

[KorvusK]

Yes, because I dont need the repeated ones, thank you!

[carlmon]

Can you post an example workbook (see yellow ribbon at the top)... I want to write you a code that works for what you already have. I can write you a code in 10 minutes

[KorvusK]

Sure thing, I think I have done it right!

What I have is the data on the 2 first sheets, and what I need is what I wrote on the final example.

[carlmon]

So what you have going on is totally possible to do in VBA and I will write you a code... However, I'd like to know if we can combine the two numbers into one cell.... so instead of Cell:1 Cell:2, it would be Cell:12, Cell:13, 14, 15

[Marc L]

According to the attachment an Excel basics VBA demonstration for starters :

Sub Demo1() 
      Const D = "¤", S = "&""" & D & """&" 
        Dim W, C%, R&, V 
    With [Sheet1!A1].CurrentRegion.Columns 
      ReDim W(.Count / 2) 
            C = 1 
        For R = 1 To UBound(W) 
            W(R) = .Parent.Evaluate(.Item(C).Address & S & .Item(C + 1).Address) 
            C = C + 2 
        Next 
    End With 
    With [Sheet2!A1].CurrentRegion.Columns 
        If .Count > 2 Then .Item(3).Resize(, .Count - 2).ClearContents 
        ReDim V(1 To .Rows.Count, 1 To UBound(W)) 
        W(0) = .Parent.Evaluate(.Item(1).Address & S & .Item(2).Address) 
    For C = 1 To UBound(W) 
        V(1, C) = Split(W(C)(1, 1), D)(0) 
        W(C) = Application.Match(W(0), W(C), 0) 
    For R = 2 To .Rows.Count 
        V(R, C) = -IsNumeric(W(C)(R, 1)) 
    Next R, C 
       .Item(3).Resize(, UBound(W)).Value = V 
    End With 
End Sub  

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

[KorvusK]

So what you have going on is totally possible to do in VBA and I will write you a code... However, I'd like to know if we can combine the two numbers into one cell.... so instead of Cell:1 Cell:2, it would be Cell:12, Cell:13, 14, 15

If it would be easier, I can decombine it later, but I preffer the 2 columns system because Ill later use SPSS and that program need the 2 columns notation

According to the attachment an Excel basics VBA demonstration for starters :

Sub Demo1() 
      Const D = "¤", S = "&""" & D & """&" 
        Dim W, C%, R&, V 
    With [Sheet1!A1].CurrentRegion.Columns 
      ReDim W(.Count / 2) 
            C = 1 
        For R = 1 To UBound(W) 
            W(R) = .Parent.Evaluate(.Item(C).Address & S & .Item(C + 1).Address) 
            C = C + 2 
        Next 
    End With 
    With [Sheet2!A1].CurrentRegion.Columns 
        If .Count > 2 Then .Item(3).Resize(, .Count - 2).ClearContents 
        ReDim V(1 To .Rows.Count, 1 To UBound(W)) 
        W(0) = .Parent.Evaluate(.Item(1).Address & S & .Item(2).Address) 
    For C = 1 To UBound(W) 
        V(1, C) = Split(W(C)(1, 1), D)(0) 
        W(C) = Application.Match(W(0), W(C), 0) 
    For R = 2 To .Rows.Count 
        V(R, C) = -IsNumeric(W(C)(R, 1)) 
    Next R, C 
       .Item(3).Resize(, UBound(W)).Value = V 
    End With 
End Sub  

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

Oh! That solves it! Very thankful!!