# Macro to transform data

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

edit retag close merge delete

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+...?

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

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.

Sort by » oldest newest most voted 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 - https://help.libreoffice.org/Calc/Arr... more

when i copy that code to A12 i got Err:501 and Err:509 but i figured out to use =IF(A11=A2;"1";"")
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;"")))