Posts by tuppari

    Dear ozgrid,


    I have spreadsheet with different data areas separated by a row, where I'd need to fill the whole area with different colours, depending on the values of column D (per area). If the values are all exact same, the area should be coloured green, if not, it should be coloured red.


    Please also note, on column E I have marked X's next to lines. This X means the line shouldn' be used in the comparison, but should still be coloured aswell.


    If there is only one row in an area (meaning nothing to compare to, excluding rows with X), the area should be coloured green by default.


    Attached an example spreadsheet. On the Desired outcome -tab I have deliberately bolded the rows to indicate which values were used for the comparison (the macro does not need to do this)


    Example file:
    forum.ozgrid.com/index.php?attachment/49300/

    Here's my problem, I have a very basic line in my macro:
    Range("K2").Select


    But, the macro gives me an "application-defined or object-defined error" if I run the macro from a different workbook, than the active one?


    I tested this, my macro swaps between two workbooks, and different Range.Select lines work if I transfer the whole macro to another workbook.

    Dear Ozgrid,


    I have a workbook with multiple columns with differiating data. I would need to compare the values of Column A to every other column with data on it, and highlight duplicates. (Note, only column A should be used as a comparison value. So A should be compared with B C and D, but B should not be compared with C or D.


    Attached example workbook.


    forum.ozgrid.com/index.php?attachment/48489/


    So this is what my start setup looks like.


    [TABLE="width: 320"]

    [tr]


    [TD="class: xl63, width: 64"]User1[/TD]
    [TD="class: xl63, width: 64"]User2[/TD]
    [TD="class: xl63, width: 64"]User3[/TD]
    [TD="class: xl63, width: 64"]User4[/TD]
    [TD="class: xl63, width: 64"]User5[/TD]

    [/tr]


    [tr]


    [td]

    X1

    [/td]


    [td]

    X1

    [/td]


    [td]

    X5

    [/td]


    [td]

    X4

    [/td]


    [td]

    R2

    [/td]


    [/tr]


    [tr]


    [td]

    Y2

    [/td]


    [td]

    Y2

    [/td]


    [td]

    R2

    [/td]


    [td]

    X3

    [/td]


    [td]

    Y2

    [/td]


    [/tr]


    [tr]


    [td]

    Y3

    [/td]


    [td]

    Y3

    [/td]


    [td]

    Y4

    [/td]


    [td]

    Y1

    [/td]


    [td]

    Y9

    [/td]


    [/tr]


    [tr]


    [td]

    X2

    [/td]


    [td]

    Y4

    [/td]


    [td]

    Y7

    [/td]


    [td]

    Y2

    [/td]


    [td]

    X1

    [/td]


    [/tr]


    [tr]


    [td]

    X3

    [/td]


    [td]

    Y5

    [/td]


    [td]

    Y3

    [/td]


    [td]

    Y7

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Y4

    [/td]


    [td]

    X2

    [/td]


    [td]

    X1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Y5

    [/td]


    [td][/td]


    [td]

    X2

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    X4

    [/td]


    [td][/td]


    [td]

    X4

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    X5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R2

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    And this is what it should look like after running the macro



    [TABLE="width: 320"]

    [tr]


    [TD="class: xl63, width: 64"]User1[/TD]
    [TD="class: xl63, width: 64"]User2[/TD]
    [TD="class: xl63, width: 64"]User3[/TD]
    [TD="class: xl63, width: 64"]User4[/TD]
    [TD="class: xl63, width: 64"]User5[/TD]

    [/tr]


    [tr]


    [td]

    X1

    [/td]


    [td]

    X1

    [/td]


    [td]

    X5

    [/td]


    [td]

    X4

    [/td]


    [td]

    R2

    [/td]


    [/tr]


    [tr]


    [td]

    Y2

    [/td]


    [td]

    Y2

    [/td]


    [td]

    R2

    [/td]


    [td]

    X3

    [/td]


    [td]

    Y2

    [/td]


    [/tr]


    [tr]


    [td]

    Y3

    [/td]


    [td]

    Y3

    [/td]


    [td]

    Y4

    [/td]


    [td]

    Y1

    [/td]


    [td]

    Y9

    [/td]


    [/tr]


    [tr]


    [td]

    X2

    [/td]


    [td]

    Y4

    [/td]


    [td]

    Y7

    [/td]


    [td]

    Y2

    [/td]


    [td]

    X1

    [/td]


    [/tr]


    [tr]


    [td]

    X3

    [/td]


    [td]

    Y5

    [/td]


    [td]

    Y3

    [/td]


    [td]

    Y7

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Y4

    [/td]


    [td]

    X2

    [/td]


    [td]

    X1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Y5

    [/td]


    [td][/td]


    [td]

    X2

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    X4

    [/td]


    [td][/td]


    [td]

    X4

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    X5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R2

    [/td]


    [/tr]


    [/TABLE]



    The purpose of this macro is to find values from columns B - XX, that aren't found in column A.
    As from this example, the values Y7 and Y9 were found.


    In the data workbook we're using, there's around 70 columns of data.

    Re: VBA Macro to separate data


    Quote from royUK;627438

    Attach an example file with some dummy data


    Attached an example.


    Basically I need the Technical names in one row in their own cells, and each attribute listed on multiple rows under the correct column (which is specified by the "TECHNICAL NAME"


    forum.ozgrid.com/index.php?attachment/48395/


    edit: So basically I need to have the technical name only once, but each unique attribute must be listed.

    I have a worksheet with data in the following format;


    [TABLE="width: 500"]

    [tr]


    [td]

    A

    [/td]


    [td]

    Value1

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    Value2

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    Value3

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    Value4

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    Value5

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    Value6

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    Value7

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    Value8

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    Value9

    [/td]


    [/tr]


    [tr]


    [td]

    D

    [/td]


    [td]

    Value10

    [/td]


    [/tr]


    [/TABLE]


    What I'd need is a VBA macro to sort the data into multiple columns with only one topic, with all the values below which were next to the same topic, in a format following;


    [TABLE="width: 500"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [/tr]


    [tr]


    [td]

    Value1

    [/td]


    [td]

    Value4

    [/td]


    [td]

    Value7

    [/td]


    [td]

    Value10

    [/td]


    [/tr]


    [tr]


    [td]

    Value2

    [/td]


    [td]

    Value5

    [/td]


    [td]

    Value8

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Value3

    [/td]


    [td]

    Value6

    [/td]


    [td]

    Value9

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I am quite sure there is a macro already done for this purpose, but I couldn't find one for this need.