+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 24

Extract results in a row from a formula

  1. #1
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Extract results in a row from a formula

    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.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,304

    Re: Extract results in a row from a formula

    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!

  3. #3
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Re: Extract results in a row from a formula

    lets try ! iam sure we can get it!
    Last edited by louis128; 01-12-2019 at 11:04 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,726

    Re: Extract results in a row from a formula

    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!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    2,004

    Re: Extract results in a row from a formula

    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.
    Attached Files Attached Files
    Last edited by Bo_Ry; 01-12-2019 at 11:07 AM. Reason: be 1 not b1
    Bo

  6. #6
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Re: Extract results in a row from a formula

    dear ali,how can i use vba, to get that formula?

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,304

    Re: Extract results in a row from a 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.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,726

    Re: Extract results in a row from a formula

    You said this:

    I want in K:K a history of the counts of J1
    You can't do this without VBA. Shall I move your thread to the VBA section for you?

  9. #9
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Re: Extract results in a row from a formula

    BO ,thanks! how are you?iam glad i heard from you ,because you are the best!!!
    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.

  10. #10
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Re: Extract results in a row from a formula

    not yetthanks ali

  11. #11
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    2,004

    Re: Extract results in a row from a formula

    Quote Originally Posted by louis128 View Post
    only up to the result on I:I.
    What do you mean? , column I is your Input.

    If you want the result in column K, just put the formula in K2 and copy down.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Re: Extract results in a row from a formula

    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!!

  13. #13
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    279

    Re: Extract results in a row from a formula

    is it possible?Thanks so much,i mean it!

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,726

    Re: Extract results in a row from a formula

    Be patient!

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,304

    Re: Extract results in a row from a formula

    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).

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1