Search numbers ending digits in Excel

Criminal

Well-Known Member
Veteran
Jun 6, 2013
921
722
126
@shravank30 updated the formula. You need to populate (drag) the "Decimals" column if you add more stuff to column B.
 

Attachments

Last edited by a moderator:
  • Like
Reactions: shravank30

shravank30

Active Member
Adept
Aug 29, 2009
750
21
32
39
See my 2nd attachment. That is the most dynamic solution, without using VBA. It pulls out the decimal, creates a unique list of decimals and then pulls the respective counts. It can be easily expanded beyond 115 rows.

It is working for 115 rows brilliantly.
How do I extend the range to 500 rows ?

Thanks for advising[DOUBLEPOST=1371208880][/DOUBLEPOST]
@shravank30 updated the formula. You need to populate (drag) the "Decimals" column if you add more stuff to column B.

Working flawlessly.
Problem solved

Thanks everyone for all your efforts. Really appreciate it.
 
Last edited by a moderator:
  • Like
Reactions: asingh

asingh

Well-Known Member
Super Mod
Jun 13, 2009
6,513
1,260
303
New Delhi
Mmm. @gauravH 's solution is elegant. Make sure to add up the counts of rows and occurrences to make sure you are catching all decimals. If you have something not in the range of .05, it might not work. Change B2 to "108.11"; and the count will get less. So keep re-checking.Else the count list should be expanded for all values possible, or I can update my sheet for 1000 row depth.

:)
 
Last edited by a moderator:

Criminal

Well-Known Member
Veteran
Jun 6, 2013
921
722
126
@asingh I use arrays for all of my personal excel work but stick to simple formulas if an excel file is supposed to be shared with others.

I counted values in the increment of 0.5 as it was OP's requirement.
 
Last edited by a moderator:
  • Like
Reactions: shravank30

asingh

Well-Known Member
Super Mod
Jun 13, 2009
6,513
1,260
303
New Delhi
@asingh I use arrays for all of my personal excel work but stick to simple formulas if an excel file is supposed to be shared with others.

I counted values in the increment of 0.5 as it was OP's requirement.
Which is cool. At times array functions are necessary, and it is the developers job to make it dynamic as possible or keep the row-depth to qualify for 'all-needs'. I personally never hard-code in values no matter what, but this was a RAD so what you did is superb. As I said, it is an elegant solution...!

Maybe on your spread sheet: You could put in three cells. Count of the rows, count of the decimals found, and the sum of the decimals found. They should always match. That way the user knows if there is a mismatch (the latter of the three).


I think the original data is in multiples of 5 paise as mentioned in op!
I guess, I missed that. Nice. Thanks.

:)
 
Last edited by a moderator:
  • Like
Reactions: shravank30

Spy king

Well-Known Member
Adept
Oct 16, 2005
660
11
82
Mmm. @gauravH 's solution is elegant. Make sure to add up the counts of rows and occurrences to make sure you are catching all decimals. If you have something not in the range of .05, it might not work. Change B2 to "108.11"; and the count will get less. So keep re-checking.Else the count list should be expanded for all values possible, or I can update my sheet for 1000 row depth.

:)
Could you explain for further use how you set up your sheet?
 
Last edited by a moderator:

Spy king

Well-Known Member
Adept
Oct 16, 2005
660
11
82
Yea, sure. Ask what you are not-clear about. I can tell, of course.
Mainly,
1. Checking for unique digits ( SUMPRODUCT((COUNTIF(C2:$C$115,C2)=1)*1)
What exactly does this do, If Countif = 1, then ? I am a little confused with SUMPRODUCT, doesn't it require a range?
2. Extraction of all the unique digits
 
  • Like
Reactions: shravank30

asingh

Well-Known Member
Super Mod
Jun 13, 2009
6,513
1,260
303
New Delhi
Mainly,
1. Checking for unique digits ( SUMPRODUCT((COUNTIF(C2:$C$115,C2)=1)*1)
What exactly does this do, If Countif = 1, then ? I am a little confused with SUMPRODUCT, doesn't it require a range?
2. Extraction of all the unique digits

Basically what this does:

=IF(SUMPRODUCT((COUNTIF(C2:$C$115,C2)=1)*1)=0,"",1)
It will flag the last occurrence of and entity in a group of cells, in a distinct range. If you notice the start cell C2; it is moving and dynamic. So if it encounters a same value in the same range, it will return a zero, but when it hits the last possibility, it will return the 1. This way, I am de-marking...what is unique (with a 1)...as per the last occurrence. No, SUMPRODUCT (SM) does not require a range, just an array. So I get to multiply the resultant (an array) of the COUNTIF using the SM logic to return a 0,1 ...i.e. boolean. That is how the processing works.

The unique extractor is:

INDEX(C$2:C$115,SMALL(IF($D$2:$D$115=1,ROW($C2:$C115)-1,""),ROW($C2:$C115)-1)
You see it 2x in there, cause the first time round it is used to trap any errors, and thereon return clean values. You have to understand how array (CSE) functions work. Basically it is looping through the range, looking for "1", and placing that value in the cell it is placed in. It creates an array matrix within it self, and plugs it on the cell it has been CSE'ed on..! Basically it looks for a 1 (in an array); and then picks up the row value where that 1 was found (in the same array); and using INDEX, extract the equivalent value from the left of the array (but with the same range). This way it cycles through the whole range, and picks up...all the values which were flagged with a "1" using the above. Beautiful...!

:)
 

shravank30

Active Member
Adept
Aug 29, 2009
750
21
32
39
One more query please
I have a figure in Cell A which I keep changing
in Cell B I want the output to be 1% of Cell A or Rs 50, whichever is lower
What should be the command I have to give in Cell B ?

Thanks for advising
 

shravank30

Active Member
Adept
Aug 29, 2009
750
21
32
39
I want to count the total number of cells in 2 different columns having some numbers
What will be the formula ?

I want the result in Cell D80 for the number of cells populated between I88:I91 and T88:T91

Thanks for advising

Got the solution using COUNTA Formula.:happy::happy:
 
Last edited:
  • Like
Reactions: asingh