ExcelBanter - How do I transpose a large amount of data from rows to columns?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - How do I transpose a large amount of data from rows to columns? (https://www.excelbanter.com/excel-discussion-misc-queries/257950-how-do-i-transpose-large-amount-data-rows-columns.html)

How do I transpose a large amount of data from rows to columns?

Hi,

I have a large amount of data downloaded from a banking system which I would
like to transpose, however as there are hundreds suppliers I don't want to
repeat the transpose action for every single one. Can someone help me with
this?

Below is how it is:

[RECIPIENT AMTSGERICHT LUNEBURG]
ACCOUNTNO 106024003
BANKCODE 25050000
NAME AMTSGERICHT LUNEBURG
PURPOSE NZS LG - 15553

[RECIPIENT E.ON BAYERN AG]
ACCOUNTNO 62004312
BANKCODE 70020270
NAME E.ON BAYERN AG
PURPOSE INVOICES 690011186888-07060#9 DEC, 690011026217-070426 #DEC

[RECIPIENT DETEKTEI EISEN]
ACCOUNTNO 311005448
BANKCODE 44050199
NAME DETEKTEI EISEN
PURPOSE INVOICE 1206

[RECIPIENT ASTERIA GV GMBH]
ACCOUNTNO 377978000
BANKCODE 21050000
NAME ASTERIA GV GMBH
PURPOSE NEBENKOSTEN 2007/2008#CGV KUENZELSAU

[RECIPIENT ENSEL-NT GMBH]
ACCOUNTNO 6611750
BANKCODE 56051790
NAME ENSEL-NT GMBH
PURPOSE 6825936

This is how I would like it:

ACCOUNTNO BANKCODE NAME
106024003 25050000 AMTSGERICHT LUNEBURG
62004312 70020270 E.ON BAYERN AG

Many thanks!

How do I transpose a large amount of data from rows to columns?

Is this data all in one column (A), or is the Account Number, Bank
Code and Name in column B? Will you always have 5 rows per client with
a single blank row between them?

Pete

On Mar 4, 11:17*am, Martijn wrote:
Hi,

I have a large amount of data downloaded from a banking system which I would
like to transpose, however as there are hundreds suppliers I don't want to
repeat the transpose action for every single one. Can someone help me with
this?

Below is how it is:

[RECIPIENT AMTSGERICHT LUNEBURG] * * * *
ACCOUNTNO * * * 106024003
BANKCODE * * * *25050000
NAME * *AMTSGERICHT LUNEBURG
PURPOSE NZS LG - 15553

[RECIPIENT E.ON BAYERN AG] * * *
ACCOUNTNO * * * 62004312
BANKCODE * * * *70020270
NAME * *E.ON BAYERN AG
PURPOSE INVOICES 690011186888-07060#9 DEC, 690011026217-070426 #DEC

[RECIPIENT DETEKTEI EISEN] * * *
ACCOUNTNO * * * 311005448
BANKCODE * * * *44050199
NAME * *DETEKTEI EISEN
PURPOSE INVOICE 1206

[RECIPIENT ASTERIA GV GMBH] * *
ACCOUNTNO * * * 377978000
BANKCODE * * * *21050000
NAME * *ASTERIA GV GMBH
PURPOSE NEBENKOSTEN 2007/2008#CGV KUENZELSAU

[RECIPIENT ENSEL-NT GMBH] * * *
ACCOUNTNO * * * 6611750
BANKCODE * * * *56051790
NAME * *ENSEL-NT GMBH
PURPOSE 6825936

This is how I would like it:

ACCOUNTNO * * *BANKCODE NAME
106024003 * * * * * 25050000 * * * *AMTSGERICHT LUNEBURG
62004312 * * * * * *70020270 * * * * E.ON BAYERN AG

Many thanks!

How do I transpose a large amount of data from rows to columns?

How do I transpose a large amount of data from rows to columns

Hi Herbert,

Many thanks, this looks like exactly what I need!

I suppose I can copy my date into your sheet and then use it.

There is only one thing as I get an error message:

Run-time error "9" :

Subscript out of range

"Herbert Seidenberg" wrote:

Thanks again

Martijn

Excel 2007
Transpose with macro
http://c0718892.cdn.cloudfiles.racks.../03_04_10.xlsm
.