Extract multiple records matching criteria from list

[William DeLeo]

Lets say I have an Input Sheet with a list of names and dates. For each
name, there may exist multiple dates (i.e. the names may be listed
multiple times each with a different date).

I established an Output Sheet with all the possible names in the list
(Output column 1). For each unique name (row), I need to pull all
dates associated with that name from the Input Sheet list and fill the
adjacent columns.

I need some sort of VLOOKUP-array thing where all fields matching the
criteria are returned to a range of cells.

When I do it manually, I use the auto-filter and copy and paste from
the filtered list. I need to avoid using macros for this task if at
all possible.

Any ideas?

Thanks in advance!

 

[Ron Coderre]

This may be the "square peg, round hole, big hammer" approach, but have
you tried a pivot table for this?

When you build it, set
Rows: Name, Date
Data: count of date

It might give you an acceptable output.

Ron

 

[William DeLeo]

I have never used pivot tables. If you think that would work I'll loo
into it.

Thanks

 

[Ron Coderre]

Assuming data is in Cells A1:B50, with
A1: Name
B1: Date

Step_1: Select your 2 columns of data (say...A1:B50)

Step_2: Data>Pivot Table>
MS Excel list....[Next]
Range: A1:B50...[Next]

Click the [Layout] button
Drag Name to the Rows section
Drag Date to the Rows section
Drag Date to the Data section (it will change to Count of Date)
Click [OK]
Choose Existing Worksheet and select a cell of the right for the Pivot
Table to be located.

Click [Finish]

There's your pivot table....experiment with it.

Ron

 

[William DeLeo]

Thanks so much ... I'll do that.



p.s. You guys rock

 

[Biff]

Hi!

Here's a formula aternative to a pivot table.

Assume that on the Input Sheet the names are in column A, A1:A20 and the
dates are in column B, B1:B20.

On the Output sheet you have the list of unique names listed in column A,
A1:An

Enter this formula in cell B1 as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(ISERROR(LARGE(IF(Sheet1!$A$1:$A$20=$A1,Sheet1!$B$1:$B$20),COUNTIF(Sheet1!$A$1:$A$20,$A1)-(COLUMN(A:A)-1))),"",LARGE(IF(Sheet1!$A$1:$A$20=$A1,Sheet1!$B$1:$B$20),COUNTIF(Sheet1!$A$1:$A$20,$A1)-(COLUMN(A:A)-1)))

Copy down to the end of the list in column A, then across until you get a
solid column of blank cells (meaning the data has been exhausted).

This will extract the dates in ascending order. If you want the dates in
descending order, in the formula change LARGE to SMALL.

Biff

"William DeLeo" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

Ooops!

Minor tweak.....

Change all the references to Sheet1 to the name of your Input Sheet.

You also might have to format the cells as DATE.

Biff

 

[William DeLeo]

Hey Biff,

That's exactly the type of thing I was looking for ... but when I
use the formula all I get are the largest dates in all cells (as I fill
over I just keep getting more and more columns of the largest date).

I'll try to disect it now and figure out what's going on ... but let me
know if you see an error. I used the following specifically (array
entered):

=IF(ISERROR(LARGE(IF(Import!$A$1:$A$1346=$A1,Import!$B$1:$B$1346),COUNTIF(Import!$A$1:$A$1346,$A1)-(COLUMN(A:A)-1))),"",LARGE(IF(Import!$A$1:$A$1346=$A1,Import!$B$1:$B$1346),COUNTIF(Import!$A$1:$A$1346,$A1)-(COLUMN(A:A)-1)))

Either way, thanks so much!

 

[William DeLeo]

Nope ... I'm an idiot ... it wasn't calculating the formulas
automatically. It works perfect!!

Thanks so much!

 

[Biff]

I hate pivot tables so I'm inclined to come up with these types of formulas.

Thanks for the feedback!

Biff

"William DeLeo" <[email protected]>
wrote in message
news:[email protected]...

 

[William DeLeo]

Unfortunately the fix you suggested leads to another dilema. I have a
data column associated with each date as well. I was trying to divide
up the input sheet information into two parallel sheets, one with site
and date, the second with site and data. Next step is to manipulate
the data and move it to sheets that are used for plotting. But, your
method sorts the data in a similar way that it sorts the dates (say low
to high) so the cells that hold the dates (on OUTPUT-DATES) are not the
same as the associated cell that holds the data (on OUTPUT-DATA)
because the data is sorted low to high as well.

Given that I now have a date/site sheet (OUTPUT-DATES) that is sorted
appropriately, can you think of a way to create a parallel sheet that
stores the data (OUTPUT-DATA) in the same cell that the date was
stored? I can envision using some sort of VLOOKUP procedure with two
steps/criteria ... I have the criteria 1 in "OUTPUT-DATA-column A", and
criteria 2 in "OUTPUT-DATES-each column" to be matched to "INPUT-columns
1 and 2" and to return "INPUT-column3".

Following your methodology, I guess I need to first establish a
temporary 3-column array with all the rows where the sites match. Then
from that array find the single row where the date matches. Then return
the column 3 value of that row. Got more innovative array ideas up your
sleave?


p.s. the best thing I learned from your previous formula is that the
syntax "Import!$A$1:$A$1346=$A1" can be used as a loop type function
for the IF conditional. Cool way of integrating LARGE with column
index as well. Very clever Thanks again!

 

[Biff]

Hi!

Sorry, but I'm not really following you on this.

If you can send me your file I'll be able to see what you mean. It sounds
like you want to do a lookup based on site/date. If that's what you want,
it's not difficult (usually!) but I would need *very* specific details.
Here's my address:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"William DeLeo" <[email protected]>
wrote in message
news:[email protected]...

 

[William DeLeo]

I sent the file and look forward to your feedback.

Thanks so much