Concatenate 48 values at a time

[drmingle]

I have a data set that needs to be concatenated in groups of 48 (sequentially - ie. the first 48 columns in the first row , the second 48 columns in the first row, the third 48 columns in the first row, etc).

I want the code to repeat until I get to the end of the columns.

If the codes places a formula in the destination cell then I will autofill the other rows, if it doesn't then I will need for the code to perform the procedure until it reaches the bottom of the rows (about 28k).

I find a code snippet online that might be resourced somehow...

Any help would be appreciated.

<code>
Option Explicit  Sub Sample()     Dim LastRow As Long     Dim Ws As Worksheet      Set Ws = Sheets("Sheet1")      LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row      '~~> If your range doesn't have a header     Ws.Range("H1:H" & LastRow).Formula = "=A1&B1&C1&D1&E1&F1&G1"      '~~> If it does then     Ws.Range("H2:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2" End Sub</code></pre>

 

[hiker95]

drmingle,

I have a data set that needs to be concatenated in groups of 48 (sequentially - ie. the first 48 columns in the first row , the second 48 columns in the first row, the third 48 columns in the first row, etc).

So that we can get it right the first time:

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better:

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[drmingle]

I have a data set that needs to be concatenated in groups of 48 (sequentially - ie. the first 48 columns in the first row , the second 48 columns in the first row, the third 48 columns in the first row, etc).

I want the code to repeat until I get to the end of the columns.

If the codes places a formula in the destination cell then I will autofill the other rows, if it doesn't then I will need for the code to perform the procedure until it reaches the bottom of the rows (about 28k).

I find a code snippet online that might be resourced somehow...

Any help would be appreciated.

<code>
Option Explicit  Sub Sample()     Dim LastRow As Long     Dim Ws As Worksheet      Set Ws = Sheets("Sheet1")      LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row      '~~> If your range doesn't have a header     Ws.Range("H1:H" & LastRow).Formula = "=A1&B1&C1&D1&E1&F1&G1"      '~~> If it does then     Ws.Range("H2:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2" End Sub</code>

I will not be able to post the data. It is against our policy.


My general example would be the following:

I want in cell A1 to have my concatenated product from the adjacent 48 cells.

My specific situation is the following:

I have 2304 columns of data that need to be concatened in groups of 48.
The ouput of each should be in the sequence that it is encountered (i.e. A1 = 1st 48, B1= 2nd 48, C1 = 3rd 48).
If it helps the data dimensions are 48 x 48 (that is where the 2304 is coming from).

 

[hiker95]

drmingle,

You have in row 1 some raw data in range A1:CJP1 = 2,304 columns?

You want cell A1 to contain the concatenated range A1:AV1, = first 48 columns?

And, cell B1 to contain the concatenated range AW1:CR1, = second 48 columns?

And, so on, and, so on?

 

[drmingle]

drmingle,

You have in row 1 some raw data in range A1:CJP1 = 2,304 columns?

You want cell A1 to contain the concatenated range A1:AV1, = first 48 columns?

And, cell B1 to contain the concatenated range AW1:CR1, = second 48 columns?

And, so on, and, so on?

Yep - that's it.

The only additional thing is that I have 28k rows, so not only will I need to do what you stated up above...but I will also need to complete it for each row until I reach an empty cell (in column B - where (B1) all my row data starts).

 

[hiker95]

drmingle,

One last try:

You have 28,000+ rows of raw data?

The first row starts in cell B1, and occupies the range B1:CJQ1 = 2,304 columns?

In order to continue I would like to see what the raw data looks like in range B1:K5.

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better:

You can upload a sample workbook/worksheet with range B1:K5 to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you can not provide one of the two above options then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Armando Montes]

Maybe:
How to add a range of cells in excel – concat() | Chandoo.org - Learn Microsoft Excel Online