Need UDF for executing a concatenate in a matrix of text

[dea006]

First, thank you for your time here!

I'm hoping to find a UDF or other macro that will allow me to perform a concatenate on a matrix of text built out in excel. The aim is to create an output column that contains every possible combination of the text that has been input into the various fields (In the screenshotted example there would be 96 outputs). No need to scrub the output of any formulas that didn't work due to "N/A"s, I'm really just looking to shave time here wherever possible.

Any help would be greatly appreciated. Thanks again for your time and assistance!

Best,
David

 

[diddi]

in your screen shot, there are 7 words which can form 7! combinations of arrangements of all 7 words. that is 5040 combinations. can you provide an example with say 3 words that shows the actual output you want to achieve. that might be easier to visualise than your written description

 

[dea006]

in your screen shot, there are 7 words which can form 7! combinations of arrangements of all 7 words. that is 5040 combinations. can you provide an example with say 3 words that shows the actual output you want to achieve. that might be easier to visualise than your written description

Ahh, apologies for the confusion. The output should only contain one input from each column. In the screenshot example, the output should never have multiple 'creative's or 'DSP's listed, etc. Similarly, the Advertiser and campaign would be the same for all outputs as there is only one row completed for that column.

Let me know if this doesn't clarify completely. Thanks so much for the time and help here!

Best,
David

 

[Peter_SSs]

Welcome to the MrExcel board!

Would something like be any use?

Sub Combos()
  Dim a As Variant, b As Variant, vRws As Variant
  Dim lr As Long, k As Long, t As Long, u As Long, v As Long, w As Long, x As Long, y As Long, z As Long
  
  Const fr As Long = 6
  
  lr = Columns("B:H").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  vRws = Evaluate(Replace(Replace("row(#:@)", "#", fr), "@", lr))
  a = Range("B1:H" & lr).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For t = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 1)), "N/A", False))
    For u = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 2)), "N/A", False))
      For v = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 3)), "N/A", False))
        For w = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 4)), "N/A", False))
          For x = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 5)), "N/A", False))
            For y = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 6)), "N/A", False))
              For z = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 7)), "N/A", False))
                k = k + 1
                b(k, 1) = a(t, 1) & a(u, 2) & a(v, 3) & a(w, 4) & a(x, 5) & a(y, 6) & a(z, 7)
              Next z
            Next y
          Next x
        Next w
      Next v
    Next u
  Next t
  Range("J" & fr).Resize(k).Value = b
End Sub

My sample data and (partial) results:

dea006.xlsm
	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6	a	b	c	f	h	j	n		abcfhjn
7	N/A	N/A	d	g	i	k	o		abcfhjo
8	N/A	N/A	e	N/A	N/A	l	N/A		abcfhkn
9	N/A	N/A	N/A	N/A	N/A	m	N/A		abcfhko
10									abcfhln
11									abcfhlo
12									abcfhmn
13									abcfhmo
14									abcfijn
15									abcfijo
16									abcfikn
17									abcfiko
18									abcfiln
19									abcfilo
20									abcfimn
21									abcfimo
22									abcghjn
23									abcghjo
24									abcghkn
25									abcghko
26									abcghln
27									abcghlo
28									abcghmn
Sheet1

 

[dea006]

Welcome to the MrExcel board!

Would something like be any use?

Sub Combos()
  Dim a As Variant, b As Variant, vRws As Variant
  Dim lr As Long, k As Long, t As Long, u As Long, v As Long, w As Long, x As Long, y As Long, z As Long
 
  Const fr As Long = 6
 
  lr = Columns("B:H").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  vRws = Evaluate(Replace(Replace("row(#:@)", "#", fr), "@", lr))
  a = Range("B1:H" & lr).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For t = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 1)), "N/A", False))
    For u = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 2)), "N/A", False))
      For v = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 3)), "N/A", False))
        For w = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 4)), "N/A", False))
          For x = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 5)), "N/A", False))
            For y = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 6)), "N/A", False))
              For z = fr To fr + UBound(Filter(Application.Transpose(Application.Index(a, vRws, 7)), "N/A", False))
                k = k + 1
                b(k, 1) = a(t, 1) & a(u, 2) & a(v, 3) & a(w, 4) & a(x, 5) & a(y, 6) & a(z, 7)
              Next z
            Next y
          Next x
        Next w
      Next v
    Next u
  Next t
  Range("J" & fr).Resize(k).Value = b
End Sub

My sample data and (partial) results:

dea006.xlsm
	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6	a	b	c	f	h	j	n		abcfhjn
7	N/A	N/A	d	g	i	k	o		abcfhjo
8	N/A	N/A	e	N/A	N/A	l	N/A		abcfhkn
9	N/A	N/A	N/A	N/A	N/A	m	N/A		abcfhko
10									abcfhln
11									abcfhlo
12									abcfhmn
13									abcfhmo
14									abcfijn
15									abcfijo
16									abcfikn
17									abcfiko
18									abcfiln
19									abcfilo
20									abcfimn
21									abcfimo
22									abcghjn
23									abcghjo
24									abcghkn
25									abcghko
26									abcghln
27									abcghlo
28									abcghmn
Sheet1

This is amazing, massive help! Thanks for the quick reply Peter!

Best,
David

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.