• 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.

Need to split data in multiple rows

@Gopigk

New Member
Hi all,

I have data in excel, where I am receiving multiple Job/Lot numbers in one cell against one Invoice number. But I need that data like each job/Lot-wise in separate rows. It is a repeated task and I am doing every time like separating them into columns and transpose them into rows. As I need to work on huge data, this is a very tedious task for me to complete. Herewith I attached a sample excel file for ready reference. There are 2 sheets, in sheet 1 original raw data as received and in sheet 2 sample format as I required.

Request to verify and help me in this
 

Attachments

bosco_yip

Excel Ninja
2 set of formulas is enough for your work

1] In L2, copied right to M2 and all copied down :

=TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",,D$2:D$10),",",REPT(" ",350)),ROW(A1)*350,350))

Remark : The above formula used a TEXTJOIN function of which available in Office 365 or Excel 2019 only.

2] In I2, formula copied right to J2, K2 N2 & O2 and all copied down :

=IF($L2="","",LOOKUP(1,0/FIND($L2,$D$2:$D$10),A$2:A$10))

71917
 

Attachments

Last edited:
Please try Power Query

Select Raw data > ribbon Data> From Table > Advanced editor > Paste below code

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.no", Int64.Type}, {"Inv no", type text}, {"Inv date", type date}, {"Job No.", type text}, {"Lot No.", type text}, {"Amt", Int64.Type}, {"O/st amt", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns( { Text.Split([#"Job No."],","),Text.Split([#"Lot No."],",")},{"Job No.","Lot No."})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Job No.", "Lot No."}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Job No.", "Lot No."}, {"Job No.", "Lot No."}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"S.no", "Inv no", "Inv date", "Job No.", "Lot No.", "Amt", "O/st amt"})
in
    #"Reordered Columns"
 

Attachments

@Gopigk

New Member
2 set of formulas is enough for your work

1] In L2, copied right to M2 and all copied down :

=TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",,D$2:D$10),",",REPT(" ",350)),ROW(A1)*350,350))

Remark : The above formula used a TEXTJOIN function of which available in Office 365 or Excel 2019 only.

2] In I2, formula copied right to J2, K2 N2 & O2 and all copied down :

=IF($L2="","",LOOKUP(1,0/FIND($L2,$D$2:$D$10),A$2:A$10))

View attachment 71917
Dear Bosco sir, thank you very much for your great help and efforts.
It's working fine on this small sample data, but when I am applying it to my actual data, where the number of rows are around 1500 to 2000, (sometimes it may increase), it's not working. Actually, I didn't understand the 350 figure in the formula, I tried to increase it based on the number of rows, but didn't get results. could you please help with this?

Thanks in advance.
 

bosco_yip

Excel Ninja
Dear Bosco sir, thank you very much for your great help and efforts.
It's working fine on this small sample data, but when I am applying it to my actual data, where the number of rows are around 1500 to 2000, (sometimes it may increase), it's not working. Actually, I didn't understand the 350 figure in the formula, I tried to increase it based on the number of rows, but didn't get results. could you please help with this?

Thanks in advance.
Textjoin function unable to concatenate 2000 rows of data and exceeding its limitation.

Please switch to PQ as per Post #.3 suggestion.

Regards
 

@Gopigk

New Member
Thank you, sir, for your suggestion. but couldn't understand that PQ, and unable to do the same as suggested in Post#3

anyway thank you very much both of you for trying to help
 

Peter Bartholomew

Well-Known Member
Another response that will not be of direct value :(. I am experimenting with a new 365 version of Excel which allows one to program on the grid using worksheet formulae within the LET function. The formula is by by far the most involved that I have tried but I am still at the stage of exploring what is possible.
Code:
= LET(
  list, sColon&TEXTJOIN(sColon,,JobNo.)&sColon,
  n,    LEN(list),
  k,    SEQUENCE(n),
  chr,  MID(list, k, 1),
  job\, FILTER( k, (chr=comma)+(chr=sColon) ),
  inv\, FILTER( k, chr=sColon),
  firstchr, FILTER(job\+1, job\<n),

  job, LET(
    finalchr, FILTER(job\-1, job\>1),
    MID(list, firstchr, finalchr-firstchr+1) ),

  lot, LET(
    list, comma&TEXTJOIN(comma,,LotNo.)&comma,
    n,    LEN(list),
    k,    SEQUENCE(n),
    chr,  MID(list, k, 1),
    lot\, FILTER( k, chr=comma ),
    firstchr, FILTER(lot\+1, job\<n),
    finalchr, FILTER(lot\-1, job\>1),
    MID(list, firstchr, finalchr-firstchr+1) ),

  serialNum, XMATCH(firstchr-1, inv\, -1),
  newInvoice?, ISNUMBER(XMATCH(firstchr-1, inv\)),
  invoice,  INDEX(InvoiceNo., serialNum),
  date,     INDEX(InvoiceDate, serialNum),
  amt,      IF(newInvoice?, INDEX(Amount, serialNum), ""),
  outstamt, IF(newInvoice?, INDEX(Outstanding, serialNum), ""),

  CHOOSE({1,2,3,4,5,6,7}, serialNum, invoice, date, job, lot, amt, outstamt)  )
As for the output
71941
The formula is in cell K1. The shading is generated as a conditional format.
The starting point of using TEXTJOIN was taken from Bosco's solution. The splitting algorithm is somewhat more pedantic.
 

@Gopigk

New Member
@Gopigk
Testing:
Open this file
Press [ Do It ]-button

Use with Your own data
Copy code from sheet2's code-page to Your files code-page and press [ F5 ] to run it.
Dear VLETM sir, really you are great. Thank you very much. It's working very well with my own data.
In fact, all the above 3 solutions (post#2,3&7) may work to meet my requirement, but my knowledge is not up to that mark, to understand TEXT join formula as per Bosco sir and Power Query as per post#3 excel wizard sir and LET function according to peter sir as in post#7. Even understanding your macro also far beyond my knowledge, but simply copying code to module and press F5 is looks easy than above.
anyway, thank you very much once again for your kind help.
Sir, Let me know what is the best way and how to learn macro's like you, as I don't have any coding knowledge as I am a normal accounting person.
 

vletm

Excel Ninja
@Gopigk
My sample won't need so much from user to use as You noticed.
'The best way' ... if You know how to do it manually, then You can write the code too - every steps have to write.
'Learn' ... by coding ... coding ... coding ... and ... coding.
If You read my code line-by-line, You should notice some keywords as well as actions which it will do.
As You wrote, if You have 'a-lot-of-row' then You would see progress from Statusbar - then You'll know - freezing or what?
 

p45cal

Well-Known Member
A variant of vletm's code which will handle the case (which may never exist!) where there are different numbers of Job Nos. and Lot Nos. in the same row; none will be missed out:
Code:
Sub Do_It2()
Application.ScreenUpdating = False
With ActiveSheet
  y_max = .Cells(.Rows.Count, "A").End(xlUp).Row
  For y = y_max To 2 Step -1
    If InStr(.Cells(y, "D").Value, ",") > 0 Then
      dd = Split(.Cells(y, "D"), ",")
      ee = Split(.Cells(y, "E"), ",")
      de = Application.Max(UBound(dd), UBound(ee))
      .Range("A" & y & ":G" & y).Copy
      .Range("A" & y + 1 & ":G" & y + de).Insert Shift:=xlDown     
      .Range("D" & y & ":D" & y + de).Value = Application.Transpose(dd)
      .Range("E" & y & ":E" & y + de).Value = Application.Transpose(ee)
    End If
    Application.StatusBar = "Status: " & y & " / " & y_max
  Next y
  With Application
    .ScreenUpdating = True
    .StatusBar = False
    .CutCopyMode = False
  End With
End With
End Sub
The above code will place #N/A in cells where there are insufficient corresponding Job/Lot Nos.

If the user prefers blank cells instead of #N/A then replace these two lines:
Code:
      .Range("D" & y & ":D" & y + de).Value = Application.Transpose(dd)
      .Range("E" & y & ":E" & y + de).Value = Application.Transpose(ee)
with:
Code:
      .Range("D" & y & ":D" & y + de).ClearContents
      .Range("D" & y & ":D" & y + UBound(dd)).Value = Application.Transpose(dd)
      .Range("E" & y & ":E" & y + de).ClearContents
      .Range("E" & y & ":E" & y + UBound(ee)).Value = Application.Transpose(ee)
@vletm , consider putting your code in a standard-code module rather than a sheet's code-module; should the user delete that sheet he won't lose any code.
 

vletm

Excel Ninja
p45cal
Based give sample data, I did a sample solution - 'my way'.
If real data has ... whatever ... as many times has happened, then those should able to modify later.

As user would like to use xlsx-files then
a) it would be safer to put this code to sheet's code-module, then no need to be make extra checking, if user runs this with totally wrong data.
b) user could have original code in file, which user would use and run with each data-file by copy it as I wrote
The real data file do not need to save that code at all.
 

p45cal

Well-Known Member
As user would like to use xlsx-files then
a) it would be safer to put this code to sheet's code-module, then no need to be make extra checking, if user runs this with totally wrong data.
Why safer? What extra checking? It makes no difference whether the code is in a sheet code-module or a standard module.
b) user could have original code in file, which user would use and run with each data-file by copy it as I wrote
The real data file do not need to save that code at all.
Again, I see no difference where the code is. Regardless of where the code is, it can be called and act in the same way.

I was merely ponting out that a user, inexperienced in macros, could easily delete a sheet which has code in its code-module without knowing that there's code in it, but could not lose code like that if that code is in a standard code-module.
 

vletm

Excel Ninja
p45cal
Safer: User should focus to a specific sheet and to run macro with that sheet.
Check: If focus only to one sheet then no need to eg check is that sheet correct sheet - if columns D & E has data which is as in this case.
Something same, which You were worry - if different number of those key cell datas.
If user which do normally do not use macros, then user need to know too - how to create new module.
The code is eg in Test File.xlsb. User will copy it to needed file - run it - save that file as it has been .xlsx-format.
Same procedure with next .xlsx-file. If one day, need to modify code, then that modification could do to ... one file (instead of many files).
If user will delete that sheet - then the data would be deleted too - then - what matter with that code anymore?
The data of sheet will be more important than ... any code.
 

@Gopigk

New Member
Please try Power Query

Select Raw data > ribbon Data> From Table > Advanced editor > Paste below code

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.no", Int64.Type}, {"Inv no", type text}, {"Inv date", type date}, {"Job No.", type text}, {"Lot No.", type text}, {"Amt", Int64.Type}, {"O/st amt", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns( { Text.Split([#"Job No."],","),Text.Split([#"Lot No."],",")},{"Job No.","Lot No."})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Job No.", "Lot No."}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Job No.", "Lot No."}, {"Job No.", "Lot No."}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"S.no", "Inv no", "Inv date", "Job No.", "Lot No.", "Amt", "O/st amt"})
in
    #"Reordered Columns"
Dear Excel Wizard.

Thank you very much, now I tried your Power Query suggestion also by learning some basics from online youtube video's and I got succeeded. Thank you for introducing this feature to me, really it's a powerful tool.

Thanks once again.
 
Top