Consolidate several columns into one (not concatenate) (formula solution (not vba))

[melendezml]

Greetings of harmony,
I have the following situation and I'm trying to do this using a PC with Excel 2007.

I would like this:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]T[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]C[/TD]
[TD]O[/TD]
[TD]E[/TD]
[TD]I[/TD]
[TD]M[/TD]
[TD]I[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]K[/TD]
[TD]U[/TD]
[TD]M[/TD]
[TD]H[/TD]
[TD]R[/TD]
[TD]L[/TD]
[TD]N[/TD]
[TD]E[/TD]
[TD][/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]S[/TD]
[TD]U[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]G[/TD]
[TD]W[/TD]
[TD]TH[/TD]
[TD]IS [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 566"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


To look like this:

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]H[/TD]
[/TR]
[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]N[/TD]
[/TR]
[TR]
[TD]K[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]U[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]M[/TD]
[/TR]
[TR]
[TD]U[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]H[/TD]
[/TR]
[TR]
[TD]F[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]R[/TD]
[/TR]
[TR]
[TD]H[/TD]
[/TR]
[TR]
[TD]E[/TD]
[/TR]
[TR]
[TD]L[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]I[/TD]
[/TR]
[TR]
[TD]N[/TD]
[/TR]
[TR]
[TD]G[/TD]
[/TR]
[TR]
[TD]M[/TD]
[/TR]
[TR]
[TD]E[/TD]
[/TR]
[TR]
[TD]W[/TD]
[/TR]
[TR]
[TD]I[/TD]
[/TR]
[TR]
[TD]TH[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]H[/TD]
[/TR]
[TR]
[TD]IS [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This procedure will become part of a macro. The number of rows will vary but the number of columns will be the same (11 columns).
I don't care too much about the order since I can later sort. As long as the data from all columns in now in one column.

Appreciatively,
Maria

 

[AlphaFrog]

Confused

Title says; " (formula solution (not vba)) ".
Description contradicts; "This procedure will become part of a macro."

 

[John Davis]

If it will become part of a macro, then why do you need a formula?

 

[melendezml]

If it will become part of a macro, then why do you need a formula?

Well, to be honest, I'm not too familiar with vba... even though that is the language that a macro uses.
I do know how to record a macro and am comfortable using formulas and would prefer the option of using a formula and then creating the macro myself at least until I take up on the task of learning more about vba.

Best,
Maria

 

[XOR LX]

Hi,

One way, assuming that the data is in A1:K3 and that the blanks, if any, are "genuine" blanks (and not e.g. the "" as a result of formulas in those cells), and after first defining the following in Name Manager:

Name</SPAN>: </SPAN>RArry</SPAN>
Refers to</SPAN>: </SPAN>=ROW(INDIRECT("1:"&COLUMNS(Sheet1!$A$1:$K$3)))-1</SPAN>

Name</SPAN>: </SPAN>CArry</SPAN>
Refers to</SPAN></SPAN>: =TRANSPOSE(COLUMN(Sheet1!$A$1:$K$3))>=COLUMN(Sheet1!$A$1:$K$3)</SPAN>

is the following array formula**</SPAN>:</SPAN>

=IFERROR(INDEX(INDEX($A$1:$K$3,,MATCH(TRUE,MMULT((0+(CArry)),SUBTOTAL(3,OFFSET($A$1:$A$3,,RArry,,)))>=ROWS($1:1),0)),SMALL(IF((1-(INDEX($A$1:$K$3,,MATCH(TRUE,MMULT((0+(CArry)),SUBTOTAL(3,OFFSET($A$1:$A$3,,RArry,,)))>=ROWS($1:1),0))="")),ROW($A$1:$A$3)-MIN(ROW($A$1:$A$3))+1),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+MMULT((0+(CArry)),SUBTOTAL(3,OFFSET($A$1:$A$3,,RArry,,)))),ROWS($1:1)))),"")</SPAN>

Copy down until you start to get blanks.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[melendezml]

It didn't work for me but I will carefully follow steps again and check.
Also, I have a question. Instead of copying down until I start to get blanks, is it possible to select a range and count all non-blanks and then tell the formula how many times to drag down?
Thanks.

 

[XOR LX]

Make sure to amend the Sheet1 in the Named Ranges if required.

Without VBA, you cannot tell Excel how far to copy down a formula. However, provided that the number of rows to which you copy the formula is at least equal to the number of cells in your range, you will not have to worry about missing results.

Regards

 

[hiker95]

melendezml,

Thanks for the Private Message.

The below macro should adjust for a varying number of rows and columns.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	T	N	O	O	C	O	E	I	M	I	T
2	H	K	U	M	H	R	L	N	E		H
3	A	Y	S	U	F	H	P	G	W	TH	IS
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	T	N	O	O	C	O	E	I	M	I	T		T
2	H	K	U	M	H	R	L	N	E		H		H
3	A	Y	S	U	F	H	P	G	W	TH	IS		A
4													N
5													K
6													Y
7													O
8													U
9													S
10													O
11													M
12													U
13													C
14													H
15													F
16													O
17													R
18													H
19													E
20													L
21													P
22													I
23													N
24													G
25													M
26													E
27													W
28													I
29													TH
30													T
31													H
32													IS
33
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 05/06/2014, ME775842
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, n As Long, c As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range(Cells(1, 1), Cells(lr, lc))
n = Application.CountA(Range(Cells(1, 1), Cells(lr, lc)))
ReDim o(1 To n, 1 To 1)
For c = 1 To lc
  For i = 1 To lr
    If a(i, c) <> "" Then
      j = j + 1
      o(j, 1) = a(i, c)
    End If
  Next i
Next c
Columns(lc + 2).ClearContents
Cells(1, lc + 2).Resize(n).Value = o
Columns(lc + 2).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.