find and match a same number occurence in a column against another

[OttoMan]

Hello All,
How can I use a formula to find the relevant week number in cell H2 (B1:E1) and identify the number of occurences of 3 (for week3) and list the ID's against that from A1:A10?

H3:H5 is an example of what the outcome should be,...and whenever the Week number is changed the list should populate with the new ID's for that relevant week,..

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ID	Week1	Week2	Week3	Week4
2	112892	1						Week3
3	116239		2					118901
4	118901			3				120723
5	120723			3				103670
6	132738		2
7	135701	1
8	103670			3
9	104512				4
10	106850				4
Sheet2

 

[Wulf]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		ID	Week1	Week2	Week3	Week4						Week1	Week2	Week3	Week4
2	2	112892	1						Week3			112892
3	3	116239		2					118901				116239
4	4	118901			3				120723					118901
5	5	120723			3				103670					120723
6	6	132738		2									132738
7	7	135701	1									135701
8	8	103670			3									103670
9	9	104512				4									104512
10	10	106850				4									106850
Sheet1

Like this?

 

[Yogi Anand]

Hi Ottoman:

Welcome to MrExcel Board!

one way ...

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Week1	Week2	Week3	Week4			3
2	112892	1						Week3
3	116239		2					118901
4	118901			3				120723
5	120723			3				103670
6	132738		2
7	135701	1
8	103670			3
9	104512				4
10	106850
Sheet13

array formula in cell H1 is ... =SUM(IF(INDEX(A2:G10,0,MATCH($H$2,$A$1:$G$1))>0,1,""))

array formula in cell H3 is ... =IF(ROWS($1:1)>$H$1,"",INDEX($A$1:$A$10,SMALL(IF(ISNUMBER(INDEX($A$2:$G$10,0,MATCH($H$2,$A$1:$G$1))),ROW($A$2:$A$10),""),ROWS($1:1))))

this is then copied down.

 

[OttoMan]

Thank Wulf,.. that's quite simple,.. however is there a way of doing this without creating another table with the IF formula,.. like a single formula in H3 onwards??...
atlast for the time being I will use this,... thanks for your help,..

 

[OttoMan]

Thanks Yogi Anand,.. just the formula I wanted !!!!

 

[Yogi Anand]

You Are Very Welcome Ottoman ... now let us keep EXCELing.

 

[venkat1926]

perhaps pivot table can be used. but I do not have much experince in pivot tables except for some preliminary ideas.

I have prepared a macro " test"
see the comments given at the top of the macro in capital letters.

there is also UNDO macro which undoes what the macaro does so that you can check again or you can change the week no. and run

Once you have run the macro, run UNDO macro and then only run test
the two maros are

'CLEAR ANY VALUES OR FORMULAS IN THE FIRST ROW AFTER "WEEK4"
'THERE SHOULD BE BLANK COLUMNS BETWEEN THE MAIN DATA AND WHAT YOU WANT TO FIND
'FOR EACH WEEK.E.G. BETWEN COLUMNS N AND H
'THERE SHULD NOT BE MORE THAN 9 WEEKS. OTHERWISE
'SOME MODIFICATION TO MACRO IS REQUIRED.


Sub test()
Dim rng1 As Range, rng As Range
Dim x
Dim cfind As Range, cfind1 As Range
Dim j As Integer, k As Integer, m As Integer, n As Integer
Dim p As Integer
Dim add As String
j = Range("a1").End(xlDown).Row
k = Range("a1").End(xlToRight).Column
'msgbox "no. of rows  " & j
'msgbox "no. of columns   " & k
Set rng = Range("H2")
x = rng.Value
Set rng = Range("H2")
Set rng1 = Range(Cells(1, 1), Cells(j, k))
'msgbox rng1.Address
m = 0
On Error Resume Next
With rng1
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then Exit Sub
'msgbox cfind.Address
p = cfind.Column
With Columns(p)
n = Right(cfind, 1)
Set cfind1 = .Cells.Find(what:=n, lookat:=xlWhole)
'msgbox cfind1.Address
If cfind1 Is Nothing Then GoTo line1
add = cfind1.Address
'msgbox add
m = m + 1
rng.Offset(m, 0) = Cells(cfind1.Row, "a")
Do
Set cfind1 = .FindNext(after:=cfind1)
If cfind1 Is Nothing Then GoTo line1
'msgbox cfind1.Address
If cfind1.Address = add Then GoTo line1
m = m + 1
rng.Offset(m, 0) = Cells(cfind1.Row, "a")
Loop
End With
End With
line1:
MsgBox "macro over. click ok"
End Sub

Sub undo()
Range(Range("H2").Offset(1, 0), Range("H2").End(xlDown)).Clear

End Sub

 

[Aladin Akyurek]

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=512 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17>ID</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Week1</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Week2</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Week3</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Week4</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl30 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:fmla='=COUNTIF(INDEX(B2:E9,0,MATCH(H2,B1:E1,0)),REPLACE(H2,1,4,"")+0)' x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>112892</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64>Week3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>116239</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:fmla='=IF(ROWS(H$3:H3)<=H$1,INDEX($A$2:$A$10,SMALL(IF("Week"&INDEX($B$2:$E$10,0,MATCH(H$2,$B$1:$E$1,0))=H$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(H$3:H3))),"")' x:num x:arrayrange="H3">118901</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>118901</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:fmla='=IF(ROWS(H$3:H4)<=H$1,INDEX($A$2:$A$10,SMALL(IF("Week"&INDEX($B$2:$E$10,0,MATCH(H$2,$B$1:$E$1,0))=H$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(H$3:H4))),"")' x:num x:arrayrange="H4">120723</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>120723</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:fmla='=IF(ROWS(H$3:H5)<=H$1,INDEX($A$2:$A$10,SMALL(IF("Week"&INDEX($B$2:$E$10,0,MATCH(H$2,$B$1:$E$1,0))=H$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(H$3:H5))),"")' x:num x:arrayrange="H5">103670</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>132738</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(H$3:H6)<=H$1,INDEX($A$2:$A$10,SMALL(IF("Week"&INDEX($B$2:$E$10,0,MATCH(H$2,$B$1:$E$1,0))=H$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(H$3:H6))),"")' x:arrayrange="H6"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>135701</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl36 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>103670</TD><TD class=xl37 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl37 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl37 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: white" width=64 x:num>3</TD><TD class=xl37 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl38 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl39 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>104512</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>4</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>106850</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> </TD></TR></TBODY></TABLE>

H1:

=COUNTIF(INDEX(B2:E10,0,MATCH(H2,B1:E1,0)),REPLACE(H2,1,4,"")+0)

H3:

Control+shift+enter, not just enter...

=IF(ROWS(H$3:H3)<=H$1,INDEX($A$2:$A$10,SMALL(IF("Week"&INDEX($B$2:$E$10,0,MATCH(H$2,$B$1:$E$1,0))=H$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(H$3:H3))),"")

...and copy down.

The above scheme is robust against inserting rows before the data.

 

[OttoMan]

Thank you Aladin Akyurek.

Just couldn't believe the number of different excel formulas there are to do the same thing !! I am actually trying to figure out what the formula is doing,.. best thing is it just works !!!