VBA Permutations of data string

Power sellers on Amazon and ebay will especially find this VBA solution useful to help create the multiple descriptions required to mix and jumble up their wordings and ultimately increase the SEO of their site and products. This particular code takes all the keywords in a matrix on the activesheet that are in cells A1:E6 and lists the different permutations from each. A good example here would be clothing where each column A:E represents a different ‘element’.

Column A could be the size – Large, Medium, Small
Column B might be the colour – Red, Green, Purple, Orange
Column C the type – Tshirt, Shirt, Shorts
etc…

The code will identify which cells are not used and create the permutations against each column. In this example the first few outputs would be:

Large Red Tshirt
Large Red Shirt
Large Red Shorts
Large Green Tshirt

…right through to the last…

Small Orange Short

You will need to copy this entire code into a module and assign it to a button or a macro name in the workbook. It can be adapted and expanded to allow for a huge amount of data and permutations, potentially saving hours of thinking and work and above all, absolute accuracy.

Feel free to leave us a message or contact us through the form below to help create a larger matrix or implement this in your project.



Sub Button1_Click()
Application.ScreenUpdating = False

For r = 1 To 6
For c = 1 To 5

If Left(ActiveSheet.Cells(r, c), 1) <> "¬" And ActiveSheet.Cells(r, c) <> "" Then
ActiveSheet.Cells(r, c) = "¬" & ActiveSheet.Cells(r, c)
End If

Next c

Next r

Dim arr As Variant, sSolution As String

arr = Range("A1:E6").Value
Arrangements arr, "", LBound(arr, 2), sSolution
Range("F1") = Mid(sSolution, 3)

Call Button2_Click

ActiveSheet.Range("F1").Select

Call Button3_Click

End Sub


Sub Button2_Click()
Dim arr As Variant, sSolution As String

arr = Range("A1:E6").Value
Arrangements arr, "", LBound(arr, 2), sSolution
Range("F1") = Mid(sSolution, 3)
End Sub

Sub Arrangements(ByRef arr, ByVal s As String, ByVal lInd As Long, ByRef sSolution As String)
Dim i As Long

For i = LBound(arr, 1) To UBound(arr, 1)
If arr(i, lInd) <> "" Then
If lInd = UBound(arr, 2) Then
sSolution = sSolution & ", " & s & arr(i, lInd)
Else
Arrangements arr, s & arr(i, lInd), lInd + 1, sSolution
End If
End If
Next i
End Sub


Sub Button3_Click()
Dim text As String
Dim a As Integer
Dim name As Variant
text = ActiveCell.Value
name = Split(text, ", ")
For a = 0 To UBound(name)
Cells(a + 8, 1).Value = name(a)
Next a

End Sub






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

2 thoughts on “VBA Permutations of data string

  1. Datanology says:

    Yes, the data would be output into a .txt file and would be easy for you to manage in a text editor or importing into your website if applicable. We would just need the data, and the process would be run, giving you your output almost immediately,

  2. Mark says:

    Hi, I have over 10,000 items in 4 columns that I need to be output in each separate permutation with a prefix and suffix of a pipe delimiter |. This exceeds Excels 1.04million rows. Do you have a solution for this ?

    Thanks

    Mark

Leave a Reply

Your email address will not be published. Required fields are marked *