Fill Series Number Problem

[muhammad susanto]

hi all...

i need help, how to make fill series number based on the same data :
like this : expected result star from box1 till box5
Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td=bgcolor:#FFFF00]

data​

[/td][td=bgcolor:#FFFF00]

box1​

[/td][td=bgcolor:#FFFF00]

box2​

[/td][td=bgcolor:#FFFF00]

box3​

[/td][td=bgcolor:#FFFF00]

box4​

[/td][td=bgcolor:#FFFF00]

box5​

[/td][td=bgcolor:#FFFF00]

etc…​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

a​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

a​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

a​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

b​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

g​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

f​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

f​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

f​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

f​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

c​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

c​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

any help, much appreciated...

note :
without helper column

m.susanto

 

[Peter_SSs]

Copied across and down.

Book1
	E	F	G	H	I	J
3	data	box1	box2	box3	box4	box5
4	a	1	2	3
5	a
6	a
7	b	1
8	g	1
9	f	1	2	3	4
10	f
11	f
12	f
13	c	1	2
14	c
Fill series

Cell Formulas
Range	Formula
F4	=IF($E4=$E3,"",IF(COLUMNS($F4:F4)>COUNTIF($E4:$E$14,$E4),"",COLUMNS($F4:F4)))

 

[muhammad susanto]

Copied across and down.

	E	F	G	H	I	J

<tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]box1[/TD]
[TD="align: center"]box2[/TD]
[TD="align: center"]box3[/TD]
[TD="align: center"]box4[/TD]
[TD="align: center"]box5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Fill series​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F4[/TH]
[TD="align: left"]=IF($E4=$E3,"",IF(COLUMNS($F4:F4)>COUNTIF($E4:$E$14,$E4),"",COLUMNS($F4:F4)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thanks Peter, i'm sorry i'm missing it...
shoud be :

E	F	G	H	I	J

<tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]box1[/TD]
[TD="align: center"]box2[/TD]
[TD="align: center"]box3[/TD]
[TD="align: center"]box4[/TD]
[TD="align: center"]box5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"] 4 [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"] 5 [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"] 6 [/TD]
[TD="align: center"] 7 [/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

the number is series....

 

[Peter_SSs]

Insert the blue text into the previous formula.
=IF($E4=$E3,"",IF(COLUMNS($F4:F4)>COUNTIF($E4:$E$14,$E4),"",COUNT($F$3:$J3)+COLUMNS($F4:F4)))

 

[muhammad susanto]

Insert the blue text into the previous formula.
=IF($E4=$E3,"",IF(COLUMNS($F4:F4)>COUNTIF($E4:$E$14,$E4),"",COUNT($F$3:$J3)+COLUMNS($F4:F4)))

hello Peter,....
working well, Thank so much....

 

[Peter_SSs]

hello Peter,....
working well, Thank so much....

You are welcome.