matching items

[tony lindsley]

I am using the following formula to return values when a cell is equa
to
that on a worksheet. First of all I use the countIF scenario to coun
all the
items that are identical. Then an address to work out the position and
finally match to find the relevant data which is on the same line. M
problem
is that when it finds the first set of data it returns this and th
following
number of rows that match the count instead of the first set of data
the
second set and so on. Can anyone help please?
(worksheet2 formula used to match data from worksheet 1.)

a1 =(cell to be matched i.e 30/09/04)
b1 =COUNTIF(worksheet1!B:B,A1)


a
=IF($C$1>B4,CELL("address",OFFSET(worksheet1!B1,MATCH($A$1,worksheet1!B:B,0
-1,0)),"")
b4 =(item number in sequence starting from 1)
c4 =IF($C$1>=$B4,OFFSET(worksheet1!$B1,MATCH($A$1,worksheet1!
B:$B,0)-1,0),"")
d4 =IF($C$1>=$B4,OFFSET(worksheet1!$B1,MATCH($A$1,worksheet1!
B:$B,0)-1,-1),"")
2nd line
a
=IF($C$1>B5,CELL("address",OFFSET(INDIRECT(A4,1),MATCH($A$1,INDIRECT(A4):wo
ksheet1!B10000, 0),0)),"")
b5 =(next number in sequence ie 2)
c5 =IF($C$1>=$B5,OFFSET(INDIRECT($A4,1),MATCH($A$1,INDIRECT($
4):worksheet1!$B10000, 0),0),"")
d5 =IF($C$1>=$B5,OFFSET(INDIRECT($A4,1),MATCH($A$1,INDIRECT($
4):worksheet1!$B10000, 0),-1),"")
then the second line repeating

The data on worksheet 1 looks like the following:

title date
item 1 10/09/04
item 2 11/09/04
item 3 10/09/04
item 4 10/09/04
item 5
item 6 10/09/04

What I want to appear on worksheet 2 are:

(cell a1 being the cell to be replicated i.e 10/09/04)

item 1 10/09/04
item 3 10/09/04
item 4 10/09/04
item 6 10/09/0

 

[Peter Atherton]

Tony

You do not need formulas for this. Excel has good database
facilities using Filters and Advanced Filters on the Data
Menu.

Auto Filters hideunwanted information in place while
advanced filters copy the information to a new location.

Seeon-;ine helpor visit www.contextures.com to see good
tutorials.

Regards
Peter