Extract multiple records matching criteria from list

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!

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

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

Thanks!

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

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



p.s. You guys rock!

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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!

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

Thanks so much!

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

I sent the file and look forward to your feedback.

Thanks so much!

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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]...
>>
>> 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!
>>
>>
>> --
>> William DeLeo
>> ------------------------------------------------------------------------
>> William DeLeo's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1256
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=382077
>>
>
>

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]...
>
> Nope ... I'm an idiot ... it wasn't calculating the formulas
> automatically. It works perfect!!
>
> Thanks so much!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>

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]...
>
> 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!
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile:
> http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=382077
>