Concat big data

[Gulya]

Hi Experts,

I was wondering if there is an easier way to concat multiple cells based on the below mentioned criteria;

1. To concat set of two cells from 29 columns, for example DT2=concat(O2,P2), DU2=concat(O2,Q2) and so on..
2. To concat set of three cells from 29 columns, for example EEET2=concat(O2,P2,Q2), EEEF2=concat(O2,P2,R2) and so on..
3. To concat set of four cells from 29 columns, for example FFFT2=concat(O2,P2,Q2,R2), FFFU2=concat(O2,P2,Q2,T2) and so on..
4. To concat set of fife cells from 29 columns, for example SSST2=concat(O2,P2,Q2,R2,S2), SSSU2=concat(O2,P2,Q2,R2,T2) and so on..
5. To concat set of six cells from 29 columns, for example TTTT2=concat(O2,P2,Q2,R2,S2,T2), TTTU2=concat(O2,P2,Q2,R2,S2,U2) and so on..
6. And so on until concat set of 29 cells

I have attaches the file for easy reference.

As per my calculation it will be 406 columns to concat just set of two cells and to concat all based on the above criteria, it will be more then 5000 columns and it takes a lot of time to do manually, so appreciate if somebody could provide me any solution like a macro or any formula to complete this task easier.;

[AliGW]

In DT2 copied across and down:

=TEXTJOIN(",",,$O2:P2)

[Pete_UK]

If the numbers in red across the top from DT1 onwards can be relied upon, then you can use this formula in DT2:

=INDEX($O2:$AQ2,LEFT(DT$1,FIND(",",DT$1)-1))&","&INDEX($O2:$AQ2,RIGHT(DT$1,LEN(DT$1)-FIND(",",DT$1)))

You can copy this across (and down) to generate the 2-character combinations. Beyond that, I would need to see examples of what you are trying to achieve.

It might be better doing this using VBA.

Hope this helps.

Pete

[AliGW]

I see you changed the information in the opening post AFTER I posted - please don't do this.

[AliGW]

Seen, but no reply ...

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Gulya]

Hi Pete_UK,

The formula is working perfectly if there is a data in the 1rst row, so can we use a formula even to the header/1st row?.

Regarding set of three cells, I have included examples in the column TJ2 onwards.

Appreciate if you could provide either VBA or formula for the remaining set of 3,4,5,6,7......29 cells as well please.

Thanks

[Pete_UK]

This formula, in cell TJ2, works for the next set of numbers that you have in TJ1:VJ1, and can be copied across to VJ2 :

=INDEX($DT2:$TI2,MATCH(LEFT(TJ$1,3),$DT$1:$TI$1,0))&","&INDEX($O2:$AQ2,SUBSTITUTE(TJ$1,LEFT(TJ$1,4),""))

although I think it will fail when you start to get 2-digit numbers in the first and second position, because I have hard-coded the 3 and 4 above (in red). It basically works by taking what you have already concatenated (in the range DT2:TI2) and joining on the final part from the range O2:AQ2. The same principle could be applied to all your other concatenations if they are built up systematically as 2-column, 3-column, 4-column etc. up to 29 columns.

However, I do feel that it is not a problem suitable for a formula solution, but I am not in a position to offer a VBA approach - perhaps someone else will chip in with that.

Hope this helps.

Pete

[Gulya]

Thanks Pete_UK for the effort

The formula seems okey, but still I need to concat DT1:XFD1 manually because the below provided formulas are working based on DT1:XFD1 data.

Is there a way to use the below formulas without referring to DT1:XFD1

=INDEX($O2:$AQ2,LEFT(DT$1,FIND(",",DT$1)-1))&","&INDEX($O2:$AQ2,RIGHT(DT$1,LEN(DT$1)-FIND(",",DT$1)))

=INDEX($DT2:$TI2,MATCH(LEFT(TJ$1,3),$DT$1:$TI$1,0))&","&INDEX($O2:$AQ2,SUBSTITUTE(TJ$1,LEFT(TJ$1,4),""))

thanks

[Pete_UK]

I'll have a go at generating the number combinations for you, so that they can just be pasted into row 1.

Pete

[Pete_UK]

Just to be clear, you don't want anything concatenated with itself ?

Pete

[Gulya]

There won't be any concatenation with itself like concat(A1,A1), everything will be concatenated with different cells.
thanks

[AliGW]

Have I gone invisible?

[Gulya]

Have I gone invisible?

Hi Ali,
I didn?t get you🤔

[Glenn Kennedy]

Ali, your solution concatenates everything. That's not what was wanted (I think).

Try this:

=IFERROR(TEXTJOIN(",",,INDEX($O2:$AQ2,,FILTERXML("<A><B>"&SUBSTITUTE(DT$1,",","</B><B>")&"</B></A>","//B"))),"")

copied across. Works for all cells where expected answers are shown.

[AliGW]

Hi Ali,
I didn?t get you

I offered a solution in post #2. You haven't acknowledged it.

Administrative Note

... please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

Ali, your solution concatenates everything. That's not what was wanted (I think).

It was what I understood was wanted at the time. However, as it was seen hours ago and simply ignored, I suppose I guessed incorrectly.

[Gulya]

well noted Ali.
As Glenn mentioned =TEXTJOIN(",",,$O2:P2) is concatenating everything. Thanks

[Gulya]

Hi Glenn,
Is there a way to use the formulas without referring to DT1:XFD1, otherwise I need to concat the header (DT1:XFD1) again manually in order to use the formula. thanks

[Glenn Kennedy]

I was thinking about that earlier... trying to be "smart". However I gave up. I'll take another look either now, or in the morning.

[Glenn Kennedy]

I guess it depends on how smart you want the formula to be...

See the formulae in purple & light blue. To fully automate this would require a bit more (a lot more??) effort. How does this look??

="1,"&1+COLUMNS($DT1:DT1)

and then a variant in the light blue cells.

[Gulya]

Thanks a lot Glenn, Pete and Ali

[Glenn Kennedy]

I assume you're OK with a semi-automatic workaround, then?

I hope so... as I'm not sure how to tackle full automation!

[Gulya]

Hi Glenn,
Running the below query to generate set of 2 & 3......29 combinations then updating them manually with the following formula =TEXTJOIN(",",,A2,B2)

Sub HeaderGenerator()

Const u& = 29
Const v& = 3
Const m& = 2 ^ 20
Dim x&(), a&, b&, c&, k&, r&, sp&
ReDim x(1 To m, 1 To v)
sp = v + 10
For a = 1 To u
For b = a + 1 To u
For c = b + 1 To u
' For d = c + 1 To u
k = k + 1
If k > m Then
Cells(sp * r + 1).Resize(m, v) = x
r = r + 1
k = 1
ReDim x(1 To m, 1 To v)
End If
x(k, 1) = a
x(k, 2) = b
' x(k, 3) = c
'Next c
Next b
Next a

Cells(sp * r + 1).Resize(k, v) = x

End Sub