Vlookup to the left ?

[Eric_in_EVV]

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 !

 

[Don Guillett]

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

 

[Pete_UK]

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

 

[ryguy7272]

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

 

[Don Guillett]

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