Ask Your Question
0

Macro to transform data

asked 2018-05-19 14:16:50 +0100

unknown1001 gravatar image

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 flag offensive close merge delete

Comments

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

SM_Riga gravatar imageSM_Riga ( 2018-05-19 16:21:34 +0100 )edit

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

unknown1001 gravatar imageunknown1001 ( 2018-05-19 16:40:45 +0100 )edit

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 gravatar imageSM_Riga ( 2018-05-19 17:14:56 +0100 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2018-05-19 18:58:05 +0100

updated 2018-05-19 19:16:46 +0100

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

image description

edit flag offensive delete link more

Comments

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

unknown1001 gravatar imageunknown1001 ( 2018-05-19 21:07:34 +0100 )edit

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;"")))

SM_Riga gravatar imageSM_Riga ( 2018-05-19 21:39:29 +0100 )edit

You are the boss, thank you!

unknown1001 gravatar imageunknown1001 ( 2018-05-19 21:46:11 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-19 14:16:50 +0100

Seen: 38 times

Last updated: May 19 '18