how to copy every nth column of a row to the first cell of the row?

G

garyusenet

How do you copy every nth column of a row to the first cell of the row?


The values in the columns are string values and I'd like to place a
space between every value as it's inserted into the cell.

There are thousands of columns (using excel 2007) and each row is using
a different number of columns, so the macro will need to figure out
when the last column of data is in the row and stop at that point.
 
R

Ryan.Chowdhury

Interesting problem. This is as far as I could get:

=(IF(MOD((COLUMN(C2:N2)-2),$A2)=0,IF($C2:$N2="","",$C2:$N2&" "),""))

assumptions:
1. data columns start in cell C, hence, the column count is subtracted
by 2, "...-2),$A..."
2. you have a column, in this case "A", that specifies the "n" in your
problem - the frequency of the columns
3. the data universally ends at column "N". You may have to extend for
you example.

What this returns is an array of strings, blank when the column is
blank or if the column number does not match your "n" or the string
plus a space represented by the value in every nth column.

Now, I tried to concatenate that as an array formula, but it only
returned the first value. The above formula might be the input for a
custom function, but that's as far as I could get.

Sorry.
 
H

HS Hartkamp

Gary, Ryan,

As I see it, there are two ways to take this a step further.

- A custom concatenate function that takes a matrix (variant) as input, or
- using two extra rows of cells concatenating the values manually.

Both have advantages and disadvantages.


Option 1: the user defined function:

Function concat(r As Variant) As Variant
Dim out As String
Dim i As Integer
For i = LBound(r) To UBound(r)
If r(i) <> "" Then
out = out & r(i) & " "
End If
Next
concat = Trim(out)
End Function

This would change Ryan's (previous post) function to
= Map1!Concat.concat(IF(MOD(COLUMN(C3:IV3);A3)=0;C3:IV3;""))


Option 2: manually in two steps (without using VBA)
- create a matrix in C5:IV5 over cells C3:IV3 that contains the formula
=IF(MOD(COLUMN(C3:IV3);A3)=0;IF(C3:IV3<>"";C3:IV3&" ";"");"")
This matrix (use ctrl-alt-enter to input it) could be a row below (as in
this example), but could also be on another sheet. In this matrix, every nth
cell is selected.

- Concatenate the resulting cells one by one, for example by using (in cell
C6, assuming the matrix is in C5:IV5)
= B6&C5 (and copy that formula to the right, all the way up to column IV)
In this second step, with each cell to the right, the concatenation string
grows by adding one cell.

The final result would be in cell IV6

Not elegant, but it works.

Personally I prefer the first option, and I see it as a shortcoming of excel
that the CONCATENATE-function will not take a ranges or matrices as input.


Bas Hartkamp.
 
Top