I have a code that I don't know how to use

[Tayfun1992]

Hello friends. I needed a code for Excel and a friend helped me with it. But I don't know how to implement the code. I didn't even ask him. Here's what I'm going to use: I have a list of numbers like "1,2,3,4" in columns A and B. There is also a "common values" (written in code: "ortakDegerler" and "ortakdeger") in a single cell, such as "1,2,3,4". The code had to combine the appropriate cells in A and B into a single cell, based on common values. But I don't know how to run the code. So it is unclear where I should write the lists in A and B, in which cell I should write the "common values". But there are these in the content of the code and I can't figure it out. I am sharing the code here. I would appreciate it if you could take a look and tell me what to do. Happy days everyone..

Sub ortakListeKarsilastir()
'31032023 veyselemre
Application.ScreenUpdating = False
Dim ortakDegerler, elem, listeDic As Object, aList, bList, i, ii, al, ver, dic As Object, itms, kys
Set listeDic = CreateObject("Scripting.Dictionary")
Set dic = CreateObject("Scripting.Dictionary")

ortakDegerler = Split(Range("D2").Value, ",")
aList = Range("A2:B" & Cells(Rows.Count, 1).End(3).Row).Value
bList = Range("B2:C" & Cells(Rows.Count, 2).End(3).Row).Value

For i = 1 To UBound(aList)
al = ortakDegerAl(aList(i, 1), ortakDegerler, dic)
aList(i, 2) = ""
If al = "" Then
al = Trim(aList(i, 1))
ver = "D[" & i + 1 & " > " & aList(i, 1) & "]"
If Not listeDic.exists(al) Then
listeDic(al) = ver
Else
listeDic(al) = listeDic(al) & " ** " & ver
End If
Else
aList(i, 2) = al
End If
Next i

For i = 1 To UBound(bList)
bList(i, 2) = ""
al = ortakDegerAl(bList(i, 1), ortakDegerler, dic)
If al = "" Then
al = Trim(bList(i, 1))
ver = "C[" & i + 1 & " > " & bList(i, 1) & "]"
If Not listeDic.exists(al) Then
listeDic(al) = ver
Else
listeDic(al) = listeDic(al) & " ** " & ver
End If
Else
bList(i, 2) = al
End If
Next i

For i = 1 To UBound(aList)
For ii = 1 To UBound(bList)
If aList(i, 2) = bList(ii, 2) Then
al = birlestir(aList(i, 1) & "," & bList(ii, 1), dic)
ver = IIf(aList(i, 2) <> "", "A[", "B[") & i + 1 & " - " & ii + 1 & " > " & aList(i, 1) & " - " & bList(ii, 1) & "]"
If Not listeDic.exists(al) Then
listeDic(al) = ver
Else
listeDic(al) = listeDic(al) & " ** " & ver
End If
End If
Next ii
Next i
Range("E1:F" & Cells(Rows.Count, "E").End(3).Row).ClearContents
kys = listeDic.keys
itms = listeDic.items
For i = 0 To UBound(kys)
Cells(i + 1, "E").Value = kys(i)
Cells(i + 1, "F").Value = itms(i)
Next i
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("E1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("E1:F" & Cells(Rows.Count, "E").End(3).Row)
.Apply
End With
Application.ScreenUpdating = True

End Sub
Function birlestir(liste, dic)
Dim elem, al, i, ii
With dic
.RemoveAll
For Each elem In Split(liste, ",")
.Item(Val(elem)) = Null
Next elem
al = .keys
If UBound(al) > 0 Then
For i = 0 To UBound(al) - 1
For ii = i + 1 To UBound(al)
If al(i) > al(ii) Then
elem = al(ii)
al(ii) = al(i)
al(i) = elem
End If
Next ii
Next i
End If
End With
birlestir = Join(al, ",")
End Function
Function ortakDegerAl(liste, ortakDegerler, dic)
Dim elem, al
With dic
.RemoveAll
For Each elem In Split(liste, ",")
.Item(elem) = Null
Next elem
For Each elem In ortakDegerler
If .exists(elem) Then
al = al & " " & elem
End If
Next elem
End With
ortakDegerAl = Trim(al)
End Function

 

[Jimmypop]

Hi there...

Code not tested but willing to have a crack at it...

Firstly, let me explain what the code does. It compares the values in column A and B with a common value provided in cell D2 (as a comma-separated list of values). If a cell in either column A or B contains a value that matches a value in the common list, it will merge the corresponding cells in columns A and B into a single cell. The results are then displayed in columns E and F.

Now, to implement the code, you need to follow these steps:

1. Open your Excel sheet and press Alt+F11 to open the VBA editor.
2. Right-click on the sheet where you have the data, and select "View Code".
3. In the VBA editor, insert a new module by clicking on "Insert" -> "Module".
4. Copy the entire code that your friend gave you and paste it into the new module.
5. In the code, the common values are defined as the comma-separated list of values in cell D2. Therefore, you need to make sure that you have this cell in your sheet and that it contains the correct values.
6. Also, make sure that the values in columns A and B are in the correct format (i.e., comma-separated values).
7. Once you have made these changes, run the code by clicking on "Run" -> "Run Sub/Userform" or by pressing F5.

That's it! The code should now execute and display the results in columns E and F. If you encounter any errors or issues, feel free to ask for further assistance.

 

[Tayfun1992]

Hi there...

Code not tested but willing to have a crack at it...

Firstly, let me explain what the code does. It compares the values in column A and B with a common value provided in cell D2 (as a comma-separated list of values). If a cell in either column A or B contains a value that matches a value in the common list, it will merge the corresponding cells in columns A and B into a single cell. The results are then displayed in columns E and F.

Now, to implement the code, you need to follow these steps:

1. Open your Excel sheet and press Alt+F11 to open the VBA editor.
2. Right-click on the sheet where you have the data, and select "View Code".
3. In the VBA editor, insert a new module by clicking on "Insert" -> "Module".
4. Copy the entire code that your friend gave you and paste it into the new module.
5. In the code, the common values are defined as the comma-separated list of values in cell D2. Therefore, you need to make sure that you have this cell in your sheet and that it contains the correct values.
6. Also, make sure that the values in columns A and B are in the correct format (i.e., comma-separated values).
7. Once you have made these changes, run the code by clicking on "Run" -> "Run Sub/Userform" or by pressing F5.

That's it! The code should now execute and display the results in columns E and F. If you encounter any errors or issues, feel free to ask for further assistance.

Thank you very much for your help, it worked.

 

[Jimmypop]

Glad to hear that and glad we could assist

 

[MARK858]

Right-click on the sheet where you have the data, and select "View Code".

Why put the code in a sheet module when it isn't Worksheet event code? it can (should) reside in a regular module

Edit: in fact the above line is superceded by the following line

In the VBA editor, insert a new module by clicking on "Insert" -> "Module"

and so the sheet step can be omitted as it is being placed in a regular module on the following line

 

[Jimmypop]

Slight mistake from my side...was typing very furiouslywill double check given steps next time....

 

[MARK858]

Slight mistake from my side...was typing very furiouslywill double check given steps next time....

No problem, just had me puzzled