Search numbers ending digits in Excel

asingh said:
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.
Click to expand...


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

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


Working flawlessly.
Problem solved

Thanks everyone for all your efforts. Really appreciate it.
 
Last edited by a moderator:
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:
@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:
gauravH said:
@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.
Click to expand...

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


Spy king said:
I think the original data is in multiples of 5 paise as mentioned in op!
Click to expand...

I guess, I missed that. Nice. Thanks.

:)
 
Last edited by a moderator:
asingh said:
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.

:)
Click to expand...

Could you explain for further use how you set up your sheet?
 
Last edited by a moderator:
asingh said:
Yea, sure. Ask what you are not-clear about. I can tell, of course.
Click to expand...

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
 
Spy king said:
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
Click to expand...


Basically what this does:

=IF(SUMPRODUCT((COUNTIF(C2:$C$115,C2)=1)*1)=0,"",1)
Click to expand...

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)
Click to expand...

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

:)
 
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
 
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:
Back
Top