Formula simplification needed.

[Sekars]

Hello Friends

From the inputs of light green cells (C11 by validation, B13 as shown below) the formula in light yellow cell C13 dragging the checklist entries.

Now in Cell C13 have huge formula, is there any way to simpify this formula.

thanks in advance

[FlameRetired]

Try this in C13.

Please Login or Register  to view this content.

[FlameRetired]

Another way ... array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[samba_ravi]

or the below will work

Please Login or Register  to view this content.

[Sekars]

Hello

Thanks for all, No formulas are suit this requirement.

FlameRetired formulas partially working, Samba formula not working.

Thanks again

[samba_ravi]

Hello

Thanks for all, No formulas are suit this requirement.

FlameRetired formulas partially working, Samba formula not working.

Thanks again

will you please attach a sample file where it was not worked

[Sekars]

will you please attach a sample file where it was not worked

Please refer the attached excel file in Post #5.

[samba_ravi]

cell b13 is in text format so that it was not worked for zero
try below one

Please Login or Register  to view this content.

[jason.b75]

Without an clear explaination of purpose, I'm going with

=IF(B13="0",C7,MID(C7,FIND(B13,C7),IFERROR(FIND(B13+1,C7)-FIND(B13,C7)-1,LEN(C7))))

Which gives identical results to the existing megaformula in C13

[samba_ravi]

Without an clear explaination of purpose, I'm going with

=IF(B13="0",C7,MID(C7,FIND(B13,C7),IFERROR(FIND(B13+1,C7)-FIND(B13,C7)-1,LEN(C7))))

Which gives identical results to the existing megaformula in C13

1) if there is more then 9 numbers then how your formula get the correct results
2) if you replaced C7 with vlookup formula, your formula also be looks like mega formula

[jason.b75]

I repeat the first line of my previous post for the benifit of those who appear not capable of reading it the first time.

Without an clear explaination of purpose

[samba_ravi]

I repeat the first line of my previous post for the benifit of those who appear not capable of reading it the first time.

Without an clear explaination of purpose

Then what is the need of mentioning megaformula
Which gives identical results to the existing megaformula in C13

[jason.b75]

Compare results of simple formula to mega formula, tell me where it gives a different result.

I didn't say it was the perfect formula, I said it gives the same results.

Maybe if you learn to read properly.

[Sekars]

Please input in the B13 cell as in the format of `-X', `X-' and `X-Y' where X and Y is integers (whole numbers without decimal places). Example : -5, 7- and 4-8. And after kindly refer the result for every inputs.

[jason.b75]

See if this works for you, I used a copule of helpers in the hidden rows.

In E2 =IFERROR(CHOOSE(MATCH(B13*1,{-8,0,1,9}),ABS(B13-1),9,B13+1,MONTH(B13*1)+1),9)
In E3 =IFERROR(CHOOSE(MATCH(B13*1,{-8,0,1,9}),1,1,B13*1,DAY(B13*1)),--LEFT(B13))
In C13 =TRIM(MID(SUBSTITUTE(SUBSTITUTE(CHAR(10)&C7&CHAR(10),CHAR(10),REPT(" ",LEN(C7)),MAX(E2:E3)),CHAR(10),REPT(" ",LEN(C7)),MIN(E2:E3)),LEN(C7),LEN(C7)))

If you're looking to expand the lists beyond 1-8 then I would suggest looking at a UDF.

[Sekars]

UDF leads to change the .xlsx to .xlsm that i don't want.

Thanks for all of your effort to simply this formula. I know it is very difficult and a challenge.

Again thanks to all.

[FlameRetired]

You're welcome. Thanks for the feedback.