LinkBack URL
About LinkBacks
Hi,
I've been struggling in automating to count consecutive same text starting from the latest date going backwards. Attached here is a sample excel file. Does anyone have an idea on how to execute this? Thanks!
Hi,
I've been struggling in automating to count consecutive same text starting from the latest date going backwards. Attached here is a sample excel file. Does anyone have an idea on how to execute this? Thanks!
There's probably a better way to do this, but here's one solution
=12-MAX(IF(C2:K2=LOOKUP(2,1/(C2:K2<>L2),C2:K2),COLUMN(C2:K2)))
Array formula, use Ctrl-Shift-Enter
LOOKUP(2,1/(C2:K2<>L2) find the last occurrence in a row of the value which is NOT equal to L2 the last value in the row.
The rest of the formula finds the maximum column number where that value exists.
The result is subtracted from 12 (Column L is the 12th column in the sheet) to give the final value.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Hi Special-K,
Thank you for your answer.
Typically when you need to compare part of a data set against all other parts of a data set (1 compared to 2, 1 compared to 3, 1 compared to 4, etc) then you need to use an array formula as this is effectively what an array allows for, performing the same calculation on each element of your data set instead of on the data set as a whole. Specifically in your case, to know if a number is consecutive we need to compare it against other values to discover if it is.
So the short answer is, there is unlikely to be a non array style formula capable of what you asked for in this case
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
Thank you Zer0Cool for the clarification. Appreciate it.
Non-CSE alternative:
=12-AGGREGATE(14,6,(C2:K2=LOOKUP(2,1/(C2:K2<>L2),C2:K2))*COLUMN(C2:K2),1)
Another way:
=12-LOOKUP(12,COLUMN(C2:L2)/(C2:L2<>L2))
You could use AGGREGATE but you're using Excel 2007 so AGGREGATE won't work.
I'll have another think...
Thank you Phuocam and 63falcondude. The non-CSE alternatives solved it for me.
Happy to help. Thanks for the rep! I just converted the formula from post #2.
If AGGREGATE worked for you, please update your profile to show the current version of Excel that you have.
Special-K, the aggregate solved it for me. The excel version I used is from Office 365, forgot update it. Anyways, thank you for your time.
63falcondude I've updated it already. Thank you all for the inputs. Really appreciate it. This is solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks