Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

**student93** · 08-14-2014, 05:01 AM

Hello everybody. This is my first time posting on this forum so please be patient with me.

So I managed use infopath to collect data and exported to an excel file. The problem is that in infopath, all fields that are from a repeating table are combined into one cell separated by a semicolon. Therefore I need to find a macro that will be able to do the following:
1. check each cell in a row to find the cell with the most number of semicolons (this will tell the macro to insert this many rows)
2. push back the unexpanded data behind to make room to expand the cells from the first row and convert a list of semicolons into individual rows
3. go to the next unexpanded column and do the same until the end of the database
4. if possible, fill in the gaps in the A column with the corresponding number

I have multiple repeating tables so I was wondering if there is a macro that can check if there are extra semicolon on each cell in each column and add rows accordingly.

I been looking online for a solution that can do this, but I couldn't find it. I'm hoping that there is a universal macro that can accomplish all of the above so not only I can use it for this particular database, but other people who are exporting infopath with repeating tables to excel can also use this macro.

Below is an example with the excel file. Obviously that is not the database I have. (The database I have is far longer and has at least 6 repeating tables)

Any advice or pointers would be wonderful
Thank you so much

After.PNG Before.PNG

**patel45** · 08-14-2014, 06:06 AM

try this code, but it does not match all your requirements

Sub a()
With Sheets(1)
  LR = .Cells(.Rows.Count, "A").End(xlUp).Row
  drow = 2
  On Error Resume Next
  For r = 2 To LR
    arr1 = Split(.Cells(r, 2), ";")
    arr2 = Split(.Cells(r, 3), ";")
    arr3 = Split(.Cells(r, 4), ";")
    arr4 = Split(.Cells(r, 5), ";")
    arr5 = Split(.Cells(r, 6), ";")
    With Sheets(2)
      For a1 = 0 To UBound(arr1)
         .Cells(drow, 1) = r - 1
         .Cells(drow, 2) = LTrim(arr1(a1))
         .Cells(drow, 3) = LTrim(arr2(a1))
         .Cells(drow, 4) = LTrim(arr3(a1))
         .Cells(drow, 5) = LTrim(arr4(a1))
         .Cells(drow, 6) = LTrim(arr5(a1))
         drow = drow + 1
      Next
    End With
  Next
End With
End Sub

**student93** · 08-14-2014, 12:36 PM

Hi, I been trying out your code and just adding extra arrays until it the number of arrays matches the number of columns I have on the real database. However, it seems that your code only checks for the most number of semicolons in like the first five, so if the column with the most number of semicolons are further down, the number of new rows won't be correct and data will be truncated. Do you have any advice on how to remedy that?

also instead of automatically adding the numbers, is it possible to use the names on column A as reference and just copy that name if new rows are created?

Thanks

**patel45** · 08-14-2014, 01:27 PM

Sub a()
With Sheets(1)
  LR = .Cells(.Rows.Count, "A").End(xlUp).Row
  drow = 2
  On Error Resume Next
  For r = 2 To LR
    arr1 = Split(.Cells(r, 2), ";")
    n = UBound(arr1)
    arr2 = Split(.Cells(r, 3), ";")
    If UBound(arr2) > n Then n = UBound(arr2)
    arr3 = Split(.Cells(r, 4), ";")
    If UBound(arr3) > n Then n = UBound(arr3)
    arr4 = Split(.Cells(r, 5), ";")
    If UBound(arr4) > n Then n = UBound(arr4)
    arr5 = Split(.Cells(r, 6), ";")
    If UBound(arr5) > n Then n = UBound(arr5)
    With Sheets(2)
      For a1 = 0 To n
         .Cells(drow, 1) = r - 1
         .Cells(drow, 2) = LTrim(arr1(a1))
         .Cells(drow, 3) = LTrim(arr2(a1))
         .Cells(drow, 4) = LTrim(arr3(a1))
         .Cells(drow, 5) = LTrim(arr4(a1))
         .Cells(drow, 6) = LTrim(arr5(a1))
         drow = drow + 1
      Next
    End With
  Next
End With
End Sub

**student93** · 08-14-2014, 03:34 PM

Wow thanks for such a fast reply! I really appreciate it. I been working on the code you gave me and I think it works great! I just have one more question. For the name column (first column), the name isn't just a number but more or less like a text. Is it possible to repeat the text on extra rows based off of what's on the A column rather than a numbering scheme that begins with 1? The name field will always be on column A if that helps.

Once again, thank you so much. I'm blown away by how fast you're responding to my post.

**patel45** · 08-15-2014, 01:56 AM

i'm sorry but I can not understand your request

**student93** · 08-15-2014, 02:04 AM

You know on the first column (column A) it is numbered numerically from 1 to the end of the list? What happens if on the original data if they are not numbered starting from one? So the column A on my data does not start with 1 but its more like like:

001Text1
003Text2
007Text3

So when the program expands the rows, I would like instead of numbering the blank rows as 1s 2s or 3s, Id like them to number "001Text1", "002Text2", or "007Text3" appropriately.

Is that possible?

**student93** · 08-15-2014, 02:08 AM

Double posted. Sorry

**HaHoBe** · 08-15-2014, 01:36 PM

Hi, student93,

your data is inconsistent: sometimes the string starts with a semicolon, sometimes it ends with one, there´s a blank behind a semicolon. You should keep the data consitent throuhout to make the programming a bit easier otherwise you would need to transform the data prior to running the macro or make sure the code does so for each single cell value.

Regarding your question, change

         .Cells(drow, 1) = r - 1

to

         .Cells(dRow, 1) = Sheets(1).Cells(r, 1).Value

Ciao,
Holger

**student93** · 08-15-2014, 05:10 PM

Wow! Thank you so much, it works perfectly! Yea because it's infopath, I can only save the data from the repeating table into one cell and so I decide to separate all the entries in the repeating table by a semicolon. The reason why there would be multiple semicolons next to each other is when there is no data entered between fields (which often happens). But either way this code does a great job separating them out by using the semicolon as the delimiter.

So once again, thank you so much patel45, and thank you so much HaHoBe for helping me out with this.

Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Re: Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to separate cell values and insert into new rows

[SOLVED] Adding rows automatically in two separate worksheets

Insert rows to separate categories

[SOLVED] How to Separate Text and Add New Rows Automatically

[SOLVED]Separate cell string at the first delimiter (ex. space) - macro

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions