# Posts by Fluff13

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• ## Sorting data in a table in a single column

Another option if you have xl365

=TOCOL(B2:F17,1,1)

Kitap2.xlsx

• ## Formula working but when i record macro its not working

Glad you sorted it & thanks for letting us know.

• ## Formula working but when i record macro its not working

You need to change the name of the variable you have called "c" either that or change the formula to use range references rather than R1C1 notation

• ## Identify weekend that has the max number of occurrences

Glad to help & thanks for the feedback.

• ## Identify weekend that has the max number of occurrences

=LET(w,WORKDAY.INTL(A3-1,SEQUENCE(NETWORKDAYS.INTL(A3,MAX(A3:A100),"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))

This will now work if the first date is a Friday, which it would ignore before.

• ## Identify weekend that has the max number of occurrences

=LET(w,WORKDAY.INTL(A3,SEQUENCE(NETWORKDAYS.INTL(A3,A69,"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))

• ## Less clunky way of listing missing month/day combinations

You're welcome & thanks for the feedback.

• ## Less clunky way of listing missing month/day combinations

=LET(a,TEXT(SEQUENCE(366,,DATE(2020,1,1)),"m/d"),FILTER(a,ISNA(MATCH(a,TEXT(dateN,"m/d"),0))))

• ## Highlight lowest value if unique

Glad to help & thanks for the feedback

• ## Highlight lowest value if unique

Did you try my suggestion?

• ## Range.Cells function with formula

You're welcome & thanks for the feedback.

• ## Range.Cells function with formula

You need to double-up the quotes inside the formula like

Code
``Cells(LastRow + 6, "A").Formula = "=CONCATENATE(ROUND(DAYS(A" & LastRow + 5 & ",TODAY())/7,0),"" Weeks"")"``
• ## Sumif function not working.Returning zero

You're welcome & thanks for the feedback

• ## Sumif function not working.Returning zero

Without the = sign the formula is treating the > as a comparison, so it's looking for anything that is greater than the text "7 Days"

But with the = sign it treats the > as text

• ## Sumif function not working.Returning zero

If it's not working, can you supply a sample workbook

• ## Sumif function not working.Returning zero

Try it like

=SUMIF(Sheet1!B2:B27,"=>7 Days", Sheet1!D2:D27)

• ## repetition numbers in a single cell 3 times

You're formula is returning this array

{"71";"15";"54";"45";"57";"77";"78";"85";"50";"08";"81";"10";"08";"8"}

As you can see the final digit is repeated on it's own. Try

=MAX(FREQUENCY(0+MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),2),0+MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),2)))>=3

• ## Go to another Sheet Macro

You're welcome & thanks for the feedback.