Can someone help? (urgently needed)

[rainx]

Hi, I have a problem here, I have a list of entries of which has different group names. I need to sort them into various files based on their group name. In a way, doing filter, However, my concern is my group names will vary every month and I also need the files to be save respectively at a place with their group name. Kinda tough, Hope someone would be able to provide some advice. Wondering if it can just filter those that are in the list into different files instead of providing a fixed list to do for all each time. Please Help!

Thanks!
<!-- / message --> <!-- sig --> __________________
Cheers
Rainx

 

[cornflakegirl]

Um, just realised that the second lot of code that Jindon posted did the same thing - feel free to ignore me!

 

[cornflakegirl]

Jindon - I've just tested your code from post #36. It's kind of working - but only seems to be saving the first value for each Fruit/Code combination.

So my test sample

Apple A 1
Apple A 2
Apple B 1
Apple B 2
Apple B 3
Pear C 1
Pear C 2
Pear D 1
Pear E 1

becomes an Apple workbook, with sheet A and sheet B - but sheet A only has one line - Apple A 1 - the Apple A 2 line is nowhere. Similarly for the other sheets - each has only one line.

 

[jindon]

Jindon - I've just tested your code from post #36. It's kind of working - but only seems to be saving the first value for each Fruit/Code combination.

oops
change

                z = .item(w(2,ii))
                ReDim Preserve z(1 To UBound(w,1), 1 To UBound(z,2) + 1)
                For iii = 1 to UBound(w,1) : z(iii, UBound(z,2)) = w(iii,ii) : Next
            End If

to

                z = .item(w(2,ii))
                ReDim Preserve z(1 To UBound(w,1), 1 To UBound(z,2) + 1)
                For iii = 1 to UBound(w,1) : z(iii, UBound(z,2)) = w(iii,ii) : Next
                .item(w(2,ii)) = z
            End If

 

[cornflakegirl]

.item(w(2,ii)) = z

I can't believe I didn't spot that...

Now working perfectly. It is a thing of beauty. When I grow up, I want to write code like you!

 

[jindon]

I can't believe I didn't spot that...

Now working perfectly. It is a thing of beauty. When I grow up, I want to write code like you!

You will soon be able to do since you are really a quick learner...

 

[rainx]

Sub test()
Dim a, i As Long, ii As Long, w(), e, ws As Worksheet
a = Range("a1").CurrentRegion.Resize(,3).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a,1)
        If Not IsEmpty(a(i,1)) Then
            If Not .exists(a(i,1)) Then
                ReDim w(1 To UBound(a,2), 1 To 1)
                For ii = 1 To UBound(a,2) : w(ii, 1) = a(i,ii) : Next
                .add a(i,1), w
            Else
               w = .item(a(i,1))
               ReDim Preserve w(1 To UBound(a,2), 1 To UBound(w,2) + 1)
               For ii = 1 To UBound(a,2) : w(ii, UBound(w,2)) = a(i,ii) : Next
               .item(a(i,1)) = w
           End If
        End If
    Next
    For Each e In .keys
        w = .item(e)
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(e).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        Set ws = Sheets.Add
        ws.Name = e
        ws.Cells(1).Resize(UBound(w,2), UBound(w,1)).Value = _
            Application.Transpose(w)
    Next
End With
End Sub

Hi

Can I know which part should I change if the column I wan to filter is no longer Column 1?

 

[rainx]

Can i ask also can i start doing the split at row 5 because I have header on top the data...

Thanks!

 

[jindon]

Hi

Can I know which part should I change if the column I wan to filter is no longer Column 1?

Can i ask also can i start doing the split at row 5 because I have header on top the data...

Thanks!

It is fully depends on your data structure. show me the screenshot of your data.

 

[rainx]

I know the 1st part alr... for the 2nd part, heres the data

<table x:str="" style="border-collapse: collapse; width: 634pt;" width="845" border="0" cellpadding="0" cellspacing="0"><col style="width: 44pt;" width="58"> <col style="width: 47pt;" width="63"> <col style="width: 84pt;" width="112"> <col style="width: 53pt;" span="3" width="71"> <col style="width: 26pt;" width="35"> <col style="width: 200pt;" width="266"> <col style="width: 74pt;" width="98"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="4" class="xl25" style="height: 12.75pt; width: 228pt;" width="304" height="17">ABC ABC</td> <td style="width: 53pt;" width="71">
</td> <td style="width: 53pt;" width="71">
</td> <td style="width: 26pt;" width="35">
</td> <td style="width: 200pt;" width="266">
</td> <td style="width: 74pt;" width="98">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" class="xl25" style="height: 12.75pt;" height="17">Testing header</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" class="xl25" style="height: 12.75pt;" height="17">As at 1st Jul 2008</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Acct</td> <td>No.</td> <td>Number</td> <td class="xl24">Inv Date</td> <td class="xl24">Due Date</td> <td class="xl24">Inv Amt</td> <td class="xl24">Curr.</td> <td> Text</td> <td>comments</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> <td x:num="" align="right">123</td> </tr> </tbody></table>

 

[rainx]

ar.. sorry i dunno how to post it as image haha.. sorry about it..

Thanks