Extract numbers from a column with the same repeated digits and at the same position as the reference number

[cestudante]

Hello, I'm new here, I have a problem with a table in excel and I need your help please!
On column A of such table, all numbers have 2 repeated digits and, for each of one of these numbers, I need to retrieve from column H all numbers that have the same repeated digits at the same position as the reference number in column A and include them in column B, separated by commas. Some examples:. if I have the number 022 on column A, i need to retrieve in column H the numbers 022, 122, 222, 322, 422,.... etc. If I have in column A the number 505, I need to retrieved the numbers 505, 515, 525, 535, 545,.... etc. If I have in column A the number 007, i need to retrieved 001, 002, 003,...007, 008, 009. If I have the number 9511 in column A i need to look for all numbers with 4 digits ending with 11.
I'm afraid it is a bit confused , i do apologize but it is difficult to explain I don't know VBA and I tried already several formulas with no success. Can you please, please, help me? Thank you so much in advance

 

[Micron]

Chiming in mainly to keep abreast of any answers you get. I continue to be amazed at formulas that people come up with to solve problems. I've said before that I can't imagine an issue being solved with a formula only to be proven wrong. I cannot imagine it being possible this time because your repeated values are dynamic, and worse, there's no guarantee they would be in the same location and worse still, the number of digits seems to range from 3 to who knows what. Thus I can only envision a code solution (again).

However, I do wonder about one aspect that I think you need to address in order to zero in on a good solution, and that is, what if the number is not like 9511 but is like 9911? Then it is 2 pairs (99 and 11) or it is one block of 4? Or something else?

 

[cestudante]

Chiming in mainly to keep abreast of any answers you get. I continue to be amazed at formulas that people come up with to solve problems. I've said before that I can't imagine an issue being solved with a formula only to be proven wrong. I cannot imagine it being possible this time because your repeated values are dynamic, and worse, there's no guarantee they would be in the same location and worse still, the number of digits seems to range from 3 to who knows what. Thus I can only envision a code solution (again).

However, I do wonder about one aspect that I think you need to address in order to zero in on a good solution, and that is, what if the number is not like 9511 but is like 9911? Then it is 2 pairs (99 and 11) or it is one block of 4? Or something else?

Numbers will be kept at the same location and I only have numbers with 3 and 4 digits, being 2 equals. Those numbers are flights, there will never be a number with 2 pairs

 

[felixstraube]

Let's say you have your data like you said, search strings in A, and data in H:

Try this formula in J and copy down (adjust range $H$1:$H$1000 as needed):

=LET(
t, $A1,
d, $H$1:$H$1000,
l, LEN(t),
n, MID(t,SEQUENCE(,l),1),
p, BYCOL(n, LAMBDA(x, SUM((n=x)*1)-1)),
searchStr, CONCAT(IF(p, n, "?")),
returnArray, FILTER(d, ISNUMBER(SEARCH(searchStr,d))*(LEN(d)=l)),
TRANSPOSE(returnArray)
)

Not sure what you wanna do with the results so I just transposed them into columns. If you need something else just tell me.

 

[felixstraube]

sorry no XL2BB at the moment

 

[felixstraube]

If you need them separated by commas in column B like this:

try this formula in B1 and copy down:

=LET(
t, $A1,
d, $H$1:$H$1000,
l, LEN(t),
n, MID(t,SEQUENCE(,l),1),
p, BYCOL(n, LAMBDA(x, SUM((n=x)*1)-1)),
searchStr, CONCAT(IF(p, n, "?")),
returnArray, FILTER(d, ISNUMBER(SEARCH(searchStr,d))*(LEN(d)=l)),
IF(SUM(p),TEXTJOIN(",",,returnArray),"")
)

I had to correct the formula.

 

[felixstraube]

or better this one:

=LET(
t, $A1,
d, $H$1:$H$1000,
l, LEN(t),
n, MID(t,SEQUENCE(,l),1),
p, BYCOL(n, LAMBDA(x, SUM((n=x)*1)-1)),
searchStr, CONCAT(IF(p, n, "?")),
returnArray, FILTER(d, ISNUMBER(SEARCH(searchStr,d))*(LEN(d)=l)*SUM(p)),
IFERROR(TEXTJOIN(",",,returnArray),"")
)