Question: Text sorting in columns

[DargonSwift]

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

 

[DargonSwift]

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]

does concatenate function work for you?

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

 

[DargonSwift]

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]

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]

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]

Anyone have any suggestions for this?

 

[fairwinds]

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]

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
	A	B	C	D
1	a	1	red
2	b	2	blue
3	c	3	green
4	d		yellow
5			pink
6
Consolidate Columns

 

[DargonSwift]

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.