How to convert multiple rows and columns of data in EXCLE into one column

How to convert multiple rows and columns of data in EXCLE into one column

Option Base 1

Sub cell range except spaces()

Dim my As Range, my1(), c As Range

Dim p As Long

Dim i As Long

Set my = Application.InputBox(Prompt:="Use the mouse to select a cell range", Type:=8)

p = my.Count

i = 1

ReDim my1(p, 1)

For Each c In my

If c.Value "＂" Then

my1(i, 1) = c.Value

i = i 1

End If

Next c

Set my = Application.InputBox(Prompt:="Use the mouse to select the first cell to output", Type:=8)

Application.ScreenUpdating = False

If i = 1 Then

my = my1

Else

Range(my, my.Offset(i - 2, 0)) = my1

End If

Application.ScreenUpdating = True

MsgBox "Complete."

End Sub

This code can achieve what you want.

Operation method reference

How to use excel to arrange data in multiple rows and columns into one column

You can refer to the question "Help: Is there a way to batch convert rows to columns" that I answered in "" yesterday

That is, convert the above 3 rows into the following 2 columns, I don’t know if this is what it means!

1. Enter the formula in cell A10:

=OFFSET($A$1,INT((ROW(A1)-1)/10),MOD(ROW(A1)-1,10))

Copy and drop down

2. Enter the formula in cell B10:

=IF(OFFSET($A$1,INT((ROW(B1)-1)/10),MOD(ROW(B1) 9,10) 1)=＂＂,＂＂,OFFSET($A$1 ,INT((ROW(B1)-1)/10),MOD(ROW(B1) 9,10) 1))

Copy and drop down

3. Select the area A10:B39, right-click to copy - then right-click - Paste Special - Value

That is, the data area of ​​A10:B39 is formed, the formulas are all gone, and all the cells become data.

4. Sort and delete the rows with spaces in column B. That's it.

How to turn multiple rows and columns of excel table into one column of data

E2=IF(ROW(A1)>COUNTA($A$2:$D$9),"＂",INDIRECT(TEXT(SMALL(IF($A$2:$D$9＂",ROW($A$2: $D$9)*100 COLUMN($A$2:$D$9),99^9),ROW(A1)),"r0c00"),))

F2=IF(ROW(A1)>COUNTA($A$2:$D$9),"＂,INDEX($A$1:$D$9,MOD(SMALL(IF($A$2:$D$9＂" ",ROW($A$2:$D$9)/100 COLUMN($A$2:$D$9),99^9),ROW(A1)),1)*100,INT(SMALL(IF($A$2 :$D$9＂＂,ROW($A$2:$D$9)/100 COLUMN($A$2:$D$9),99^9),ROW(A1))))

Copy the array formula downwards [Array formula, after entering the formula, place the cursor in the formula edit bar and press CTRL SHIFT and Enter to make the array formula take effect]

The above is the detailed content of How to convert multiple rows and columns of data in EXCLE into one column. For more information, please follow other related articles on the PHP Chinese website!