Convert rows into columns adding certain Number

[elbrujo]

Hello there,

I need to convert a row with certain text delimited by semicolons into two columns, one containing the text from the row, and another one containing its ID

For example:

1	something goes here; another thing here; etc
2	here something goes; a thing goes here; cte
3	something here goes; thing another here; tce
4	goes a thing here; another here; xyz

And it should be like this:

1	1	something goes here
2	1	another thing here
3	1	etc
4	2	here something goes
5	2	a thing goes here
6	2	cte
7	3	something here goes
8	3	thing another here
9	3	tce
10	4	goes a thing here
11	4	another here
12	4	xyz

Note that in the first table, there is an ID for each row, but I need to create a new column containing the indicator that X statement belongs to the same ID, and so that's why the first three rows are ID number 1, as in the first table, they all belonged to the same cell. Is it clear? I apologize, English isn't my first language.

I was thinking I could add a sequence number to each semicolon, to have something like

something goes here; 1; another thing here; 1; etc; 1

(note that in the first table that the final string doesn't have a final semicolon... So I'd have to add that one), and then importing it via csv, I'd have many columns that hopefully I could convert into rows...

Unfortunately I might imagine the logic behind it, but I don't know how could I add a sequence based on a wildcard, like "add X to *;"

Thank you very much for your time, dear forumers. Any advise is highly appreciated.
Have a great day.

Regards.

[LokeshKumar]

Could you please upload sample workbook.....

[elbrujo]

Hello there,

I have uploaded a sample workbook, since the original contains thousands of rows.
As you can see, there are some numbers missing in column "A", since some have been removed arbitrary, I'd need that each University in between the semicolons, has its own row with the number it has assigned by column "A". Am I being clear?

Thank you very much for your time sir,
Have a great day.

[:) Sixthsense :)]

	A	B
1	1	something goes here; another thing here; etc
2	2	here something goes; a thing goes here; cte
3	3	something here goes; thing another here; tce
4	4	goes a thing here; another here; xyz

	C	D
1	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:1)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:1)),"")
2	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:2)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:2)),"")
3	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:3)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:3)),"")
4	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:4)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:4)),"")
5	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:5)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:5)),"")
6	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:6)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:6)),"")
7	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:7)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:7)),"")
8	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:8)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:8)),"")
9	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:9)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:9)),"")
10	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:10)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:10)),"")
11	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:11)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:11)),"")
12	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:12)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:12)),"")
13	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:13)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:13)),"")
14	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:14)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:14)),"")
15	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:15)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:15)),"")
16	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:16)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:16)),"")
17	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:A),ROWS($1:17)),"")	=IFERROR(ConvertToRows($A$1:$B$4,COLUMNS($A:B),ROWS($1:17)),"")

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

[elbrujo]

Hi there,

I deeply apologize, but I don't see the code resulting in any change to the table, the content is still:

1	something goes here; another thing here; etc
2	here something goes; a thing goes here; cte
3	something here goes; thing another here; tce
4	goes a thing here; another here; xyz

Any advise?
Thank you,
Regards.

[:) Sixthsense :)]

I deeply apologize, but I don't see the code resulting in any change to the table, the content is still:

Please refer the attached file

[elbrujo]

Thank you very much @Sixthsense!, it worked like a charm.

I was wondering if there is any way to speed up the calculating process, since there are about 50K records and according to my calculations it might take over 10 hours

Edit:
Oh, another two concerns:
1. Would it process the file every time it opens?
2. Could I save the result as a plain text sheet and not a dynamic table?

Thank you very much for your time,
Regards.

[elbrujo]

How could I convert this dynamic result into plain text? it just finished.

[:) Sixthsense :)]

I was wondering if there is any way to speed up the calculating process

I changed the UDF to macro so it will work faster

Download the macro file and in Result sheet Click the Macro button to get the data.

[elbrujo]

Hi there,

Sorry to reopen this thread. But I'm using this macro to do the same on another table, it is working fine until record 3313, after that one, the macro doesn't work... And starts rendering numers like... But if I remove the last 3314 record, it works... Any adivse?

See the attachments. The macro works with 3313 records, and doesn't work with 3314 or more. There are more than 8K records, it starts failing from 3313

Doesn't work:
Sin título.png
Sin título2.png



Works
Sin título3.png
Sin título4.png

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic