hi my friends! i have something which i think it should not be so hard to do.
example: in I:I there are only 1 and 2 number ,at J1 there is a formula which counts a certain event( if 12 comes counts 1,if no counts 0),at K:K I need to extract the counting results of J1
thanks for your time and i hope we can give me help!
Last edited by louis128; 01-12-2019 at 10:21 AM.
So you want a history of J1 in column K? Every time J1 changes, add the new result to the end of the list in K?
That is not possible with a formula!
lets try !iam sure we can get it!
Last edited by louis128; 01-12-2019 at 11:04 AM.
Sorry - we can't do the impossible. You will need VBA. Shall I move the thread for you?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Please try at L2 Press Ctrl+Shift+Enter and copy down
=IFERROR(MATCH(0,--(INDEX(I$1:I2,N(IF(1,ROWS(I$1:I2)-ROW(I$1:I1)+1)))<>INDEX(I$1:I2,N(IF(1,ROWS(I$1:I2)-ROW(I$1:I1))))),)-1,INT(ROWS(I$1:I2)/2))
I think K3 should be 1 as the 2 1 2 is 1 pair and a half.
Last edited by Bo_Ry; 01-12-2019 at 11:07 AM. Reason: be 1 not b1
dear ali,how can i use vba, to get that formula?
There is no point in trying something that will not work.
When J1 changes, all formulas that look at J1 will change with it, the old results will be gone!
The only ways to do it would give you a new result in column K when anything is changed anywhere in excel, not just J1.
You said this:
You can't do this without VBA. Shall I move your thread to the VBA section for you?I want in K:K a history of the counts of J1
BO ,thanks! how are you?
we are very close, consider that I:I is from up to 100 and L:L the same, so i dont want the next values at L:L, only up to the result on I:I.
not yet
What do you mean? , column I is your Input.Originally Posted by louis128
If you want the result in column K, just put the formula in K2 and copy down.
Boss, what i don't want is the values at K6 and K7, I just want when the value change at I1 to change the same time at K2 only.Not values below!!
is it possible?Thanks so much,i mean it!
Be patient!
Good luck with this one, Bo! I'm not wasting my time on this one.
Louis, you need to understand that the impossible doesn't suddenly become possible just because it is what you want.
What you want is beyond the capability of excel. As Ali stated, it might be possible with vba, but your questions and explainations are as clear as crude oil! Assuming from your location that your first language is Greek or Turkish, not English, maybe you would be better of asking your questions in your own language (in the 'other languages' section of the forum).
Maybe this
K2 with CSE
=IF(I2="","",IFERROR(MATCH(0,--(INDEX(I$1:I2,N(IF(1,ROWS(I$1:I2)-ROW(I$1:I1)+1)))<>INDEX(I$1:I2,N(IF(1,ROWS(I$1:I2)-ROW(I$1:I1))))),)-1,INT(ROWS(I$1:I2)/2)))
If not, then I have no idea what you describe at all.
iam greek , i try to explain as better as possible
Last edited by louis128; 01-12-2019 at 11:56 AM.
Bo, i love you!!!
dear ,Ali Bo_Ry is the best at excel forum.Thanks so much!
Louis - it's not a competition.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
take care of Bo_ry!
Thank you, Happy to help.
Next time, Please explain what you want and mockup manual expected result.
I barely understand this, I just guess.
I would explain like "If no data in I6, I want K6 to be empty."
igot it! thanks
what I need is a formula in C1 which tracks the values of B1
note :B1 is a formula which counts an 123 ,213,321 event (formula you created!!!)
thank you bo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks