Sort, match and keep in line is possible? URGENT!

[jamesavilalfa]

Ok, here I go, I'm on a deadline, I have to sort and rearrange a list of up to 20K rows and this is going to kill me! (So my boss!)

Here is the case, lets say I have colums A to C with an ID, Name and Value number that I will call "Group 1". Then I have colums D to F with ID, Name and Value again and I will call them "Group 2". Eventualy data from Group 1 is equal to the data on the adjacent rows on Group 2 but many others are different.

My task is to delete the rows that doesn't have a exact match on the adjacent group and get an equal number of cells on all colums, by deleting only those who doesn't have a perfect match.

This is just a practical example but on Group 1 I have some other colums with extra data that cant be found on Group 2 and I must keep. Aditionaly, the resulting sum on colums C should be the same of the sum of colum F too.

this is quite a challenge for me and I think for most out there, but this is my daily job!

THANKS!

 

[jindon]

If you can show us some sample data with your desired result, we might help you.

 

[jamesavilalfa]

Here is the sample:

Initial situation is the first colored area, then, the second shows higlited the extra data to erase to make a match between group 1 and group 2- Finally, the trhird colored area shows the desired result. Thanks!

 

[jindon]

Not sure if this is what you wanted...

Sub test()
Dim myCol As Collection, b(), i As Long, z As String
Set myCol = New Collection
a = Range("g2", Range("g" & Rows.Count).End(xlUp)).Resize(, 3).Value
    For i = 1 To UBound(a, 1)
        z = a(i, 1) & ";" & a(i, 2) & ";" & a(i, 3)
        myCol.Add z, z
    Next
    a = Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 5).Value
    ReDim b(1 To UBound(a, 1), 1 To 5)
    For i = 1 To UBound(a, 1)
        z = a(i, 1) & ";" & a(i, 2) & ";" & a(i, 3)
        On Error Resume Next
        Err.Clear
        myCol.Add z, z
        If Err <> 0 Then
            n = n + 1
            For ii = 1 To 5: b(n, ii) = a(i, ii): Next
        Else
            myCol.Remove myCol.Count
        End If
    Next
Range("a2").Resize(UBound(b, 1), 5).Value = b
End Sub

 

[jamesavilalfa]

Woo! I'm not such an advanced user, so editing Scripts is not relly my strenght, and I really don`t know how to use this script just yet, so figuring out how to make it work could take some valuable time on my deadline (which is today!).

I wish there could be an easier way to go around this.

Thanks a lot!

 

[jindon]

Well, I'm not a Mac user.
Windows, you can

1) hit Alt + F11 to open vbe
2) Go To [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again to get back to Excle
4) hit Alt + F8, select "test" then click on [Run]

Make sure you have back up file...

 

[Kaps_mr2]

Ok another approach.

Make Column J a concatenation of column' A, B and C :-

J1= A1&B1&C1

and then make column K a concanation of columns G, H and I :-

K1 =G1&H1&I1

and then column L :-

L1 IF(ISNA(MATCH(J1,K:K,0),1,0)

and then copy down for cols J1, K1, and L1.

This will give a 1 in column L for all those items in Group 1 and not in Group C.

Sort by column L. This should give you all unwanted items at the bootom.

Remember to do this on a COPY of your workbook !!! Thanks

Kaps

 

[jamesavilalfa]

Great! I'm going to try those clues!

I'll be back and tell you if it worked... BTW, I use Windows too, no worries!

 

[jamesavilalfa]

I'm back... deadline was over and I ulmost got it there, but my boss gave me one more day!

Concatenating solved a good part of the problem, but still I'm not able to get what I need, think about sheets of 127k rows! hu?

Thanks for your support, I really need this, so some more clues would be really nice!