Macro to transform data

[unknown1001]

Hi,

I have a data in columns

           Columns:
    L1	L2	L3	L4	L5	L6
    8	12	31	39	43	45
    5	10	11	22	25	27
    18	19	20	26	45	49
    2	11	14	37	40	45
    8	10	15	35	39	49
    24	26	31	35	43	47
    13	20	23	29	38	44
    8	13	15	22	38	47

and i would like transform numbers from L1 to L6 columns to new column like this:

                      Columns:
1  2  3  4  5  6  7  8  9  10  11  12  13  ...until max number 49
                     1             1
            1              1   1

if there is number in first cell in L1 column then add number 1 to row under same number column.

Best regards

[SM_Riga]

Hi there. Do you really need a macro for this task? Or using Calc formulas is acceptable too? And what if the same number is in several columns on the same row? Should it then be 1+1+…?

[unknown1001]

Yes Calc formulas will be perfect too. If there will be more number 8 this should looks like that:

                          Columns:
1  2  3  4  5  6  7  8  9  10  11  12  13  ...until max number 49
                     1             1
            1              1   1
                           
                     1

each cell with number from columns L1 L2 L3 L4 L5 L6 add 1 to new columns under columns number

There will be no more than once each number in row

[SM_Riga]

Thanks for reply. Then the goal could be achieved using conditional IF test in array formula. I will write an extended answer in an hour when I am back to desktop computer.

[SM_Riga]

So here is example with your source data (I hope I understood the way you want to transform your data correctly).

In a Row11 please fill cells with numbers from 1 to 49 or extend to whatever you need. Then in cell A12 enter an array formula:

=IF(MAX(IF(A$11=$A2:$F2,1,""))=0,"",MAX(IF(A$11=$A2:$F2,1,"")))

To enter an array formula just copy it to A12 cell and confirm with Shift + Ctrl + Enter. Then drag it right holding Ctrl key to the last column in list, AW column in your case. This will fill all the cells in a row. Last step is to extend formula to columns. Select cells from A12 to AW12 and drag selection holding Ctrl key as long, as there are rows in your data source, 8 in your case.

More on array formulas - Array Functions - LibreOffice Help

[unknown1001]

when i copy that code to A12 i got Err:501 and Err:509 but i figured out to use =IF(A11=A2;"1";"")

[SM_Riga]

Ok, just replace commas with semicolons to fit your LO settings
=IF(MAX(IF(A$11=$A2:$F2;1;""))=0;"";MAX(IF(A$11=$A2:$F2;1;"")))

[unknown1001]

You are the boss, thank you!