Find the part number in which data are located

[Kishan]

Using Excel 2000

Hi,

My data are divided in the 4-parts (part-1 to part-4) in columns C:AC, within these columns in the each row is filled only one cell within any of 4-parts.

I want the results in column AE:AH "part number2 in which part data are located

Example data.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1
2
3
4																															Result	Result	Result	Result
5			Part 1								Part 2			PART 3									Part 4								Part1	Part2	Part3	Part4
6												1|X|1																				2
7												1|X|1																				2
8																	X|1|X																3
9																							X|X|X											4
10																						2|X|1											3
11																	X|1|X																3
12														1|X|X																			3
13																									X|2|X									4
14			1|1|1																												1
15					1|2|1																										1
16														1|X|X																			3
17														1|X|X																			3
18			1|1|1																												1
19																		X|1|2															3
20			1|1|1																												1
21			1|1|1																												1
22			1|1|1																												1
23							1|2|2																								1
24											1|1|X																					2
25
26
27
Sheet10

Thank you in advance

Regards,
Kishan

 

[Logit]

.
The formula you can use is COUNTA.

=IF(COUNTA(C6:J6)=0,"","1")

Paste the above formula in AE6 and drag down the column.



In AF6 paste and drag down :

=IF(COUNTA(K6:M6)=0,"","2")

In AG6 paste and drag down :

=IF(COUNTA(N6:V6)=0,"","3")

In AH6 paste and drag down :

=IF(COUNTA(W6:AC6)=0,"","4")

 

[Kishan]

.
The formula you can use is COUNTA.

=IF(COUNTA(C6:J6)=0,"","1")

Paste the above formula in AE6 and drag down the column.



In AF6 paste and drag down :

=IF(COUNTA(K6:M6)=0,"","2")

In AG6 paste and drag down :

=IF(COUNTA(N6:V6)=0,"","3")

In AH6 paste and drag down :

=IF(COUNTA(W6:AC6)=0,"","4")

Hello Logit, yes formulas worked with the post#1 perfectly, as my original data results are based on formulas, adapting your idea I changed COUNTA to SUMPRODUCT

=IF(SUMPRODUCT(--(C6:J6>"")),1,"") 
=IF(SUMPRODUCT(--(K6:M6>"")),2,"")
=IF(SUMPRODUCT(--(N6:V6>"")),3,"")
=IF(SUMPRODUCT(--(W6:AC6>"")),4,"")

Thank you very much for your help and time

Have a nice day

Regards,
Kishan

 

[Logit]

.
You are welcome.