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!
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!
Excel 2016 (Windows) 32 bit
B C D E F G 1DATA 2name1 1 1/1/2019DATA 3name2 1/2/2019name1 =COUNTIF($B$2:$B$16,F3) 4name3 1 1/3/2019name2 =COUNTIF($B$2:$B$16,F4) 5name4 1 1/4/2019name3 =COUNTIF($B$2:$B$16,F5) 6name5 1 1/5/2019name4 =COUNTIF($B$2:$B$16,F6) 7name2 1/6/2019name5 =COUNTIF($B$2:$B$16,F7) 8name3 1/7/2019 9name4 1 1/8/2019 10name5 1/9/2019 11name1 1 1/10/2019 12name2 1/11/2019 13name3 1/12/2019 14name4 1 1/13/2019 15name5 1/14/2019 16name2 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
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.
Maybe like this:
F2Please Login or Register to view this content.
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.
Please try at G2
Formula: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).
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.
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.
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
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
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.
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.
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.
@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.
@Purlo
You have reply by several members.
Please add to whom you replying.
@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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks