Grouping Data

[cybermatrix]

I have a large list I need to merge together and it would take a while to copy and paste. The first column has a style number and the 2nd column is the related styles. Any help would be appreciated.

How I need it to be in the sheet
Style Related Styles
35ALP 55,2400,440W,D100W,D113W,D120W,OB12

The way the sheet is set up at this time

<table border="0" cellpadding="0" cellspacing="0" width="200"><col style="width: 69pt;" width="92"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 69pt;" height="20" width="92">Style</td> <td style="width: 81pt;" width="108">Related Style</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td align="right">55</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td align="right">2400</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td>440W</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td>D100W</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td>D113W</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td>D120W</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">35ALP</td> <td>OB12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td>054Y</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td align="right">2003</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td align="right">4002</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td align="right">437</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td>A4600</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td>G880</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td>J300</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td>29M</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td align="right">5170</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">54ALP</td> <td>G200</td> </tr> </tbody></table>

 

[texasalynn]

take a look at this

 

[cybermatrix]

Thanks and since I have not played with macros a lot. How would I handle putting all the different values in to look for? In my example style 35ALP is just one of about 500 that is in column A.

 

[texasalynn]

try this

Sub CombineStyle()
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
combineWord = Null
    If Len(Cells(i, "A").Value) > 0 Then
        If WorksheetFunction.CountIf(Range("A1", "A" & i), Range("A" & i).Value) = 1 Then
            For j = 1 To Cells(Rows.Count, "B").End(xlUp).Row
                If Cells(j, "A").Value = Cells(i, "A").Value Then
                    combineWord = combineWord & "," & Cells(j, "B").Value
                ElseIf Len(Cells(j, "A")) = 0 And Cells(j, "A").End(xlUp).Value = Cells(i, "A").Value Then
                    combineWord = combineWord & "," & Cells(j, "B").Value
                End If
            Next j
        End If
    End If
Cells(i, "C").Value = combineWord
Next i
End Sub

 

[cybermatrix]

Thanks for the macro and I have used it for several projects. However, would you have any idea as to why all of a sudden it seems to want to have excel not respond and then respond? Plus when I used it in the past it might have taken 30 seconds to run it but now it just does not seem to want to finish.

 

[texasalynn]

try stepping thru the macro and see if it gives you an idea of where it is not work.

 

[cybermatrix]

Not sure if I follow you.

What I'm doing is opening the file. Then going to developer ribbon and clicking visual basic to open it.

Then I'm clicking the double clicking sheet the data is in and then pasting it in there. Then closing it and run the macro. And I have also added it as module.

I can see it starting to work then it does and few and then shows me not responding then it responds. It goes back and forth and never finishes.

 

[cybermatrix]

Could it be because this file I'm trying to work with is larger than the others I have worked with?

Most of the others have been a few hundred to a few thousand rows. This one is about 46K rows.

 

[cybermatrix]

That is what it is, too many rows I broke it down to smaller rows and it worked.