Looking up 2 values to return only those that match to another cel
 

I have a row of dates and a row of websites. What I am trying to do is lookup
the date and if the date matches. It would then lookup the the webiste and
return the of times it sees the matching date and website to a seperate sheet
under that date:

10/15/07 10/16/07
Monster 4 0
Careerbuilder 6 3

The above is what is what I am trying to.

Looking up 2 values to return only those that match to another cel
 

On Oct 29, 4:25 pm, tim2216 wrote:
I have a row of dates and a row of websites. What I am trying to do is lookup
the date and if the date matches. It would then lookup the the webiste and
return the of times it sees the matching date and website to a seperate sheet
under that date:

10/15/07 10/16/07
Monster 4 0
Careerbuilder 6 3

The above is what is what I am trying to.


Assuming your two rows begin in A1 (sites) and A2 (dates), create a
3rd row beginning in A3 (keys):

=CONCATENATE(TEXT(A2,"YYMMDD"),A1)

This will create a composite key into your data which looks like
"071015Monster"
You can hide this row if it's not pretty.

Now you need a summary table as you mentioned.
The column headings of this table will be your dates, as you
described. (10/15/2007,...)
The row headings of this table will be your sites. (Monster,...)

Let's say this summary table has an upper left corner in cell C9, on
the same sheet. (it can be on any sheet)
Your formula for the upper left cell of the summary data will then be
in D10 (since the sites take up column C and the dates take up row 9)

That formula in cell D10 will be:

=COUNTIF($3:$3,CONCATENATE(TEXT(D$9,"YYMMDD"),$C10 ))

This produces a composite key for each combination of date/site and
then counts how many of those composite keys appear in row $3:$3.

(this formula is copy-safe, so you can copy/paste it to expand your
table)


Brian Herbert Withun

Looking up 2 values to return only those that match to another cel
 

On Oct 29, 4:25 pm, tim2216 wrote:
I have a row of dates and a row of websites. What I am trying to do is lookup
the date and if the date matches. It would then lookup the the webiste and
return the of times it sees the matching date and website to a seperate sheet
under that date:

10/15/07 10/16/07
Monster 4 0
Careerbuilder 6 3

The above is what is what I am trying to.


Assuming your two rows begin in A1 (sites) and A2 (dates), create a
3rd row beginning in A3 (keys):

=CONCATENATE(TEXT(A2,"YYMMDD"),A1)

This will create a composite key into your data which looks like
"071015Monster"
You can hide this row if it's not pretty.

Now you need a summary table as you mentioned.
The column headings of this table will be your dates, as you
described. (10/15/2007,...)
The row headings of this table will be your sites. (Monster,...)

Let's say this summary table has an upper left corner in cell C9, on
the same sheet. (it can be on any sheet)
Your formula for the upper left cell of the summary data will then be
in D10 (since the sites take up column C and the dates take up row 9)

That formula in cell D10 will be:

=COUNTIF($3:$3,CONCATENATE(TEXT(D$9,"YYMMDD"),$C10 ))

This produces a composite key for each combination of date/site and
then counts how many of those composite keys appear in row $3:$3.

(this formula is copy-safe, so you can copy/paste it to expand your
table)


Brian Herbert Withun