topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday March 29, 2024, 2:21 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Last post Author Topic: DONE: Utility: Convert Multiple Columns in Excel to a Single Column  (Read 23375 times)

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
A fellow I work with is not very techno-savvy, and needs to collapse multiple columns in Excel into a single value.

I wrote a little utility to let him copy columns from Excel, paste into the utility, then copy back into a single column in Excel.

UTILITY (Tab Cleaner):
Screenshot - 2011-01-10 , 4_41_40 PM.png

DOWNLOAD:
* TabToSpaceCleaner.exe (159.5 kB - downloaded 506 times.)

PURPOSE:
Combine multiple columns (and rows) into a single column.

USAGE:
1) Copy columns (and rows) from MS Excel
2) Click first "PASTE" text box in the program
3) In Excel, double click a cell to deselect
4) Select a cell to paste new value(s)
5) Click second "COPY" text box in the program
6) In Excel, paste the value(s)
7) Done.

To change the separator, change the value in the "Tab Cleaner Text" box, e.g. change " - " to " " or ", " or whatever suits your purpose.

Here's an example showing columns I, J, and combined into column L:

Screenshot - 2011-01-10 , 3_55_07 PM.pngDONE: Utility: Convert Multiple Columns in Excel to a Single Column


While I'm sure few people here would need it, I'm guessing that someone will find a use.


EDIT (forgot the requirements):

REQUIREMENTS:
Microsoft Windows 2000 or higher
.NET 2.0

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
« Last Edit: January 09, 2011, 11:57 PM by Renegade »

skwire

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,286
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #1 on: January 09, 2011, 11:55 PM »
Nicely done.   :Thmbsup:

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #2 on: January 10, 2011, 12:45 AM »
sorry, don't mean to critical, but couldn't you have just used a concatenation formula (=columnA & columnB & columnC)?

you could use a VBA macro to fill it in and paste the values (no dependencies, apart from Excel)

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #3 on: January 10, 2011, 01:28 AM »
sorry, don't mean to critical, but couldn't you have just used a concatenation formula (=columnA & columnB & columnC)?

you could use a VBA macro to fill it in and paste the values (no dependencies, apart from Excel)

Nope. Not possible. Like I said, "A fellow I work with is not very techno-savvy." :P :)

He'd have to do WAY too much BS to get things working. He can copy, and he can paste, and he can click. Macros? Waaaayyyy out. Get him to copy concatenation formulas (and have dependent cells)? Not gonna fly.

Nope. This was a quick hack to help him get column concatenated quickly without any extra information being needed.

It's hard working with anything in Excel given the funky way that Excel works with the clipboard, so there's that extra step to de-focus/de-select in there, but that's the only thing that's kind of icky.

But normally, yeah -- there are many other ways. This is just designed to work for a particular work-style.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #4 on: January 10, 2011, 02:38 AM »
Just for interest sake:

Code: C# [Select]
  1. private void textBox1_MouseClick(object sender, MouseEventArgs e)
  2.         {
  3.             this.textBox1.Text = Clipboard.GetText();
  4.             this.textBox2.Text = Clipboard.GetText().Replace("\t", this.textBox3.Text);
  5.         }
  6.  
  7.         private void textBox2_MouseClick(object sender, MouseEventArgs e)
  8.         {
  9.             Clipboard.SetText(this.textBox2.Text);
  10.         }

Stupidly simple. (Should have error checking and stuff, but meh... whatever...)

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

techidave

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 1,044
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #5 on: January 10, 2011, 06:35 AM »
Nice Renegade and Thanks for sharing! :Thmbsup: :Thmbsup:

This will come in handy at my school since I have users who aren't tech savy enough to do the concatenation feature.  Heck, I don't use it all that much because I find it too time consuming.  But heck, I have to look up how to do it each and every time I want to combine a few columns.

This will make it so much easier!

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #6 on: January 10, 2011, 05:28 PM »
Nope. Not possible. Like I said, "A fellow I work with is not very techno-savvy." :P :)

He'd have to do WAY too much BS to get things working. He can copy, and he can paste, and he can click. Macros? Waaaayyyy out. Get him to copy concatenation formulas (and have dependent cells)? Not gonna fly.

Nope. This was a quick hack to help him get column concatenated quickly without any extra information being needed.

It's hard working with anything in Excel given the funky way that Excel works with the clipboard, so there's that extra step to de-focus/de-select in there, but that's the only thing that's kind of icky.

But normally, yeah -- there are many other ways. This is just designed to work for a particular work-style.

Seemed like you were cracking a walnut with a sledgehammer is all, but I figured there was probably a reason.

I still think it could be done without the copy and paste steps via a macro, but I suspect the user will be using this on lots of different source documents

<rant>
Actually this sort of thing really burns me.  I have more than a few colleagues like that (I had someone doing important calculations ask me what the numbers in red were...) and even though they work with Excel all day every day they can't or won't take the time to learn some basics that will make their (working) lives easier
</rant>


cranioscopical

  • Friend of the Site
  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,776
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #7 on: January 10, 2011, 08:02 PM »
I had someone doing important calculations ask me what the numbers in red were...

And I suppose you told him you sweated blood to get them there…

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #8 on: January 10, 2011, 08:12 PM »
Seemed like you were cracking a walnut with a sledgehammer is all, but I figured there was probably a reason.

I still think it could be done without the copy and paste steps via a macro, but I suspect the user will be using this on lots of different source documents

Yes, yes and yes. :)

Since he'll be using it on different documents, getting him to start using macros and saving them into different files is simply way beyond practical.

It is using a sledgehammer, but anyone can use a sledgehammer. Figuring out how to use some dainty, dancing nutcracker just doesn't work sometimes.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #9 on: January 10, 2011, 08:49 PM »
And I suppose you told him you sweated blood to get them there…

no, but it sure gave me pores when she said it...

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #10 on: January 10, 2011, 09:04 PM »
<rant>
Actually this sort of thing really burns me.  I have more than a few colleagues like that (I had someone doing important calculations ask me what the numbers in red were...) and even though they work with Excel all day every day they can't or won't take the time to learn some basics that will make their (working) lives easier
</rant>

Oh yeah, spot on!  

But I'm either getting older and wiser, or just older and wore out.  I was going to chime in with Concatenation Formula! or VBA! (and the code could go in personal.xls to always be available).  But even before Renegade posted his explanation I got to thinking, "if his people are like my people, this really is a simpler solution and will most likely work better because they can't break it".

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #11 on: January 10, 2011, 09:12 PM »
<rant>
Actually this sort of thing really burns me.  I have more than a few colleagues like that (I had someone doing important calculations ask me what the numbers in red were...) and even though they work with Excel all day every day they can't or won't take the time to learn some basics that will make their (working) lives easier
</rant>

Oh yeah, spot on! 

But I'm either getting older and wiser, or just older and wore out.  I was going to chime in with Concatenation Formula! or VBA! (and the code could go in personal.xls to always be available).  But even before Renegade posted his explanation I got to thinking, "if his people are like my people, this really is a simpler solution and will most likely work better because they can't break it".

;)

Setup cost with the program I wrote: Double-click to run.  :Thmbsup:

Setup cost with VBA/macros: Step 1) lose all sanity...  :o

Need I say more? ;)

For anyone that has spent an hour trying to explain over the phone how to open a file... and failed... hehehe -- yeah... it happens... Sometimes ya gotta just pick yer battles. :)
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #12 on: January 10, 2011, 09:26 PM »
 
For anyone that has spent an hour trying to explain over the phone how to open a file... and failed...

stop, please.  I need to go to sleep and I can't if I'm laughing this hard.

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #13 on: January 10, 2011, 09:28 PM »
the code could go in personal.xls to always be available

actually the easiest way is to put it in an addin (XLA) - save it to the XLSTART directory and put a button on the toolbar...

don't know about that last bit in versions past 2003 (we finally got 'upgraded' about 2 months ago and I'm still struggling with all those productivity gains...)

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #14 on: January 10, 2011, 09:48 PM »
For anyone that has spent an hour trying to explain over the phone how to open a file... and failed... hehehe -- yeah... it happens... Sometimes ya gotta just pick yer battles. :)

i think you meant bottles...

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #15 on: January 10, 2011, 10:20 PM »
For anyone that has spent an hour trying to explain over the phone how to open a file... and failed... hehehe -- yeah... it happens... Sometimes ya gotta just pick yer battles. :)

i think you meant bottles...

Hahahah~! :P Yep.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

cranioscopical

  • Friend of the Site
  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,776
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #16 on: January 10, 2011, 10:48 PM »
o, but it sure gave me pores when she said it...
You shouldn't have been on your cell!

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #17 on: January 10, 2011, 11:10 PM »
You shouldn't have been on your cell!

I was only there to help her with her division


Perry Mowbray

  • N.A.N.Y. Organizer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 1,817
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #18 on: January 10, 2011, 11:19 PM »
actually the easiest way is to put it in an addin (XLA) - save it to the XLSTART directory and put a button on the toolbar...

don't know about that last bit in versions past 2003 (we finally got 'upgraded' about 2 months ago and I'm still struggling with all those productivity gains...)

It would be interesting to know how you go... I've got lots of Excel and Word toolbars around the building and we're looking at upgrading shortly...


cranioscopical

  • Friend of the Site
  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,776
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #19 on: January 10, 2011, 11:22 PM »
You shouldn't have been on your cell!

I was only there to help her with her division

On the plus side, at least it wasn't done under the table.

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #20 on: January 11, 2011, 12:24 AM »
It would be interesting to know how you go... I've got lots of Excel and Word toolbars around the building and we're looking at upgrading shortly...

I only have the one, but it's used all over the country.  If and when I do something about it I'll let you know 8)

FYI you're old toolbars will still showup, just not like they used to (they appear under the ADDINS tab of the ribbon, and they can be attached to the quick launch bar)

I have mixed feelings about the 'upgrade' - there are good things (substantially larger worksheets for one), but the interface changes are so drastic that my productivity took a huge hit (this has been discussed elsewhere).  Even 2 months on I still have to go searching for things I used to use all the time

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #21 on: January 11, 2011, 12:32 AM »
On the plus side, at least it wasn't done under the table.
-cranioscopical (January 10, 2011, 11:22 PM)

but we never did get to the root of the problem

Shades

  • Member
  • Joined in 2006
  • **
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #22 on: January 11, 2011, 04:06 PM »
Why not? The square is not that far away...

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #23 on: January 11, 2011, 04:27 PM »
Some people are just too chicken, and always ask Y they must X the road...
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utility: Convert Multiple Columns in Excel to a Single Column
« Reply #24 on: January 11, 2011, 04:35 PM »
Why not? The square is not that far away...

I simply don't have the power

Some people are just too chicken, and always ask Y they must X the road...

it's because the cars sometimes don't algebraic in time