Need to merge data logically

Closed
Mandy - Aug 16, 2014 at 11:18 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Aug 18, 2014 at 11:40 AM
Hello,

I want Macro for below example,

SUBACC STMTPG OPBALDT OPBALTP CLBALDT CLBALTP
123abc 1 04-07-2014 04-07-2014
123abc 1 05-07-2014 05-07-2014
123abc 1 06-07-2014 06-07-2014
123abc 1 07-07-2014 07-07-2014
3456tyu 1 05-07-2014 05-07-2014
3456tyu 1 05-07-2014 05-07-2014
3456tyu 1 07-07-2014 07-07-2014
3456tyu 1 07-07-2014 07-07-2014
3456tyu 1 07-07-2014 07-07-2014
6785bnym 1 05-07-2014 05-07-2014
897643htr 1 06-07-2014 06-07-2014
897643htr 1 07-07-2014 07-07-2014
897643htr 1 06-07-2014 06-07-2014

First record should be grouped with respect to SUBACC then on basis of OPBALDT with respect to SUBACC, STMTPG should get increased. and OPBALTP and CLBALTP values should get inserted as per STMTPG. Result should be like below,

SUBACC STMTPG OPBALDT OPBALTP CLBALDT CLBALTP
123abc 1 04-07-2014 F 04-07-2014 M
123abc 2 05-07-2014 M 05-07-2014 M
123abc 3 06-07-2014 M 06-07-2014 M
123abc 4 07-07-2014 M 07-07-2014 F
3456tyu 1 05-07-2014 F 05-07-2014 M
3456tyu 1 05-07-2014 M 05-07-2014 F
3456tyu 2 07-07-2014 M 07-07-2014 F
3456tyu 2 07-07-2014 M 07-07-2014 F
3456tyu 2 07-07-2014 M 07-07-2014 F
6785bnym 1 05-07-2014 F 05-07-2014 M
897643htr 1 06-07-2014 F 06-07-2014 M
897643htr 2 07-07-2014 M 07-07-2014 F
897643htr 1 06-07-2014 F 06-07-2014 M

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 18, 2014 at 11:40 AM
Hi Mandy,

I'm missing some logic in the use of the F's and M's.

I thought whenever a new SUBACC is found, the OPBALTP should be "F" and the CLBALTP should be "M".

By this logic the CLBALTP of row 3 and 4 should be "F" and the final sample row the "F" and "M" should be switched.

If I'm correct and you are willing to sort your data first on SUBAAC and second on OPBALDT then the following code will work for you:

Sub RunMe()
Dim lRow As Long

lRow = Range("A1").End(xlDown).Row

Range("D2") = "F"
Range("F2") = "M"

For Each cell In Range("A3:A" & lRow)

    If cell.Value = cell.Offset(-1, 0).Value And _
    cell.Offset(0, 2).Value <> cell.Offset(-1, 2).Value Then
        cell.Offset(0, 1).Value = cell.Offset(-1, 1) + 1
    Else
        cell.Offset(0, 1).Value = cell.Offset(-1, 1)
    End If
    
    cell.Offset(0, 3).Value = "M"
    cell.Offset(0, 5).Value = "F"
    
    If cell.Value <> cell.Offset(-1, 0).Value Then
        cell.Offset(0, 3).Value = "F"
        cell.Offset(0, 5).Value = "M"
        cell.Offset(0, 1).Value = 1
    End If
    
Next cell

End Sub


Best regards,
Trowa
0