I am working a macro that will combine columns. Here's how it needs
to work.

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3

I need to concatenate A1 B1 C1 into D1, then A1 B1 C2 into D2, then A1
B1 C3 into D3, then A1 B2 C1 into D4, then A1 B2 C2 into D5 and so
on. Essentially, I need every combination of columns A through C
concatenated into column D.

Thanks for your help!

Sub CatData()

NewRow = 1
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow
For j = 1 To LastRow
For k = 1 To LastRow
Range("D" & NewRow) = Range("A" & i) & _
Range("B" & j) & Range("C" & k)

NewRow = NewRow + 1
Next k
Next j
Next i



End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161009

Microsoft Office Help

No macro needed: The safest way to do it is as text. Formula in D1 is =A1 &
B1 & C1. You can also add spaces (& " " &) or other characters if necessary
between the fields. It is also possible to use the + operator but it only
has the desired result with string values...it will do mathematical addition
on numerical values.

"stallone" wrote:

I am working a macro that will combine columns. Here's how it needs
to work.

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3

I need to concatenate A1 B1 C1 into D1, then A1 B1 C2 into D2, then A1
B1 C3 into D3, then A1 B2 C1 into D4, then A1 B2 C2 into D5 and so
on. Essentially, I need every combination of columns A through C
concatenated into column D.

Thanks for your help!
.

Re-Read the request. the results isn't one formula per row, the user
wants every combination.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161009

Microsoft Office Help

Oops sorry. How are you deciding which combinations go in which cells? Is
there a method? It appears you are cycling through C, then B, then A. In
which case, you would be using a nested For loop.

Sub Loops
OutputCell=1

For A=1 to 3
OutPutA=cells(A,1).value & ""
For B=1 to 3
OutPutB=cells(B,2) .value & ""
For C=1 to 3
OutPutC=cells(C,3).value & ""
FinalOutPut=OutPutA & OutPutB & OutPutC
cells(OutputCell,4).value=FinalOutPut
OutputCell=OutputCell+1
Next C
Next B
Next A

End Sub

That should do the trick based on the initial posting.


"joel" wrote:


Re-Read the request. the results isn't one formula per row, the user
wants every combination.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161009

Microsoft Office Help

.