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]

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.

Thanks so much for this. While this is way over my head at first glance, it does give me something to work with.

I will have to play around with it to see if it works for my form. If I use this, will anyone else using the form/worksheet also need to download the add-in (or have the same version of Excel as I do)?

I assume the $A$1:$A$99 is the range for column "A" and so forth? I'll have to hit my books and decipher how this works so I can understand it better. Thanks again, I'll let you know if I can get it to work.

 

[Peter_SSs]

DargonSwift

See if this is any use. It uses standard Excel formulas, but does require some 'helper' columns.

1. F1 houses 0
2. F2 (copied down): =COUNTA(OFFSET(A:A,,,,ROWS(F$2:F2)))
These results are the cumulative number of entries across the columns. There may be a better way to get these counts as OFFSET is generally not my first choice of functions.
3. G2: =LOOKUP(9.99999999999999E+307,F:F)
This gives the total number of entries.
4. I1 (copied down): =IF(ROWS(I$1:I1)>G$2,"",INDEX(A:C,ROW()-VLOOKUP(ROW()-1,F:F,1,1),MATCH(ROW()-1,F:F,1)))

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J
1	a	1	red			0	Total		a
2	b	2	blue		A	4	12		b
3	c	3	green		B	7			c
4	d		yellow		C	12			d
5			pink						1
6									2
7									3
8									red
9									blue
10									green
11									yellow
12									pink
13
14
Consolidate Columns

 

[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.

Thanks so much for this. While this is way over my head at first glance, it does give me something to work with.

I will have to play around with it to see if it works for my form. If I use this, will anyone else using the form/worksheet also need to download the add-in (or have the same version of Excel as I do)?

I assume the $A$1:$A$99 is the range for column "A" and so forth? I'll have to hit my books and decipher how this works so I can understand it better. Thanks again, I'll let you know if I can get it to work.

Here is a picture of my suggestion. Maybe it helps you understand.

Book1.xls
	A	B	C	D	E	F
1	Apples	Blue		Box	Apples
2	Oranges	Green		Crate	Oranges
3	Pears	Red		Envelope	Pears
4	Bananas	Yellow			Bananas
5	Mango	Orange			Mango
6		Violet			Blue
7		White			Green
8		Black			Red
9		Pink			Yellow
10		Brown			Orange
11					Violet
12					White
Sheet1