Apr 03 2023 09:27 AM
Hi!
I have a really simple spreadsheet that has X's and empty cells. I know its super simple to count how many times X's appear, however, I want to return results that count each "chunk" of cells.
EG:
For the row below I would want to gave something return telling me 3 x, 6 blank, 1 x, 3 blank, 5 x, 7 blank, 1 x, 4 blank, 3 x.
Thank you!!
Apr 03 2023 11:41 AM - edited Apr 03 2023 12:00 PM
Assuming those entries are in A1:AG1:
=LET(
α,A1:AG1,
β,INDEX(α,1),
γ,β="",
δ,COLUMN(α),
ε,LAMBDA(κ,λ,
LET(ζ,FREQUENCY(IF(α=κ,δ),IF(α<>κ,δ)),FILTER(ζ,ζ<>0,0)&λ)
),
ω,ε("x"," x"),
φ,ε(""," empty"),
TEXTJOIN(", ",,TOCOL(HSTACK(IF(γ,φ,ω),IF(γ,ω,φ)),2))
)
Apr 04 2023 11:37 AM
@JosWoolleyThank you however I am not at all sure what that response is telling me? Also, I am going to need to be able to apply this in other rows, so I am unsure how to modify this.
Apr 04 2023 11:41 AM
Apr 04 2023 11:44 AM
Apr 04 2023 11:47 AM
Apr 04 2023 09:26 PM