Unique data collection, if function

[kishore k]

Hi, I want data relating to desired number in excel with any suitable formula. Ex: In sheet1, cell A95 has "121000". A96 "AA101", A97 "BX112", A98 "MN10" ect,. and A99 "1210001".now if i put formula in sheet2, i need Data relating the number "121000" in each cell by cell. and formula has to stop if it finds some other number than "121000"

 

[kishore k]

can anyone reply to the query please..

 

[Nishant94]

I am not sure what you are after, but if are are looking for something like this then here it is:

Assuming you have data like this in Sheet1:

Book1
	A
95	121000
96	AA101
97	BX112
98	MN10
99	121001
100	AA102
101	BX113
102	MN11
103	121002
104	AA103
105	BX114
106	MN12
107	121003
108	AA104
109	BX115
110	MN13
111	141001
112	AA105
113	BX116
114	MN14
Sheet1

And you want something like this:

Book1
	A	B	C	D
2	121000	AA101	BX112	MN10
3	121001	AA102	BX113	MN11
4	121002	AA103	BX114	MN12
5	121003	AA104	BX115	MN13
6
7
8
Sheet2

Cell Formulas
Range	Formula
A2	{=IF(ROWS(A$2:A2)>SUM(--ISNUMBER(SEARCH("121???",Sheet1!$A$95:$A$114))),"",INDEX(Sheet1!$A$95:$A$114,COLUMNS($A2:A2)+4*(ROWS(A$2:A2)-1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.