How to combine multiple rows and columns into one row and one column
Category: [Excel] Demo Available
How to combine multiple rows and columns into one row and one column
Requirement:
The user has data that comes from a text file and gets pasted across multiple rows and columns and wants to make a table with one row and one column.
So, the user wants to convert this:
| ABC | 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 | |
| 9 | 10 | |||
| DEF | 1 | 2 | 3 | |
| GHI | 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 | |
| JKL | 1 | |||
| MNO | 1 | 2 | 3 | 4 |
| PQR | 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 | |
| 9 | 10 | 11 | 12 | |
| 13 |
to this:
| ABC | 1 |
| ABC | 2 |
| ABC | 3 |
| ABC | 4 |
| ABC | 5 |
| ABC | 6 |
| ABC | 7 |
| ABC | 8 |
| ABC | 9 |
| ABC | 10 |
| DEF | 1 |
| DEF | 2 |
| DEF | 3 |
| GHI | 1 |
| GHI | 2 |
| GHI | 3 |
| GHI | 4 |
| GHI | 5 |
| GHI | 6 |
| GHI | 7 |
| GHI | 8 |
| JKL | 1 |
| MNO | 1 |
| MNO | 2 |
| MNO | 3 |
| MNO | 4 |
| PQR | 1 |
| PQR | 2 |
| PQR | 3 |
| PQR | 4 |
| PQR | 5 |
| PQR | 6 |
| PQR | 7 |
| PQR | 8 |
| PQR | 9 |
| PQR | 10 |
| PQR | 11 |
| PQR | 12 |
| PQR | 13 |
Solution:
Code:
Sub aa()
Dim x, z(), i&, ii&, j&, r As Range
x = ActiveSheet.UsedRange.Value
ReDim z(1 To UBound(x) * UBound(x, 2), 1 To 2)
For i = 1 To UBound(x)
For ii = 2 To UBound(x, 2)
If Len(x(i, ii)) Then
j = j + 1
z(j, 1) = x(i, 1)
z(j, 2) = x(i, ii)
End If
Next ii
Next i
With Sheets.Add.[a1]
.Resize(UBound(z, 1), 2) = z
Set r = .Parent.Columns(2).SpecialCells(2).Offset(, -1).SpecialCells(xlBlanks)
r.FormulaR1C1 = "=R[-1]C"
With .Parent.Columns(1)
.Value = .Value
End With
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by graha_karya.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to combine several sheets to one sheet |
| How to use a macro to find value in a range of cells and combine values |
| How to transfer and combine data from one sheet to another |
| How to combine more that one IF AND formula |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.