Question: Text sorting in columns

DargonSwift

New Member
Joined
Aug 7, 2006
Messages
23
I'm a novice at Excel, but I've been learning a lot from this board, so I'm hoping one of you guru's can help me with my current obstacle.

Background:
I am creating a form that will produce text in 5 or more columns based on various IF/VLOOKUP/HLOOKUP based formulas (The data will be coming from a table). The data is already formated to fit the column width (since I haven't figured out how to tell it to take any data that doesn't fit and put it on the next cell down), so some lines will be broken up on different cells, and need to be preserved in that order.

Basic Problem:
I want to combine the data from around 5 columns into one column, but I don't want there to be any blank lines. I've seen other forms do just this using macros, but I know nothing about VB (yet).

So, I need to take data from Columns A, B, C, D, and E; combine them into column F without any additional lines/spaces, and preserve the order of the original columns.

I hope I've communicated this well enough. I'll search the forums for how to insert a sample of what I'm looking for. Thanks in advance for taking time to look at this.


-D
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

DargonSwift

New Member
Joined
Aug 7, 2006
Messages
23
If anyone needs more information on this, please ask. I can create an example or something.

I'm currently looking through functions trying to find something that will work. The only thing I can think of right now is a whole lot of IF statements, or possibly using a matrix of some kind that identifies which cells in each column have data, and then some how organize it.

Any advice would be appreciated.
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
does concatenate function work for you?

=concatenate(a1,b1,c1,d1,e1)
 

DargonSwift

New Member
Joined
Aug 7, 2006
Messages
23
does concatenate function work for you?

=concatenate(a1,b1,c1,d1,e1)

No, I'm not trying to combine them in that manner. I appologize if I was unclear on this. I want to stack the results of columns A through D on top of each other, ignoring blank cells (in a range).

So, if column A (say a range of A1:A10) has 5 items in it:

Column A
Apples
Oranges
Pears
Bananas
Mango

and Column B (range of B1:B10) has 10 items in it:

Column B
Blue
Green
Red
Yellow
Orange
Violet
White
Black
Pink
Brown

...Column C (range of C1:C10) is empty, but Column D (range of D1:D10)has:

Column D
Box
Crate
Envelope

...The data I want displayed in Column E would be:

Column E
Apples
Oranges
Pears
Bananas
Mango
Blue
Green
Red
Yellow
Orange
Violet
White
Black
Pink
Brown
Box
Crate
Envelope

I hope this helps explain what I'm looking for. I am hoping to do this without a macro, since I'm woefully unfamiliar with VBA. I had thought to use IF statements, but I'd end up with blank lines.

I'm sure I could eventually get it with a lot of IF's and some kind of matrix (currently looking at that option), but I'm hoping someone can help me find something a bit cleaner and easier. I'm reviewing some regular functions I'm not familiar with, such as "IsBlank" to see if I can find a work around.

Thank you for your reply though.


-DS
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

I dont think this can be handled by a formula, yet I was happy to see that from the formula expert on the board.
 

DargonSwift

New Member
Joined
Aug 7, 2006
Messages
23
I dont think this can be handled by a formula, yet I was happy to see that from the formula expert on the board.

I agree, I doubt there is a clear cut formula to do it. I'm sure it'd be easy to do in VBA, but I'm lost there. I've got an idea to create a table that determines if each cell in my columns is empty or not, using "IsBlank". Then use that table to create another table that assigns an order to the columns I want. It may take a few tables, a lot of IFs, and some INDEXing, but I'm sure I can get what I want eventually. Its just a very ugly way to do it.
 

DargonSwift

New Member
Joined
Aug 7, 2006
Messages
23

ADVERTISEMENT

Anyone have any suggestions for this?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

If you download and install morefunc add-in from here
http://xcell05.free.fr/english/index.html

You can then use:

=INDEX(ARRAY.JOIN($A$1:INDEX($A$1:$A$99,MATCH(2,1/($A$1:$A$99<>""))),$B$1:INDEX($B$1:$B$99,MATCH(2,1/($B$1:$B$99<>""))),$D$1:INDEX($D$1:$D$99,MATCH(2,1/($D$1:$D$99<>"")))),ROW()-ROW($E$1)+1)

Confirmed with Ctrl + shift + enter in E1, then dragged down.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows
DargonSwift

Are there spaces within the data in a column? Or is each column filled from the top like this for example?
Mr Excel.xls
ABCD
1a1red
2b2blue
3c3green
4dyellow
5pink
6
Consolidate Columns
 

DargonSwift

New Member
Joined
Aug 7, 2006
Messages
23
DargonSwift

Are there spaces within the data in a column? Or is each column filled from the top like this for example?

Your example is accurate. The columns will be a set range of rows (undetermined as of yet), and the only blanks rows in that range will be at the bottom of the range.
 

Forum statistics

Threads
1,141,293
Messages
5,705,532
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top