Returning one single numer.

[serge]

Hello everyone,
I have those 9 numbers in that example in row 7 and I have 4 separate position columns A:D and 3 separate row 3:5.
If I type digit 2 in A:3 which is the 1st position I need a formula starting in F:3 and dragging right to return all numbers that start with a 2.

Same thing if I would type a different digit in the 3rd position it would return all the numbers that have that digit in their 3rd position.

Then in row 4, I have a 0 as a 3rd position so it would return all numbers that have 2 in 1st position and 0 in 3rd position with here reduce to 3 numbers.

Then the final digit here the 2 in the 4th position will return a single number.
I hope it's clear enough to understand ?
Thank you.

 

[Rick Rothstein]

Are the 9 numbers all in cell A7? If so, how many spaces are between each of them?

 

[serge]

Are the 9 numbers all in cell A7? If so, how many spaces are between each of them?

Thank you Rick for answering my post,
No, that was to show you the numbers, you can put it anyway you want in your formula I will follow you or it can be like the example with 1 space in between.

 

[Rick Rothstein]

One more question... are you still using XL2007 like your profile says? Or have you upgraded since you joined this forum 16 years ago?

 

[serge]

One more question... are you still using XL2007 like your profile says? Or have you upgraded since you joined this forum 16 years ago?

I'm still on 2007 it's work fine for me.

 

[serge]

Hi Rick,

After all, I can use the row 7 with 1 space in between each number, Thank you so much.

 

[serge]

Hi, can someone help me with this formula,Please.
Thank you.

 

[Eric W]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Pos 1	Pos 2	Pos 3	Pos 4
2
3	2					2103	2121	2202	2211	2301	#NUM!
4	2		0			2103	2202	2301	#NUM!	#NUM!	#NUM!
5	2		0	2		2202	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!
6
7	2103	2121	2202	2211	2301	3102	3111	3201	4101
Sheet3

Cell Formulas
Range	Formula
F3:K5	F3	=SMALL(IF((MID($A$7:$I$7,IF($A3="",9,1),1)=$A3&"")*(MID($A$7:$I$7,IF($B3="",9,2),1)=$B3&"")*(MID($A$7:$I$7,IF($C3="",9,3),1)=$C3&"")*(MID($A$7:$I$7,IF($D3="",9,4),1)=$D3&""),$A$7:$I$7,""),COLUMNS($F:F))
Press CTRL+SHIFT+ENTER to enter array formulas.

I can get rid of the #NUM! errors, but it would more than double the size of the formula. I'd suggest using Conditional Formatting to just change the font color to the background color. I can't really advise you how to do that though, I believe the CF functionality was significantly different in 2007 than it is in newer versions of Excel. Newer functions would also make that formula simpler too.

 

[Eric W]

An updated version:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Pos 1	Pos 2	Pos 3	Pos 4
2
3	2					2103	2121	2202	2211	2301
4	2		0			2103	2202	2301
5	2		0	2		2202
6
7	2103	2121	2202	2211	2301	3102	3111	3201	4101
Sheet3

Cell Formulas
Range	Formula
F3:K5	F3	=IF(ISERROR(SMALL(IF(MMULT((MID(TRANSPOSE($A$7:$I$7),IF($A3:$D3="",9,{1,2,3,4}),1)=$A3:$D3&"")+0,{1;1;1;1})=4,TRANSPOSE($A$7:$I$7),""),COLUMNS($F:F))),"",SMALL(IF(MMULT((MID(TRANSPOSE($A$7:$I$7),IF($A3:$D3="",9,{1,2,3,4}),1)=$A3:$D3&"")+0,{1;1;1;1})=4,TRANSPOSE($A$7:$I$7),""),COLUMNS($F:F)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[serge]

Thank you very much Eric your formula works well.

I wanted to ask if for example I do not input no digits in A3:D5, can the return table be blank also ?
Meaning that the return table populate as I add digits !
If not possible it's fine I can work with it, Thank you, I really appreciate your help.