+ Reply to Thread
Results 1 to 16 of 16

Consolidate information and return a value with max results in the row

  1. #1
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Consolidate information and return a value with max results in the row

    Hello.
    I would like to make a formula, so that it would return value that repeated itself two or more times in a row on the table. It should also show how many times value was repeated.
    Thanks a lot!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Consolidate information and return a value with max results in the row

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    G
    1
    DATA
    2
    name1
    1
    1/1/2019
    DATA
    3
    name2
    1/2/2019
    name1
    =COUNTIF($B$2:$B$16,F3)
    4
    name3
    1
    1/3/2019
    name2
    =COUNTIF($B$2:$B$16,F4)
    5
    name4
    1
    1/4/2019
    name3
    =COUNTIF($B$2:$B$16,F5)
    6
    name5
    1
    1/5/2019
    name4
    =COUNTIF($B$2:$B$16,F6)
    7
    name2
    1/6/2019
    name5
    =COUNTIF($B$2:$B$16,F7)
    8
    name3
    1/7/2019
    9
    name4
    1
    1/8/2019
    10
    name5
    1/9/2019
    11
    name1
    1
    1/10/2019
    12
    name2
    1/11/2019
    13
    name3
    1/12/2019
    14
    name4
    1
    1/13/2019
    15
    name5
    1/14/2019
    16
    name2
    1
    1/15/2019
    Sheet: Sheet1
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Consolidate information and return a value with max results in the row

    It should count only if there is value in C:C, but also important condition that it should count only values in a row, so for example:
    name3 - 0
    name3 - 1
    name3 - 0
    It should return 1 only.

    It should also work backwards from the future, and show the latest results:
    name3 - 1 - 2019-01-01
    name3 - 1 - 2019-01-02
    name3 - 0 - 2019-01-03
    name3 - 1 - 2020-01-04
    It should return only 1
    name3 - 1 - 2019-01-01
    name3 - 1 - 2019-01-02
    name3 - 0 - 2019-01-03
    name3 - 1 - 2020-01-04
    name3 - 0 - 2020-02-04
    It should return 0
    So basically: formula should count how many times in a row value appears on specific name, and also return only latest result.
    Last edited by purlo; 05-29-2020 at 09:05 AM.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Consolidate information and return a value with max results in the row

    Maybe like this:

    F2
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Consolidate information and return a value with max results in the row

    This is better, anyways it should "reset" counting if it finds empty cell.
    PS: my data has empty cell but these cells are not blank.
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Consolidate information and return a value with max results in the row

    Please try at G2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Consolidate information and return a value with max results in the row

    Thanks but it still doesn't reset, if it finds blank cell. Please refer to my last attachment.
    Also it is not necessary to put all in one cell, value is enough. Sorry for my mistake.

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Consolidate information and return a value with max results in the row

    You didnt explain this is how you want it in your 1st post.

    You said:
    "I would like to make a formula, so that it would return value that repeated itself two or more times in a row". Please state your requirements plainly.

  9. #9
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Consolidate information and return a value with max results in the row

    Yes, so the point is that it would count only value that are in a ROW. If value is missing it should reset. Sorry for not explaining clearly

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Consolidate information and return a value with max results in the row

    Please try at F2

    =1/LOOKUP(2,1/FREQUENCY(IF(($B$2:$B$16=E2)*($C$2:$C$16),ROW($C$2:$C$16)),IF(($B$2:$B$16=E2)*($C$2:$C$16=0),ROW($C$2:$C$16))))

    Ctrl+Shift+Enter
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Consolidate information and return a value with max results in the row

    Thank you, this formula works very well, it is what I need. I just noticed one thing: since it doesn’t reach to empty cells it shows past results in a ROW even if most recent results were empty. My goal is to see most RECENT results if they were in a ROW and how many time.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Consolidate information and return a value with max results in the row

    Maybe like this.

    A2 =IF(Countif($B$2:$B$16,$B2)=Countif($B$2:$B2,$B2),1,"")

    Edit

    See the attached file.
    Last edited by oeldere; 06-01-2020 at 03:26 AM.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  13. #13
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Consolidate information and return a value with max results in the row

    Base on your Sample
    F2
    =IFERROR(1/LOOKUP(99,FREQUENCY(IF(($B$2:$B$16=E2)*($C$2:$C$16),ROW($C$2:$C$16)),IF(($B$2:$B$16=E2)*($C$2:$C$16=0),ROW($C$2:$C$16)))),"")

    Name2 Should be empty because most recent result is empty in C16
    Then
    Name 3 and Name 5 should be empty as C13 and C15 are empty.

  14. #14
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Consolidate information and return a value with max results in the row

    @Bo_Ry
    It works, but for some reason it does not return "full" numbers if value appears more than 1 time in a ROW.
    Thanks for your help!
    Last edited by purlo; 06-01-2020 at 04:23 AM.

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Consolidate information and return a value with max results in the row

    @Purlo

    You have reply by several members.

    Please add to whom you replying.

  16. #16
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Consolidate information and return a value with max results in the row

    @Purlo
    name 3 also latest entry has blank then why E4 (Name3) is 1
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Consolidate information from 1 worksheet to another
    By truchamp in forum Excel General
    Replies: 1
    Last Post: 12-21-2018, 12:25 AM
  2. [SOLVED] How do I return specific results based on information in other cells.
    By bradfordahill in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-01-2018, 06:00 PM
  3. [SOLVED] formula to consolidate results
    By Rzcrewgarage in forum Excel General
    Replies: 7
    Last Post: 11-11-2016, 02:39 PM
  4. Consolidate Row Information
    By Rompetelo in forum Excel General
    Replies: 1
    Last Post: 09-06-2015, 07:17 PM
  5. Function to consolidate results
    By managingcrap in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2015, 02:24 PM
  6. Consolidate information in each row of database
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-01-2011, 10:41 AM
  7. Consolidate information / Summing
    By Oggie Ben Doggie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-14-2006, 01:20 PM

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