Transpose from cell to multiple rows

[DTMHibbert]

Hi all,

Im looking for a way using VBA code to transpose data contained in one cell separated by ";" to multiple rows. Below is an example of what im trying to achieve

Event ID Day Start End Room
1 Monday 09:00 11:00 Room 1; Room 2; Room 3; Room 4
1 Monday 09:00 11:00 Room 1; Room 2; Room 3; Room 4
1 Monday 09:00 11:00 Room 1; Room 2; Room 3; Room 4
1 Monday 09:00 11:00 Room 1; Room 2; Room 3; Room 4
2 Tuesday 15:00 16:00 Room 1; Room 2
2 Tuesday 15:00 16:00 Room 1; Room 2
3 Wednesday 12:00 14:00 Room 1; Room 2; Room 3
3 Wednesday 12:00 14:00 Room 1; Room 2; Room 3
3 Wednesday 12:00 14:00 Room 1; Room 2; Room 3

Event ID Day Start End Room
1 Monday 09:00 11:00 Room 1
1 Monday 09:00 11:00 Room 2
1 Monday 09:00 11:00 Room 3
1 Monday 09:00 11:00 Room 4
2 Tuesday 15:00 16:00 Room 1
2 Tuesday 15:00 16:00 Room 2
3 Wednesday 12:00 14:00 Room 1
3 Wednesday 12:00 14:00 Room 2
3 Wednesday 12:00 14:00 Room 3

As you can see the data in the room column in all combined into one cell however it does produce duplicate rows for each room used. (Event 1 has 4 rows because it has rooms being used). Some events would only have 1 row while some larger events could have roughly 30/40

I did produce a vba script based of something i found online but i have since lost this and cannot find the source again.

Thanks guys.

 

[FormR]

Hi, welcome to the forum!

I guess it Depends on how often you needs to do this and how much data is involved - but maybe you could make use of a formula based option:

Excel 2013/2016
	A	B	C	D	E	F
1	ID	Day	Start	End	Room List	Seperated
2	1	Monday	09:00	11:00	Room 1; Room 2; Room 3; Room 4	Room 1
3	1	Monday	09:00	11:00	Room 1; Room 2; Room 3; Room 4	Room 2
4	1	Monday	09:00	11:00	Room 1; Room 2; Room 3; Room 4	Room 3
5	1	Monday	09:00	11:00	Room 1; Room 2; Room 3; Room 4	Room 4
6	2	Tuesday	15:00	16:00	Room 1; Room 2	Room 1
7	2	Tuesday	15:00	16:00	Room 1; Room 2	Room 2
8	3	Wednesd	12:00	14:00	Room 1; Room 2; Room 3	Room 1
9	3	Wednesd	12:00	14:00	Room 1; Room 2; Room 3	Room 2
10	3	Wednesd	12:00	14:00	Room 1; Room 2; Room 3	Room 3
Sheet1

Cell Formulas
Range	Formula
F2	=TRIM(MID(SUBSTITUTE(E2,";",REPT(" ",99)),COUNTIF(A$2:A2,A2)*99-98,99))

 

[DTMHibbert]

well i feel i bit daft now as i have just taken the formula and dumped it into my spreadsheet and realised it doesn't work because in my actual export from the system we use it does produce an Event ID.

I did try your formula on the example i gave and it works perfectly so thanks very much that and speedy reply. Can this be adapted?

in any one export we have up to 100,000 rows of data and this would be used weekly.

Thanks

 

[FormR]

it doesn't work because in my actual export from the system we use it does produce an Event ID.

Hi, i'm not sure what you mean here - maybe you can re-post some sample data that accurately reflects your export - be sure to also include what the expected results should look like.

in any one export we have up to 100,000 rows of data

That's a lot of data and formulas may well prove to be too resource hungry.

 

[DTMHibbert]

The export from our system doesn't contain a column for event ID so an example would be the same as above just without column A.

I think this is why i had a vba script last time.

 

[FormR]

Hi, try this on a copy of your workbook.

Assumes your room list column is "E" starting on row "2" - the results are output to column "F" - change as appropriate.

Sub m()
'https://www.mrexcel.com/forum/excel-questions/1025647-transpose-cell-multiple-rows.html
Dim a As Variant, i As Long, j As Long, k As Long, Room As Variant
a = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).Value
ReDim b(1 To UBound(a), 1 To 1)
k = 1
For i = 1 To UBound(a)
    Room = Split(a(i, 1), ";")
    For j = 0 To UBound(Room)
        b(k, 1) = Trim(Room(j))
        k = k + 1
    Next j
    i = i + UBound(Room)
Next i
Range("F2").Resize(UBound(a)).Value = b
End Sub

 

[DTMHibbert]

Im getting the following error;

Run-time error '9':
Subscript out of range - when i click debug it highlights this text ---- b(k, 1) = Trim(Room(j))

I changed the column references to like you said.

Thanks

 

[FormR]

I changed the column references to like you said.

Hi, can you post the amended code and state which column contains the list of rooms and what row they start on.

Subscript out of range - when i click debug it highlights this text ---- b(k, 1) = Trim(Room(j))

When you get the error, can you hit debug and report what this returns from the immediate window.

? i " | " & k & " | " & a(i,1)