Count Numbers in Contiguous Number Range

[Adeneen]

Hi Everyone,

New to the forum and really appreciate anyone who can assist. Below is a simple version of a much more complex data set that I am working on. I am trying to get excel to identify numbers within a contiguous number range in a single cell and place an "X" in the corresponding column. It will find the beginning and end of the number range, but not the numbers in between. I can't get it to work. Any suggestions?

Current formulas:
"ID" Column: =RIGHT(B2,LEN(B2)-FIND("SS",B2)+1)
"101" Column, copied across thru 120: =IF(NOT(ISERROR(FIND(D$1,$C2))),"X","")

Much appreciated,

Andrew

 

[Eric W]

Welcome to the MrExcel forum!

Try this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		Number	ID	101	102	103	104	105	106	107	108	109	110	111	112	113	114	115	116	117	118	119	120
2		Part AD_SS-101	SS-101	X
3		Part AD_SS-102-106	SS-102-106		X	X	X	X	X
Sheet7

Cell Formulas
Range	Formula
C2:C3	C2	=MID(B2,FIND("_",B2)+1,99)
D2:W3	D2	=IF(AND(D$1>=MID($C2,4,3)+0,D$1<=RIGHT($C2,3)+0),"X","")

This formula makes several assumptions, that each part starts with SS-, and that the numbers are always 3 digits. If that's not the case, let us know, and we can find an alternative.

 

[Adeneen]

This formula makes several assumptions, that each part starts with SS-, and that the numbers are always 3 digits. If that's not the case, let us know, and we can find an alternative.

I'll give it a try!

There are a few instances where the part number starts with a SSN- or SSGN-, but three digits is consistent.

 

[Eric W]

In that case, you should try this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		Number	ID	101	102	103	104	105	106	107	108	109	110	111	112	113	114	115	116	117	118	119
2		Part AD_SS-101	SS-101	X
3		Part AD_SS-102-106	SS-102-106		X	X	X	X	X
4		Part AD_SSGN-109-115	SSGN-109-115									X	X	X	X	X	X	X
Sheet7

Cell Formulas
Range	Formula
C2:C4	C2	=MID(B2,FIND("_",B2)+1,99)
D2:V4	D2	=IF(AND(D$1>=MID($C2,FIND("-",$C2)+1,3)+0,D$1<=RIGHT($C2,3)+0),"X","")

 

[Adeneen]

awesome. I think I can see what you're doing now. If I have 2 digit identifiers (like "23" instead of "101") is there a lot of changes to the formula?

 

[Eric W]

This version should handle 2 or 3 digit identifiers:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Number	ID	97	98	99	100	101	102	103	104	105	106	107
2		Part AD_SS-101	SS-101					X
3		Part AD_SS-102-106	SS-102-106						X	X	X	X	X
4		Part AD_SSGN-98-102	SSGN-98-102		X	X	X	X	X
5		Part AD_SSX-99	SSX-99			X
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=MID(B2,FIND("_",B2)+1,99)
D2:N5	D2	=IF(AND(D$1>=SUBSTITUTE(MID($C2,FIND("-",$C2)+1,3),"-","")+0,D$1<=ABS(RIGHT($C2,3)+0)),"X","")

I can create a version that handles any length, but it would be pretty long.

 

[Adeneen]

This version should handle 2 or 3 digit identifiers:

I can create a version that handles any length, but it would be pretty long.

WOW! Thanks so much!