• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help needed on Grouping data based on multiple colums

S

sridhar13reddy

New Member
is it possible to group the rows based on Multiple columns using VBA Code ? i have data from A1 to K1050 ( Data may change) column D as Account, column G as tracer, column H as wirn ( reference ),
column G reference would be generally populated in either G or D or H, now i need to group the transactions based on reference ( column H or Column G or column D)
 

Attachments

  • Book1.xlsx
    10.4 KB · Views: 12
Hi Sridhar ,

I am not able to understand the logic by which the original order of :

3,4,5,6,7,8,9

has now been changed to :

3,5,6,9 and 7,4,8

Can you explain ?

Narayan
 
Hi, sirhar13reddy!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look following solution. It uses a helper column N and a macro.
Formulas:
N2: =SI(ESBLANCO(G2);SI(ESBLANCO(D2);SI(ESBLANCO(H2);"";H2);SI(ESBLANCO(H2);D2;SI(D2<H2;D2;H2)));G2) -----> in english: =IF(ISBLANK(G2),IF(ISBLANK(D2),IF(ISBLANK(H2),"",H2),IF(ISBLANK(H2),D2,IF(D2<H2,D2,H2))),G2)
Macro:
Code: 
Option Explicit

Sub ThereAreStrangeSortingMethodsButThisWins()
    ' constants
    Const ksRange = "A:N"
    Const ksInputWS = "Sheet1"
    Const ksOutputWS = "Sheet2"
    ' declarations
    Dim rngI As Range, rngO As Range
    ' start
    Set rngI = Worksheets(ksInputWS).Range(ksRange).CurrentRegion
    Set rngO = Worksheets(ksOutputWS).Range(ksRange)
    ' process
    With rngO
        .ClearContents
        rngI.Copy .Cells(1, 1)
        .Parent.Activate
        .CurrentRegion.Sort [N2], , [A2], , , [B2], , xlYes
    End With
    ' end
    Set rngO = Nothing
    Set rngI = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

EDITED (SirJB7)

PS: There's no uploadable file, preformatted text wrongly copied. Fixed above.
 
Last edited:
NARAYANK991 said:
Hi Sridhar ,

I am not able to understand the logic by which the original order of :

3,4,5,6,7,8,9

has now been changed to :

3,5,6,9 and 7,4,8

Can you explain ?

Narayan
Click to expand...


Hi Narayan,

the grouping is not based on the rows, it is based on values in columns which are in common, i need to group all the transactions together based on values in columns.
and the data which i showed as
End result which I need
is a manual intervention and i want the solution if this can be run through macro.
 
SirJB7 said:
Hi, sirhar13reddy!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look following solution. It uses a helper column N and a macro.
Formulas:
N2: =SI(ESBLANCO(G2);SI(ESBLANCO(D2);SI(ESBLANCO(H2);"";H2);SI(ESBLANCO(H2);D2;SI(D2<H2;D2;H2)));G2) -----> in english: =IF(ISBLANK(G2),IF(ISBLANK(D2),IF(ISBLANK(H2),"",H2),IF(ISBLANK(H2),D2,IF(D2<H2,D2,H2))),G2)
Macro:
Code: 
Option Explicit

Sub ThereAreStrangeSortingMethodsButThisWins()
    ' constants
    Const ksRange = "A:N"
    Const ksInputWS = "Sheet1"
    Const ksOutputWS = "Sheet2"
    ' declarations
    Dim rngI As Range, rngO As Range
    ' start
    Set rngI = Worksheets(ksInputWS).Range(ksRange).CurrentRegion
    Set rngO = Worksheets(ksOutputWS).Range(ksRange)
    ' process
    With rngO
        .ClearContents
        rngI.Copy .Cells(1, 1)
        .Parent.Activate
        .CurrentRegion.Sort [N2], , [A2], , , [B2], , xlYes
    End With
    ' end
    Set rngO = Nothing
    Set rngI = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

EDITED (SirJB7)

PS: There's no uploadable file, preformatted text wrongly copied. Fixed above.
Click to expand...

Hello SirJB7,

i tried to run the macro this seems it is working however just one more help need on it , is it possible to insert a blank row for every group of transactions. it would be really great if you can fix this up.. thank you so much in advance.
 
Last edited:
Hi, sridhar13reddy!

Yes and no. Yes, it could have a blank row inserted after each group. No, I'm reluctant to spoil that neat and pristine code with a For...Next loop to do the job.

What would be that empty line for?

Regards!
 
The code to sort and move above is replicated by below. This will also not solve the problem of the spare line but is about as 'clean' as you will find.

Code: 
Sub SortClean()
    Sheet1.[a1].CurrentRegion.Offset(1).Sort [N2], 1, [a2], , , [b2]
    Sheet2.[a1].CurrentRegion.Clear
    Sheet1.[a1].CurrentRegion.Copy Sheet2.[a1]
End Sub

Happy to provide a file to prove workings.

Take care

Smallman
 
You must log in or register to reply here.
Back
Top