transpose several rows to one cell

[PT]

Hi to all in the forum,

I have made same goggling but didn’t find any answer to this, so I hope you guys can help me.

My doubt is:

How can I transpose several rows to one specific cell? Something likes this:

H1 - 20
H2 - 17
H3 - 45

Put all in k1 on this format: 20,17,45

Is this possible?

Thanks in advance.
Ismael

 

[Arcticwarrio]

put in K1

=H1&","&H2&","&H3

 

[Sektor]

Sub Transfer()

    Dim i As Long, s As String
    
    For i = 1 To Range("H1").End(xlDown).Row
        s = s & Cells(i, "H") & ","
    Next
    
    Range("K1") = Left(s, Len(s) - 1)

End Sub

 

[PT]

Hi Sektor,

Your macro work ok. Never the less, if I want to set a range, something like H1 to H20, how can I do this?

Thanks.
Ismael

 

[Weaver]

Here's a user defined function you could use

Function konkat(ByVal r As Range, Optional s As String = ",")
    For Each c In r
        konkat = konkat & c.Value & s
    Next
    konkat = Left(konkat, Len(konkat) - Len(s))
End Function

A1:A8 = the digits 1 to 8

=konkat(A1:A8) returns <TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=56 x:str><COLGROUP><COL style="WIDTH: 42pt" width=56><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=15 width=56>1,2,3,4,5,6,7,8</TD></TR></TBODY></TABLE>

=konkat(A1:A8,"/") returns
<TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=56 x:str><COLGROUP><COL style="WIDTH: 42pt" width=56><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=15 width=56>1/2/3/4/5/6/7/8</TD></TR></TBODY></TABLE>

The separator (second argument) can be omitted and a comma is used, or you can go from "" to any number of characters.

=konkat(A1:A8,"") returns
<TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=56 x:str><COLGROUP><COL style="WIDTH: 42pt" width=56><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=15 width=56>12345678</TD></TR></TBODY></TABLE>

 

[PT]

Dear all,

Problem solve.

Thanks.
Ismael

 

[DanBanez]

Hi Guys,

I am new to this and I am trying to learn.

I entered digits 1 - 8 into A1 - A8

On A9 I entered the following:

=konkat(A1:A8)

But, it gave me #NAME?

What did I do wrong?

Thanks,


DanBanez

 

[Arcticwarrio]

Did you add weavers code to a module?

 

[Weaver]

Hi Guys,

I am new to this and I am trying to learn.

I entered digits 1 - 8 into A1 - A8

On A9 I entered the following:

=konkat(A1:A8)

But, it gave me #NAME?

What did I do wrong?

Thanks,


DanBanez

Sorry, I probably should've been more explicit, but I thought you were okay with code as you'd successfully used a previous poster's code.

Arcticwarrio is correct, you need to post the code I supplied into a separate code module for it to work. Note this is different from a worksheet module (attached to a worksheet)