Ascending Column Order

  • Hi,
    I have a spreadsheet which is having Column A to B. I want to sort the column A as alphabetical order (So cells which are having same values should come together), so all columns should adjust with that accordingly.


    can we able to achieve this scenario in VBA code? If yes, please provide me the sample code.



    Sample data for the scenario is as below:


    Column A Column B
    KALA $58
    KAZO $123.7
    KA55 $23.5
    CBHY $14.0
    AMJ0 $99
    CBHY $73.0
    AMJ0 $34
    KAZO $13.0
    KA55 $28.5
    CT00 $12
    KALA $76
    KL76 $34


    Regards,
    Sri

  • Re: VBA Code: ascending order of the columns


    Hi Sri,


    This sounds like a standard sort. I'd suggest either doing it yourself manually (Data - Sort), or if you really need a macro, record yourself doing the sort. You could even sort by both name and value, if desired.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA Code: ascending order of the columns


    Okay, I got it.
    Thanks for your suggestion.


    After sort My data (INPUT) is as below, is there anyway I can get my result (OUTPUT) using VBA Macros? If it is feasible please help me with sample code.


    Input:


    Column A Column B


    AMJ0 $34
    AMJ0 $99

    CBHY $14.0
    CBHY $73.0

    CT00 $12

    KALA $58
    KALA $76

    KAZO $123.7
    KAZO $13.0

    KA55 $28.5
    KA55 $23.5

    KL76 $34


    Output:


    AMJ0 $133


    CBHY $87.0


    CT00 $12

    KALA $134

    KAZO $136.7

    KA55 $52.0


    KL76 $34


    Regards,
    Sri

  • Re: VBA Code: ascending order of the columns


    You actually want to build a PivotTable. With your original input selected, go to Insert - PivotTable. Put column A in the Row Field, and column B in the Value field. The PivotTable then can automatically sum your data, and sort it (by either col A or col B).

  • Re: VBA Code: ascending order of the columns


    You could record a macro of:
    Copy all of Col A to new sheet
    Use Data - Remove Duplicates
    Create SUMIF formula new to the condensed list
    Copy formulas and paste as values


    That's how I would do it, if needed to be done via macro.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA Code: ascending order of the columns


    Thanks Mike. Looks like we were on similar paths...


    To OP, once you have a list of names, you can make formula like:
    =SUMIF(NameRange,Name,ValueRange)


    which will summarize your values, as you requested in post #3.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA Code: ascending order of the columns


    Hi,
    Its working fine.



    I just have a question in below scenario:



    I have remove some combination of data (KAX0, KAX1, -,-,-,-,KAX9)from my sheet. Scenario is as follows:



    Input:



    ColumnA ColumnB
    KAA3 5
    KAA5 55
    KAX0 4
    KAX0 3
    KAX0 8
    KAX0 21
    KAX1 2
    KAX1 98
    KAX2 2
    KAX2 67
    KAX2 876
    KAX3 8
    KAX3 8
    KAX3 43
    ---
    ----
    ---
    ---
    KAX9 9
    KAX9 8
    KAX9 11
    KAX9 12
    KA33 45
    KA54 8



    Output:



    KAA3 5
    KAA5 55
    KA33 45
    KA54 8





    Regards,
    Sri

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!