Transpone values from column into row like (A, B, C)

**IndigoSK** · 08-18-2015, 11:09 AM

Hi guys,

I need your help.

I have different values in a column. For example

A
B
C

Now I want them in a row and with brackets and commas, so it can be used in a SQL-Statement like

(A, B, C)

Thanks for your help!
Indigo

**gmr4evr1** · 08-18-2015, 11:16 AM

Try this.....

Please Login or Register  to view this content.

Press Ctrl + Shift + Enter as this is an array.

**JOHN H. DAVIS** · 08-18-2015, 11:31 AM

Maybe:

Please Login or Register  to view this content.

**IndigoSK** · 08-19-2015, 01:24 AM

Hi

@ Tom: this is an approach. But when I have a column with 500 numbers it is not possible.
@ John: That is almost the result i want to have. Perhaps it is possible that the whole result (A, B, C) is in one cell?

Regards
Indigo

**JOHN H. DAVIS** · 08-19-2015, 06:24 AM

Try:

Please Login or Register  to view this content.

**YasserKhalil** · 08-19-2015, 06:36 AM

Try this

Please Login or Register  to view this content.

**IndigoSK** · 08-19-2015, 07:58 AM

Hi

@ John. Nice. This works, But, as i see, i have to define all the cells which should be joined. This is again not possible for 500 cells.
@ Yasser. Really nice. This works really good. I only have to add the brackets manually, but that´s not the problem.

Thank you both!
Indigo

**Doc.AElstein** · 08-19-2015, 08:03 AM

Gruß Gott Indigo

Modified version of John’s,

Please Login or Register  to view this content.

And my “Array” version

Please Login or Register  to view this content.

..
Both take this

Using Excel 2007

_Row\^Col	A
1	A
2	B
3	C
4	D
5	E
6

ConcatenateRow

And return this

Using Excel 2007

_Row\^Col	A	B
1	A	(A, B, C, D, E)
2	B
3	C
4	D
5	E

ConcatenateRow

Alan
Hof,
Bayern

**JOHN H. DAVIS** · 08-19-2015, 08:38 AM

Originally Posted by IndigoSK

Hi

@ John. Nice. This works, But, as i see, i have to define all the cells which should be joined. This is again not possible for 500 cells.
@ Yasser. Really nice. This works really good. I only have to add the brackets manually, but that´s not the problem.

Thank you both!
Indigo

A little confused by your title. This modification should address your concerns.

Please Login or Register  to view this content.

**Doc.AElstein** · 08-19-2015, 08:47 AM

Hi John,

Originally Posted by JOHN H. DAVIS

A little confused by your title. This modification should address your concerns.
.......

.. I ( think ) to get the OPs final wishes you need to also look at row 1,

Please Login or Register  to view this content.

( I expect he may not be using a header, as you may have (sensibly) assumed

Alan

**JOHN H. DAVIS** · 08-19-2015, 08:56 AM

@ Alan - You're probably right! I always assume header. My default thinking.

**IndigoSK** · 08-20-2015, 02:10 AM

Really really nice! It is quite impressive how different the approach can be from both of you.
And I finally have to say that the bavarian guys, beside soccer, can also write good vba codes

Indigo
Gruß aus Baden

**jindon** · 08-20-2015, 02:40 AM

Do you mean like this?

Please Login or Register  to view this content.

**Doc.AElstein** · 08-20-2015, 05:34 AM

. Hi,

Originally Posted by IndigoSK

...... It is quite impressive how different the approach can be from both of you. .... have to say that the bavarian guys, beside soccer, can also write good vba codes

..
Gruß aus Baden

. Thanks for that, I was not notified of your Post... just caught it coincidentally...
.....

Originally Posted by IndigoSK

...... It is quite impressive how different the approach can be from both of you. .........

.. And did you catch the response from jindon ? !! - Wow what a great “One Liner..” The sort that i wrack my brain to pull apart to see how it works, and learn from..
. Multiple response Threads are the best to learn from. ( Shame there seems to be a problem just now I think of notification of Replies not always working??)
Alan

**IndigoSK** · 08-20-2015, 09:03 AM

@ jindon:

Transpone values from column into row like (A, B, C)

LinkBack

Thread Tools

Rate This Thread

Display

Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Re: Transpone values from column into row like (A, B, C)

Thread Information

Users Browsing this Thread

Similar Threads

Transpose values in column to rows, and split cell values to extend column

Combining Column Values if Seperate Column Values are Equal

[SOLVED] Find Values in Column A and fill identical values in Column B

Add Values in column And change Values in another column vs .find match function? 2007

Macro that will copy values in Column J and paste values to Column B in new sheet

assigning date entries to week values and month values to sum column C-N values C-

Transpone Excel tables with macro/VBA

Bookmarks

Bookmarks

Posting Permissions