• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert a column into multiple rows

O

Omprakash

New Member
I am in need of a macro which will help me achieve convert a single column comma delimited values into multiple rows.

Input xls file example:
A B C D E F G
1 Text1 Text2 description goes here S,SK,12,D5,0,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM
2 Text1 Text2 description goes here S,MY,4,D0,0,NA|S,SR,6,D0,0,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM
3 Text1 Text2 description goes here S,SK,83,D0,0,NA|S,PT,45,D2,200,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM

Output required is:
A B C D E F G
1 Text1 Text2 description goes here S,SK,12,D5,0,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM
2 Text1 Text2 description goes here S,MY,4,D0,0,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM
2 Text1 Text2 description goes here S,SR,6,D0,0,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM
3 Text1 Text2 description goes here S,SK,83,D0,0,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM
3 Text1 Text2 description goes here S,PT,45,D2,200,NA 12/9/2013 8:08:25 AM 12/9/2013 10:32:50 AM

So basically if COL E has multiple "|" delimited values, it should split and create a new row and rest should be the same. Also, need to have the time diff between COL G and COL F for each row.
 

Attachments

  • om.xlsx
    11.7 KB · Views: 6
Hi, Omprakash!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at the uploaded file.


In 1st worksheet, input, there're a few helper columns used to support the build-up in 3rd worksheet. These are the formulas:

Column O: Count of individual strings in column E
O2: =LARGO(E2)-LARGO(SUSTITUIR(E2;"|";""))+1 -----> in english: =LEN(E2)-LEN(SUBSTITUTE(E2,"|",""))+1

Column P: Accumulated of O
P1: 0
P2: =SUMA(O$2:O2) -----> in english: =SUM(O$2:O2)

Column Q: Total of entries (rows)
Q1: =SUMA(O:O) -----> in english: =SUM(O:O)

Columns R:V : relative positions of the nth separator (assuming one at the end of E cell entry)
R1: ="Pos."&COLUMNA()-17 -----> in english: ="Pos."&COLUMN()-17
R2: =ENCONTRAR("|";$E2&"|";Q2+1) -----> in english: =FIND("|",$E2&"|",Q2+1)

Copy R1 across thru V1 or as required. Copy O2:V2 down as required.


In 3rd worksheet:

A1:N1 : =input!A1
O1: Total Time
P1: In what row? (helper column)
Q1: In what column? (helper column)

A2:N2 except E2: =SI(FILA()-1<=input!Q$1;SI(INDICE(input!A:A;$P2+1)="";"";INDICE(input!A:A;$P2+1));"") -----> in english: =IF(ROW()-1<=input!Q$1,IF(INDEX(input!A:A,$P2+1)="","",INDEX(input!A:A,$P2+1)),"")

E2: =SI(FILA()-1<=input!Q$1;EXTRAE(INDICE(input!E:E;$P2+1);INDIRECTO(DIRECCION(P2+1;COLUMNA(P2)+Q2;4;1;"input"))+1;INDIRECTO(DIRECCION(P2+1;COLUMNA(Q2)+Q2;4;1;"input"))-INDIRECTO(DIRECCION(P2+1;COLUMNA(P2)+Q2;4;1;"input"))-1);"") -----> in english: =IF(ROW()-1<=input!Q$1,MID(INDEX(input!E:E,$P2+1),INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))+1,INDIRECT(ADDRESS(P2+1,COLUMN(Q2)+Q2,4,1,"input"))-INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))-1),"")

O2: =SI(FILA()-1<=input!Q$1;SI(N2="";"";N2-I2);"") -----> in english: =IF(ROW()-1<=input!Q$1,IF(N2="","",N2-I2),"")

P2: =SI(FILA()-1<=input!Q$1;COINCIDIR(FILA()-1-0,1;input!P:p;1);"") -----> in english: =IF(ROW()-1<=input!Q$1,MATCH(ROW()-1-0.1,input!P:p,1),"")

Q2: =SI(FILA()-1<=input!Q$1;CONTAR.SI(P$2:p2;P2);"") -----> in english: =IF(ROW()-1<=input!Q$1,COUNTIF(P$2:p2,P2),"")

Copy A2:Q2 down as required. In the sample file the yellow area indicates up to which row formulas are copies.


In both worksheets yellow cells at row 1 indicate helper columns.


Just advise if any issue.

Regards!

PS: Be as kind as to replace :p by ": P" unquoted and without the space embedded. Thank you.
 

Attachments

  • convert a column into multiple rows - om (for Omprakash at chandoo.org).xlsx
    19.5 KB · Views: 4
thanks it is working, but what logic is being used to copy the data from sheet 1 to sheet3.
did not understand the func code on sheet 3 A2.
Also please help by explaining E2 code from sheet 3.
 
Hi, Omprakash!

First. Your original issue.
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Second. Logic to copy data from worksheet 1 to 3.
No data copy is involved, just placed formulas in 3rd worksheet that reference and extract data from 1st.

A2:
=IF(ROW()-1<=input!Q$1,IF(INDEX(input!A:A,$P2+1)="","",INDEX(input!A:A,$P2+1)),"")
input!Q$1 holds the no. of rows to be created, so the red IF condition is to set the output to null when out of scoped range.
IF(INDEX(input!A:A,$P2+1)="","",INDEX(input!A:A,$P2+1)
The red IF condition is to avoid displaying a default 0 when the cell referenced by the INDEX function is empty.
INDEX(input!A:A,$P2+1)
INDEX function extracts the $P2+1 row entry of input!A:A range.

E2:
=IF(ROW()-1<=input!Q$1,MID(INDEX(input!E:E,$P2+1),INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))+1,INDIRECT(ADDRESS(P2+1,COLUMN(Q2)+Q2,4,1,"input"))-INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))-1),"")
Same previous considerations for the red IF condition.
MID(INDEX(input!E:E,$P2+1),INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))+1,INDIRECT(ADDRESS(P2+1,COLUMN(Q2)+Q2,4,1,"input"))-INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))-1)
MID function extracts data from 1st argument, starting at position of 2nd argument and for a length of 3rd argument.
1st argument: INDEX(input!E:E,$P2+1), same as explained for A2.
2nd/3rd arguments: they're the pairs of adjacent cells from Q/R thru U/V helper columns at 1st worksheet. Basically they point to the position of each separator char "|" within E original cell, with the addition of a last "|" so as to always have a last ending delimiter. The way to reference them with a single formula is using the INDIRECT function:
From: INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))+1
It takes the 1st argument as address base (P2+1 row, Q2+1 column, relative referencing (4), format A1 (1), worksheet "input"), and adds 1 since that cell stores the occurrence of a "|" and we need to start at the next character.
To: it's built with the following separator regarding From, and then subtractring From.
INDIRECT(ADDRESS(P2+1,COLUMN(Q2)+Q2,4,1,"input"))-INDIRECT(ADDRESS(P2+1,COLUMN(P2)+Q2,4,1,"input"))-1)

Hope it helps.

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.
The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.

Regards!
 
You must log in or register to reply here.
Back
Top