Excel Help

[Adam]

Having a bit of a blonde day on Excel, I'm pretty sure I have done this before but I can't remember what I did!

Basically, if I have a list of numbers in 1 column (A) like below...

123456789
123456790
123456791
123456792
123456793
123456794
123456795
123456796
123456797
123456798
123456799
123456800
123456801
123456802
123456803
123456804
123456805
123456806
123456807
123456808
123456809

I need to copy that data and transpose into 1 cell but in a CSV format so it would look something like this (but with a space after the number,).....

123456789,

123456790,

123456791,

123456792,

123456793,

123456794,

123456795,

123456796,

123456797,

123456798,

123456799,

123456800,

123456801,

123456802,

123456803,

123456804,

123456805,

123456806,

123456807,

123456808,

123456809

Hope that makes sense!

 

[Rhino172]

Probably easier ways of doing it but I'd copy the list, paste into word (paste special, unformatted) then do a find (for paragraph marks, think it's ^p) and replace with , (comma space).

Then copy the lot and dump it back in the one cell.

 

[The Boosh!]

You can f**k about doing it manually, however I would just use visual basic.

Alt + F11

Insert > New module

Paste this:

Sub Macro()
Dim rngTemp As Range
Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("B1") = Join(WorksheetFunction.Transpose(rngTemp), ", ")
End Sub

Run it.

It will look at column A, then put all those values in cell B1.

 

[Tim.]

Highlight all cells
Copy
Paste Special (values)
Transpose
Save As csv

Will that do the job?

 

[Brand86]

Ooh Luke check you out!

That type of function will have you owning your own Twizy in no time...

 

[The Boosh!]

LOL. I can read VBA but not too good at writing it. I mainly use google and save the useful stuff. I would never buy a twizy!

 

[Brand86]

I thought you were loving it the other night!?? haha.

Anyway - you = geek!

 

[Adam]

You can f**k about doing it manually, however I would just use visual basic.

Alt + F11

Insert > New module

Paste this:

Sub Macro()
Dim rngTemp As Range
Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("B1") = Join(WorksheetFunction.Transpose(rngTemp), ", ")
End Sub

Run it.

It will look at column A, then put all those values in cell B1.

Boom... what a ledge

Cheers Luke

I need to learn this s**t!

 

[The Boosh!]

Glad it worked

Save it to your personal.xls if you have to do that regularly. You can add a button to excel tool bar and run it on demand then.

 

[Adam]

How do you add the button

 

[The Boosh!]

Right click at the top of excel in a blank area. you'll get a long list of s**t, but click customize at the bottom.

Click toolbars > New > Give it a name "Adams toolbar"

Drag it to the top like you would any other toolbar.

Then cilck on commands, scroll to macro's and drag "Custom button" to your blank toolbar.

Right click the command smiley face you've just dragged, and you can assign macro.

Yuo can also change the icon to whatever you want. Picture below to help you. I have some vba I wrote to import my banking exports into a spreadsheet i use to track my finances so I have a simular set up with my Halifax button.

 

[Adam]

Lovely stuff... button added

 

[The Boosh!]

If you want to learn some yourself you could add a couple of lines to that code which removes column A and saves the file as CSV to your desktop

 

[Adam]

Yeah thanks mate, will take a look

 

[JoedotwasT5phy]

shut up Loook

 

[Sym0n]

I'm so proud of little Lukey.