"Un-flatten" rows into multiple rows

[niedawow]

Hello,

In my current project I have a couple of very long sheets with similar data that I need to unflatten.

Example (row "Name" being with multiple records separated by comma):

ID Description Names
4711 Text1 ABC,DEF,GHI
5633 Text2 KLM,OPQ,RST

Needs to be transformed into:

ID Description Names
4711 Text1 ABC
4711 Text1 DEF
4711 Text1 GHI
5633 Text2 KLM
5633 Text2 OPQ
5633 Text2 RST

The new rows need to be inserted prior to converting the next row of the original sheet. Is there a formula I can use to do that? Or could someone be so kind to craft a macro I can run automatically through the sheet?

Thanks in advance.

 

[niedawow]

Hi hiker95,

I work in a very restricted environment, so I can not work with screenshots or use online storage to upload data. Yet, I narrowed down the problem and I can simply explain it.

Let's go back to my original example:

ID Description Names MoreInfo Date
4711 Text1 ABC,DEF,GHI Info1 2014-10-22

Running the macro it should produce:

ID Description Names MoreInfo Date
4711 Text1 ABC Info1 2014-10-22
4711 Text1 DEF Info1 2014-10-22
4711 Text1 GHI Info1 2014-10-22

Yet, it produces:

ID Description Names MoreInfo Date
4711 Text1 ABC Info1 2014-10-22
4711 Text1 DEF Info1 22.10.2014
4711 Text1 GHI Info1 22.10.2014

Notice the difference in the date format? Clicking on a original record field with a date shows "2014-10-22 00:00:00" in the formula bar. I guess the original field is set as a date field. While being copied it looses the format and is being transformed into plain text? How can I keep the old date format through out all records?

Thanks.

 

[hiker95]

niedawow,

OK, we have gone thru 3 macros, and, 3 different datasets.

And, now you want to change the dataset again, where you are displaying text strings for dates.

And, you can not give us screenshots.

And, you can not give us your workbook.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[drakeford]

Hi all!

First post so please don't be too hard on me

I've got a similar problem and I've been trying to use the code here but running into a few deadends.

Basically I've got two sets of data, one with an item id and multiple rows for employee id associated. eg:

p1 eid 1
p1 eid 2
p2 eid 3
p3 eid 4
p3 eid 5


I've also got another set of data which has item id and categorisation and categorisation %:

p1 f1 f1%
p1 f2 f2%
p2 f2 f2%
p2 f3 f3%
p3 f1 f1%

I've currently combined them to make:

p1 f1 f1% eid 1 eid 2
p1 f2 f2% eid 1 eid 2
p2 f2 f2% eid 3
p2 f3 f3% eid 3
p3 f1 f1% eid 4 eid 5

But in the end I'd like to end up with:

p1 f1 f1% eid 1
p1 f2 f2% eid 2
p2 f2 f2% eid 3
p2 f3 f3% eid 3
p3 f1 f1% eid 4
p3 f1 f1% eid 5

max eid associated with an item is 11.

The code I went with was this:

Sub Un_Flatten()
'Application.ScreenUpdating = False


Dim i As Long
Dim j As Long
Dim x As Long
Dim Author As Variant


Set x = 1


'Measure Size of Input Data - data starts on row 3


lastrow = ActiveSheet.Worksheets("data").Cells(Rows.Count, "A").End(xlUp).Row + 3


    'Loop through each row adding to new sheet
    
    For i = 3 To lastrow
    
    'Create array of authors
    
    eid = ActiveSheet.Worksheets("data").Range("D" & i, "N" & i)
    
    'Assign authors to each row and paste in new sheet
    
        For j = 1 To 11
    
            If eid(1, j) <> "" Then
            
                'Copy relevant row
        
                ActiveSheet.Worksheets("data").Range("A" & i, "C" & i).Copy
            
                'Paste relevant row
            
                ActiveSheet.Paste Destination:=Worksheets("Input").Range("A" & x, "C" & x)
            
                x = x + 1
            
                'Insert Author
            
                ActiveSheet.Worksheets("Input").Cells(x, 4).Value = eid(1,j)
            
            End If
        
        Next j
            
    Next i
    
'Application.ScreenUpdating = True
    
End Sub

Which I tried my best to use my matlab coding abilities to put something together and haven't been able to climb the vba mountain.

Any help would be wonderful!