function to isolate duplicate numbers

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.

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

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:

Formula:

Please Login or Register  to view this content.

or try and copy down:

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.