Return all matching values

[canoako]

I need to find a way to lookup a date in column A that matches a
specific date on another spreadsheet, then return the all the values to
the right of column B that I want. If there is more than 1 date that
matches, I will need ALL the matching dates to display. If Column A
Spreadsheet 1 doesn't match the desired date, then the rows in
Spreadsheet 2 that don't match can be blank.

Spreadsheet 1
Column A: Date
Column B: Name
Column C: Location
etc.
Column A, B, and C will have repeating values, but I only want to see
on Spreadsheet 2 all the values that match a certain day at the top of
that spreadsheet.

Spreadsheet 2 will look like:
Date: 1/1/06

Column A: Multiple rows matching 1/1/06
Column B: All Names that had a date in column A on spreadsheet 1 that
match 1/1/06.
Column C: All Locations that had a date in column A on spreadsheet 1
that match 1/1/06 (must be tied to the name in column B so that the
rows will look the same, but only return the ones that match the date)
etc.

Thanks!
- Jim

 

[Guest]

One way which should deliver precisely what you're after ..

A sample construct is available at:
http://www.savefile.com/files/1512663
Direct Filter by date from another sheet.xls

Assuming source data in sheet: X, cols A to C,
data in row2 down. The key col is col A = dates

In another sheet: Y (say), place

In A2:
=IF(X!A2="","",IF(COUNTIF(X!$A$2:A2,X!A2)>1,"",ROW()))
In B2:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(X!A:A,MATCH(SMALL(A:A,ROW(A1)),A:A,0)))
In C2:
=IF(X!A2="","",IF(X!A2=$D$1,ROW(),""))

Select A2:C2, copy down as far as required to cover the max expected extent
of data in the key col A in X, say down to C2000. (Hide away cols A to C, or
just format the font in white to mask)

Click Insert > Name > Define, input:
Names in workbook: Dates
Refers to: =OFFSET(Y!$B$2,,,SUMPRODUCT(--(Y!$B$2:$B$2000<>"")))
Click OK

Then select D1, click Data > Validation, Allow: List, Source: =Dates
D1 will now yield a selectable dropdown of unique dates from the key col A
in X

Paste the same col headers in X into E1:G1

Then place in E2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(X!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy E2 to G2, fill down by the smallest range sufficient to cover the max
expected number of lines for any single date, say to G51 (if max expected
lines per any single date = 50)

Test it out, select a date from the droplist in D1. All relevant lines for
that date will appear neatly bunched at the top within cols E to G.

 

[Guest]

Missing dress-up line:
Format cols B & E, and the DV cell in D1 as date

---

 

[CanoAko]

Great! This works - What does the "--" do in a function?

 

[Guest]

Great! This works

Glad to hear that

What does the "--" do in a function?

It gently coerces the boolean TRUE/FALSE returns to numeric 1's/0's. The
1's/0's could then be evaluated further, for eg summed by the SUMPRODUCT here
in:
SUMPRODUCT(--(Y!$B$2:$B$2000<>""))

---