Columns needs filling with the data in following row's first and second column

[kanishka271]

Hello,

I have the following data.

and my required output is as below,

The logic behind the output is that, it moves the first column, 2nd and 3rd row data into the 3rd column 1st row in order. and the Column 2 row 3 data into the column 4. it should also come after the existing data in the row 1 column 3 and 4.

Any idea on how to do it.
Attached are the input and output data.
Book1.xlsx (9.0 KB)

[ArjenEX]

Hi @kanishka271

You can use a similar approach as with the other topic that you raised.

Filtering rows but take the above row into consideration as well

Hello I have the following data set. which has “1” or some other string/int or a mix in the column 1. [image] The required output is as below. [image] The logic behind the two datasets is that, I need to filter out the column 1 rows, which does not have “1” in it however, need to keep just the row above the filtered out row which will contain “1” Is there any way I can do this. I have attached the two datasets in the post for the convenience. Thank you Kanishka output.xlsx (3.4 KB) inp…

With the Access features in the CE, you can use column/row “coordinates”. Note that it starts to count from zero so the offset is 1.

As such,

 column("column3") + column(0,1) + " " + column(0,2)

Meaning the original value of column 3 + column 1 row 2 + space + column 1 row 3

column("column4") + column(1,1)

Meaning the original value of column 4 + column 2 row 2

fi you want to keep your data clean, you can use

if (column("column4") != null)
    column("column4") + column(1,1)
else {
    null
}

Adding a filter to just keep the first row.

Output:

Hope this helps!

[kanishka271]

Hello This does the job. thank you very much. However the dataset that I am dealing with now has to go down around 25 rows to fill the column. how do I maximise the offset?

[Daniel_Weikert]

Have a look at the advanced tab in column expression
br

[kanishka271]

I change the multi-row access to 20 and it still says, maximum offset is 10