[FIXED] How to flip a range horizontally?

Issue

I have this google sheets input table
How to flip the range A2:E range horizontally with Arrayformula because the range A1:E is growing.

Input                   Output              
c   a   t                       t   a   c
b   i   r   d               d   r   i   b
h   o   r   s   e       e   s   r   o   h
t   i   g   e   r       r   e   g   i   t

Solution

The old way

=TRANSPOSE(SORT(TRANSPOSE(A2:E),SEQUENCE(ROWS(
                TRANSPOSE(A2:E))),0))

The new way

=LAMBDA(range,
 TRANSPOSE(SORT(range,SEQUENCE(ROWS(range)),0)))(TRANSPOSE(A2:E))

Used formulas help
TRANSPOSE - SORT - SEQUENCE - ROWS - LAMBDA

Answered By - Osm

Answer Checked By - Terry (PHPFixing Volunteer)

Read More

[FIXED] How to Flip a string in google sheets

Issue

I have this google sheets input column on the left, I want to flip the string like shown in the output column.

Input   Output
--------------
bats    stab
live    evil
meet    teem
part    trap
stop    pots

Solution

Split the string to it's characters using regex delimiter, then REDUCE the string using current&accumulator(reversal happens here):

=REDUCE(,SPLIT(REGEXREPLACE(A2,,"🐾"),"🐾"),LAMBDA(a,c,c&a))

For a array, use BYROW:

=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(str,REDUCE(,SPLIT(REGEXREPLACE(str,,"🐾"),"🐾"),LAMBDA(a,c,c&a))))

Answered By - TheMaster

Answer Checked By - Timothy Miller (PHPFixing Admin)

Read More

[FIXED] Why is it faster in Google Sheets to split this formula into two steps?

Issue

Edit: I initially diagnosed this problem totally wrong, so the question is entirely rewritten to reflect new understanding.

The problem can be reproduced using a Google spreadsheet with one sheet that contains one header row and a significant number of additional rows (let’s say 5,000).

I wanted column A to increment by 1, starting with A2, as long as the adjacent cell in B was not blank. I used this formula in A1:

={"SKU"; arrayformula(if($B2:$B="","",text(row($A2:$A),"000000")))}

This formula worked but caused extremely significant lag.

In one of my attempts to resolve the issue, I added a helper column before column A and split my formula into two formulas to see which function was causing the lag:

Cell A1: ={"SKU (helper)"; arrayformula(if($C2:$C="","",row($A2:$A)))}

Cell B1: ={"SKU"; arrayformula(if($C2:$C="","",text($A2:$A,"000000")))}

To my surprise, the answer was neither. The lag was completely eliminated. What is the reason? And is it possible to eliminate the lag without the need for a helper column?

Solution

use:

={"SKU"; SEQUENCE(ROWS(A:A)-5344; 1; 5344)}

---

update:

={"SKU"; INDEX(TEXT(SEQUENCE(COUNTA(B2:B)), "000000"))}

if you have empty cells in between use:

=LAMBDA(x, {"SKU"; INDEX(IF(x="",,
 TEXT(COUNTIFS(x, "<>", ROW(x), "<="&ROW(x)), "000000")))})
 (B2:INDEX(B:B, MAX((B:B<>"")*ROW(B:B))))

Answered By - player0

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How to count sequential repeats of a value (allowing for one skip)

Issue

I am trying to produce a variable that counts how many times a "1" appeared sequentially in the preceding rows for a different variable. However, I need the count to persist even if there is one row missing a 1. (i.e., 10111011 should register as an 8). The code I use to count sequential 1s is:

The following code provides an example of the kind of thing I'm trying to do:

input <- c(1,0,1,1,0,1,1,0,1,0,1)
dfseq <- data.frame(input)
dfseq$seq <- sequence(rle(as.character(dfseq$input))$lengths)

which produces the following dataframe:

data_struc <-
  structure(list(
    input = c(1, 0, 1, 1, 0, 1, 1, 0, 1, 0, 1),
    seq = c(1L,
            1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 1L)
  ),
  row.names = c(NA,-11L),
  class = "data.frame")

However, I want the sequence to allow for one row of "failure" on the sequence, such that it continues to count consecutive ones even if one row contains a 0 and then the 1s continue. It should only stop counting once two 0s appear consecutively

Solution

I'd use a lagged variable with an OR condition:

library(dplyr)
dfseq %>% mutate(
  cum_result = cumsum(input == 1 | (lag(input) == 1 & lead(input, default = 1) == 1))
)
#    input seq cum_result
# 1      1   1          1
# 2      0   1          2
# 3      1   1          3
# 4      1   2          4
# 5      0   1          5
# 6      1   1          6
# 7      1   2          7
# 8      0   1          8
# 9      1   1          9
# 10     0   1         10
# 11     1   1         11

Answered By - Gregor Thomas

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More