Generate array from multiple rows

[augr]

Hi,

I have a large file with ~800K rows and I'm trying to generate an array from Column C from the names in Column B or ID in Column A (grouping Column A or B into 1 row and creating an array from Column C in the same row).

Please see attached sample data and output as I'm having a hard time explaining.

Thanks for your help!

[jindon]

See if this works

Please Login or Register  to view this content.

[Marc L]

Hi !

According to your attachment try this demonstration !

Sub Demo1()
          Dim V, R&, L&
    With Worksheets("data").UsedRange.Rows
        ReDim V(1 To .Count - 1, 1 To 3)
        For R = 2 To .Count
            If .Cells(R, 1).Value = .Cells(R - 1, 1).Value Then
                          V(L, 3) = V(L, 3) & "," & .Cells(R, 3).Value
            Else
                If L Then V(L, 3) = V(L, 3) & "}"
                                L = L + 1
                          V(L, 1) = .Cells(R, 1).Value
                          V(L, 2) = .Cells(R, 2).Value
                          V(L, 3) = "{" & .Cells(R, 3).Value
            End If
        Next
                If L Then V(L, 3) = V(L, 3) & "}"
    End With
         Application.ScreenUpdating = False
    With Worksheets("output")
        .UsedRange.Offset(1).Clear
        .[A2:C2].Resize(L).Value = V
    End With
         Application.ScreenUpdating = True
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[YasserKhalil]

Try this

Please Login or Register  to view this content.

[augr]

Thank you everyone all of these work perfectly.

I should have thought this through much better as I have changed the scope of this.

I would like the curly brackets into quotes.

And I also need an equal number of comma separators even of there is no data present. The delimiter will need to be there even if it's blank. And no spaces before or after the comma delimiter.

I will import this into SQL via csv and the script reads all fields equally so if the max is 4 data points and one of the rows only has 2 data points, I will need 4 comma delimiters anyway.

Please see attached sample data and output.

Thank you so so much!!!

[jindon]

Please Login or Register  to view this content.

[augr]

Thank you so much jindon this works perfectly!

[augr]

Is there any way to optimize this? Excel keeps crashing and I can't process even just 50,000 rows.

By the way I'm using Excel 2016

[jindon]

Please Login or Register  to view this content.

[augr]

Perfect jindon! I tried it with 700K rows and it works flawlessly.

Thank you!

[augr]

Hi jindon I hate to keep doing this to you but I found another issue I didn't notice before. I can do this with a new request if it's too difficult revising your perfectly working script.

I have duplicate data in Column C and I need to remove it and keep only 1 instance.

So it would work exactly as you've written it but it would take into consideration duplicates within the group.

Please see attached for example of the duplication. It is based on Column A or Column B grouping and you'd remove Column C duplicates (keeping only 1 instance) within the group.

Thank you so so much!

[jindon]

Ah, I see

Please Login or Register  to view this content.

[augr]

Thank you jindon it works great but I'm having the crashing issue again. Is there any way to optimize it for a large data set?

[jindon]

What do you mean by "crush"?

I've just tested the code with about 800000 rows and took about 15 sec, but no "crush".

[augr]

thank you so much jindon for working on this.

I'm not sure what I am doing wrong but excel keeps crashing now with the new script.

I'll PM you with the file I am working with (I don't want it to be pubic). If you have a minute, please run the script on the file so you can see what I mean.

Thank you very very much I truly appreciate your time.

[jindon]

You have ERROR value.

Please Login or Register  to view this content.

[augr]

YAY it works now

Thank you, thank you, thank you!!!

[augr]

Hello,

Is it possible to revise this so it does not take into consideration the equal number of delimiters?

It would only return the delimiters that are present.

So the output would look like this:

One,Two
One,Two,Three
One,Two,Three,Four
One,Two,Three,Four,Five

NOT like this which is what it currently does:

One,Two,,,
One,Two,Three,,
One,Two,Three,Four,
One,Two,Three,Four,Five

Thank you!

[jindon]

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[augr]

You're a god. Works perfect. Thank you!