Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX MATCH but need to change return Values | Excel Discussion (Misc queries) | |||
Index match to return all values | Excel Worksheet Functions | |||
match values and return a third one | Excel Worksheet Functions | |||
match values in two column and return value in | Excel Worksheet Functions | |||
How to use MATCH function to return multiple values | Excel Programming |