I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where the
"DISC" value is in column B. The location where I want to report these will
be a separate workbook in the spreadsheet and I want them each reported as a
separate cell in a single row. Is there a way to use Vlookup to search the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

Use MATCH function in the right column to find the discuse that within an
INDEX function. Look in the help index for both.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric_in_EVV" wrote in message
...
I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where
the
"DISC" value is in column B. The location where I want to report these
will
be a separate workbook in the spreadsheet and I want them each reported as
a
separate cell in a single row. Is there a way to use Vlookup to search
the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

One way...

Assuming your data is on Sheet1 in the range A2:B7

In some other sheet enter this formula in A1:

=COUNTIF(Sheet1!B:B,"disc")

Enter this array formula** in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)A$1,"",SMALL(IF(Sheet1!B$2:B$7="d isc",Sheet1!A$2:A$7),ROWS(A$2:A2)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format A2:An as Date

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where
the
"DISC" value is in column B. The location where I want to report these
will
be a separate workbook in the spreadsheet and I want them each reported as
a
separate cell in a single row. Is there a way to use Vlookup to search
the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

You would normally use an INDEX/MATCH formula when your lookup values
are on the left of the sought item, but the problem with MATCH (and
VLOOKUP) is that it will only find the first occurrence or the sought-
value.

One way of doing this is to use Autofilter. Select DISC from the
filter pull-down on column B and then copy the visible cells in column
A to another sheet. Then you can transpose those dates across onto one
row.

If you wanted a formula solution (which will update automatically),
then you could enter this formula in C1 of the first sheet:

=IF(B1="","",COUNTIF(B$1:B1,"DISC"))

and copy this down. It will give you a running count against each
DISC. Then in Sheet2 you could put this formula in A1:

=IF(COLUMN(A1)MAX(Sheet1!C:C),"",INDEX(Sheet1!A:A ,MATCH(COLUMN
(A1),Sheet1!C:C,0)))

Then you can copy this across as far as you need to.

Hope this helps.

Pete

On Dec 1, 6:32*pm, Eric_in_EVV
wrote:
I have a spreadsheet that looks like this:

1/1/09 * * *<blank
1/2/09 * * *DISC
1/3/09 * * *<blank
1/4/09 * * *<blank
1/5/09 * * *<blank
1/6/09 * * *DISC

I need to search the table and be able to report out all the dates where the
"DISC" value is in column B. *The location where I want to report these will
be a separate workbook in the spreadsheet and I want them each reported as a
separate cell in a single row. *Is there a way to use Vlookup to search the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

Hi,

Try this array formula - See below on how to enter it - Array enter the
formul and then darg down to find second match etc.

=INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20="Disc",ROW($ A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike


"Eric_in_EVV" wrote:

I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where the
"DISC" value is in column B. The location where I want to report these will
be a separate workbook in the spreadsheet and I want them each reported as a
separate cell in a single row. Is there a way to use Vlookup to search the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

Just recorded a simple macro; may be the easiest way:
Sub Macro1()

Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$100").AutoFilter Field:=2, Criteria1:="<"
Columns("A:A").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Eric_in_EVV" wrote:

I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where the
"DISC" value is in column B. The location where I want to report these will
be a separate workbook in the spreadsheet and I want them each reported as a
separate cell in a single row. Is there a way to use Vlookup to search the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

After RE-reading, I suggest this macro. Correct word wrap if necessary

Sub finddisc()
Set ss = sheets("sheet11")
Set ds = sheets("sheet6")
MC = 5 'column E
With ss.Columns(MC)
Set c = .find("DISC", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox c.Row
ds.Cells(Rows.Count, "i").End(xlUp).Offset(1) = ss.Cells(c.Row, MC - 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric_in_EVV" wrote in message
...
I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where
the
"DISC" value is in column B. The location where I want to report these
will
be a separate workbook in the spreadsheet and I want them each reported as
a
separate cell in a single row. Is there a way to use Vlookup to search
the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

Thank you all for the responses. They all seem to work as expected. The one
that seems best for my specific situation is the one posted by Pete. I just
love this forum...it's a great place to get advice on solutions !

Thank you all so much !

"Don Guillett" wrote:

After RE-reading, I suggest this macro. Correct word wrap if necessary

Sub finddisc()
Set ss = sheets("sheet11")
Set ds = sheets("sheet6")
MC = 5 'column E
With ss.Columns(MC)
Set c = .find("DISC", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox c.Row
ds.Cells(Rows.Count, "i").End(xlUp).Offset(1) = ss.Cells(c.Row, MC - 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric_in_EVV" wrote in message
...
I have a spreadsheet that looks like this:

1/1/09 <blank
1/2/09 DISC
1/3/09 <blank
1/4/09 <blank
1/5/09 <blank
1/6/09 DISC

I need to search the table and be able to report out all the dates where
the
"DISC" value is in column B. The location where I want to report these
will
be a separate workbook in the spreadsheet and I want them each reported as
a
separate cell in a single row. Is there a way to use Vlookup to search
the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !


.

Well, thanks for feeding back, Eric - glad to be of help.

Pete

On Dec 2, 4:25*am, Eric_in_EVV
wrote:
Thank you all for the responses. *They all seem to work as expected. *The one
that seems best for my specific situation is the one posted by Pete. *I just
love this forum...it's a great place to get advice on solutions !

Thank you all so much !



"Don Guillett" wrote:
After RE-reading, I suggest this macro. Correct word wrap if necessary

Sub finddisc()
Set ss = sheets("sheet11")
Set ds = sheets("sheet6")
MC = 5 'column E
With ss.Columns(MC)
* * Set c = .find("DISC", LookIn:=xlValues, LookAt:=xlWhole, _
* * SearchOrder:=xlByRows, SearchDirection:=xlNext, _
* * MatchCase:=False, SearchFormat:=False)

* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * 'MsgBox c.Row
* *ds.Cells(Rows.Count, "i").End(xlUp).Offset(1) = ss.Cells(c.Row, MC - 1)
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric_in_EVV" wrote in message
...
I have a spreadsheet that looks like this:

1/1/09 * * *<blank
1/2/09 * * *DISC
1/3/09 * * *<blank
1/4/09 * * *<blank
1/5/09 * * *<blank
1/6/09 * * *DISC

I need to search the table and be able to report out all the dates where
the
"DISC" value is in column B. *The location where I want to report these
will
be a separate workbook in the spreadsheet and I want them each reported as
a
separate cell in a single row. *Is there a way to use Vlookup to search
the
2nd column and report the value from the first column....or is there some
other method of doing this ?

Thanks !

.- Hide quoted text -

- Show quoted text -