Need to count contents of cells to produce a knitting pattern-CodePudding

So this needs a bit of detail:

n,X,X,X,n is in cells B5 to F5

I need to get the following output:

1n,3x,1n

for this particular row.

Now the n's and X's represent stitches in knitting with the "n" being the background color and the "x" being the front color.

There is an array of cells B5:F12 representing the rows and stitches, so each row will have a different arrangement of stitches or background color.

I need to avoid vba as this needs to be as stable as possible with the user being my Mum who is 90 years old :) and all she needs is a place to enter the name and the layout (which I have done) and a pattern list for each row (also sorted).

I have started to consider things like:

if(B5=C5,1&B5,"")

But given the n umber of combinations that becomes very long.

Any ideas? Cheers.

CodePudding user response：

You could try:

Formula in H5:

=BYROW(B5:F12,LAMBDA(x,LET(z,REDUCE(VSTACK(TAKE(x,,1),1),DROP(x,,1),LAMBDA(a,b,IF(b=@TAKE(a,,-1),IF(COLUMNS(a)=1,VSTACK(b,TAKE(a,-1) 1),HSTACK(DROP(a,,-1),VSTACK(b,DROP(TAKE(a,,-1),1) 1))),HSTACK(a,VSTACK(b,1))))),TEXTJOIN(",",,DROP(z,1)&TAKE(z,1)))))

I'll see if I can golf the bytecount down a bit...

EDIT:

After a considerable amount of golfing (came down to 119), I came up with:

=BYROW(B5:F12,LAMBDA(x,MID(REDUCE("",x,LAMBDA(a,b,IF(RIGHT(a)=b,LEFT(a,LEN(a)-2)&1 LEFT(RIGHT(a,2)),a&",1")&b)),2,99)))

Though less dynamic than the 1st one, but possible due to the fact there are only <10 columns for each knitting pattern.

CodePudding user response：

If your mother doesn't have the latest Excel (with LAMBDA etc), here is an alternative to @JvdV's answer which only uses LET,SEQUENCE and FILTER.

It only accepts a single row, so you'd need to fill the formula down.

=LET(p,LOWER(B5:F5),c,COLUMNS(p),s,SEQUENCE(,c),
     a,IF(s=c,c,IF(INDEX(p,,s)<>INDEX(p,s 1),s,0)),
     b,FILTER(a,a>0),t,SEQUENCE(,COLUMNS(b)),
     n,IF(t=1,INDEX(b,,t),INDEX(b,,t)-INDEX(b,,t-1)),
     TEXTJOIN(",",TRUE,n & INDEX(p,,b)))

I might add that it allows for adding more than one colour into the pattern ...

and with a bit of conditional formatting, the good lady can design her own multicolour patterns!

CodePudding user response：

This is just a start of a solution, but in cell "B6" you can put the formula:

=(IF(B5=A5,A6 1,1))

This will generate following list:

   B    C    D    E    F

5: n    x    x    x    n
6: 1    1    2    3    1

From there, you can try to get the Subtotals feature to work, based on the Max formula, ... (as I said, this is just a start).