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

#### OttoMan

##### New Member
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
ABCDEFGHI
1IDWeek1Week2Week3Week4
21128921Week3
31162392118901
41189013120723
51207233103670
61327382
71357011
81036703
91045124
101068504
Sheet2

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Yogi Anand

##### MrExcel MVP
Hi Ottoman:

Welcome to MrExcel Board!

one way ...
Book1
ABCDEFGHI
1IDWeek1Week2Week3Week43
21128921Week3
31162392118901
41189013120723
51207233103670
61327382
71357011
81036703
91045124
10106850
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

##### New Member
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

##### New Member
Thanks Yogi Anand,.. just the formula I wanted !!!!

#### Yogi Anand

##### MrExcel MVP
You Are Very Welcome Ottoman ... now let us keep EXCELing.

#### venkat1926

##### Well-known Member
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

Code:
``````'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
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))
m = 0
On Error Resume Next
With rng1
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then Exit Sub
p = cfind.Column
With Columns(p)
n = Right(cfind, 1)
Set cfind1 = .Cells.Find(what:=n, lookat:=xlWhole)
If cfind1 Is Nothing Then GoTo line1
m = m + 1
rng.Offset(m, 0) = Cells(cfind1.Row, "a")
Do
Set cfind1 = .FindNext(after:=cfind1)
If cfind1 Is Nothing 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``````
Code:
``````Sub undo()
Range(Range("H2").Offset(1, 0), Range("H2").End(xlDown)).Clear

End Sub``````

##### MrExcel MVP
<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

##### New Member

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 !!!

Replies
2
Views
214
Replies
3
Views
570
Replies
7
Views
316
Replies
7
Views
281
Replies
4
Views
1K

1,191,356
Messages
5,986,177
Members
440,008
Latest member
Cmbuck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back