Hi Experts,
in a given cell consisting 0 to 7 digits has mix numbers e,g,
957, 1315, 1357399, 13579 are given numbers ; function to give result for these, blank, 1,39, blank
I am now using office365
thanks
karnik
Hi Experts,
in a given cell consisting 0 to 7 digits has mix numbers e,g,
957, 1315, 1357399, 13579 are given numbers ; function to give result for these, blank, 1,39, blank
I am now using office365
thanks
karnik
Please update your forum profile NOW.
Explain the logic here - I do not see any correlation.
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
thanks for suggestion, file is uploaded
karnik
I have already told the OP to do so - no help to be offered until this is done.
Once the profile has been updated, please explain the logic of this (which I asked you to do earlier). There are no clues here to your thinking at all.
AliGW on MS365 Beta Channel (Windows 11) 64 bit
A B 1 given results 2 975 3 1135 11 4 1351 11 5 13759 6 553979 59 7 1113799 19
Sheet: Sheet1
Last edited by AliGW; 03-29-2024 at 02:25 AM.
You PMd me to say that your profile has been updated, but it has not - it still shows Excel 2019. Please update it to 365 and then we can proceedd with the thread
I understand what you want and have a solution for it.
Please update your MS-off version in your profile to Office 365.
Then I am allowed to post the solution.
... .
Here are 2 different solutions:
Please try and copy down:or try and copy down:Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
great couple of formulas Hans!!
I allow me to propose this alternative solution:
I hope it results useful.Please Login or Register to view this content.
Isolate duplicate numbers (solution).xlsx
This single formula will produce results for the entire specified range (change as needed)...
Formula:Please Login or Register to view this content.
@RaulSerg,
Sure, let me give it a try...
First, the problem was to list all the digits within a number that repeat within that number. So, if the number were 528593312, then only the digits 2, 3 and 5 repeat (the other digits appear only once each) and so the returned value should be 235. So, to attack this problem, I use this to count the number of times a given digit appeared with the individual number to see if that number of times was greater than one or not...
LEN(r)-LEN(SUBSTITUTE(r,x,""))>1
Next comes the tricky part of my formula and probably what threw you in understanding it. I used the number of times the given digit repeated as the second argument in the LEFT function call. If the number of times the digit repeated was zero or one, then the above logical expression would return FALSE, which the LEFT function's second argument equates to zero, and so the LEFT function would return zero characters from the first argument. If the number of times the digit repeated was greater than one, then the logical expression would return TRUE which the LEFT function's second argument equates to 1 and, since the first argument is a single digit, that digit is returned. The REDUCE function feeds the individual digits from 0 to 9 (via the SEQUENCE function) into the above described calculation (via the LAMBDA function) which are then concatenated together by the ampersand character. Remember, either a digit that appears more than once or an empty text string ("") is being concatenated thus producing the requested outcome. Finally, each cell in A2:A7 is being treated, one at a time inside the REDUCE function call via the variable named 'r'. The reason this all works for the entire range is because the LEFT, LEN and SUBSTITUTE combination of functions work correctly with dynamic arrays, hence, the whole range gets processed cell by cell.
Last edited by Rick Rothstein; 04-03-2024 at 03:05 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks