# 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

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:

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.