Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array (MATCH function?)
The worksheet "Arenas" contains a list of names in colA. The worksheet
"Chart1" contains a list of dates in colA and a list of names in colE. From the Arenas worksheet, I want to populate cell E4 with a value of 1 for when a name in Arenas:colA matches a name in Chart1:colE and has a unique date in Chart1:colA. If there are multple matches for a given date, I still want cell E4 to have a value of 1 (instead of the number of times a match occurs for that date). If no matches exist, E4 should have a value of 0. Can anyone kindly help? Thank you. Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array (MATCH function?)
The following entered in Col B of Arenas will give you 1 if the name in Col A
matches with a name in Chart1:colE =ISNA(VLOOKUP(A1,Charts!E:E,1,False),0,1) I did not understand your reference to E4... I don't think this is what you want... Let us know "bob" wrote: The worksheet "Arenas" contains a list of names in colA. The worksheet "Chart1" contains a list of dates in colA and a list of names in colE. From the Arenas worksheet, I want to populate cell E4 with a value of 1 for when a name in Arenas:colA matches a name in Chart1:colE and has a unique date in Chart1:colA. If there are multple matches for a given date, I still want cell E4 to have a value of 1 (instead of the number of times a match occurs for that date). If no matches exist, E4 should have a value of 0. Can anyone kindly help? Thank you. Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array (MATCH function?)
I think we'll probably need to see an example.
-- Biff Microsoft Excel MVP "bob" wrote in message ... The worksheet "Arenas" contains a list of names in colA. The worksheet "Chart1" contains a list of dates in colA and a list of names in colE. From the Arenas worksheet, I want to populate cell E4 with a value of 1 for when a name in Arenas:colA matches a name in Chart1:colE and has a unique date in Chart1:colA. If there are multple matches for a given date, I still want cell E4 to have a value of 1 (instead of the number of times a match occurs for that date). If no matches exist, E4 should have a value of 0. Can anyone kindly help? Thank you. Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array (MATCH function?)
Sorry if i wasn't clear, the reference to E4 is the cell in which the formula
will go. Note sure how to provide an example (file) for you in this forum. The formula you suggested returns an error and points to the zero as the problem. Bob "Sheeloo" wrote: The following entered in Col B of Arenas will give you 1 if the name in Col A matches with a name in Chart1:colE =ISNA(VLOOKUP(A1,Charts!E:E,1,False),0,1) I did not understand your reference to E4... I don't think this is what you want... Let us know "bob" wrote: The worksheet "Arenas" contains a list of names in colA. The worksheet "Chart1" contains a list of dates in colA and a list of names in colE. From the Arenas worksheet, I want to populate cell E4 with a value of 1 for when a name in Arenas:colA matches a name in Chart1:colE and has a unique date in Chart1:colA. If there are multple matches for a given date, I still want cell E4 to have a value of 1 (instead of the number of times a match occurs for that date). If no matches exist, E4 should have a value of 0. Can anyone kindly help? Thank you. Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array (MATCH function?)
I am providing an example as to the above question.
I am trying to calculate how many games a basketball player has played based on the entries in the worksheets "Arenas" and "Chart1." In Arenas, the entries in col A are as follows: COL A Arenas Butler Haywood Thomas In Chart1 the entries in cols A and E are as follows: COL A COL E 4/23/08 Arenas 4/24/08 Butler 4/24/08 Arenas 4/23/08 Haywood 4/23/08 Arenas 4/26/08 Thomas In this example, it is evident from the data in Chart1 that Arenas has played in 2 games (4/23 and 4/24) even though his name populates 3 cells, 2 of which are on the same date (4/23). In the Arenas sheet, I want a formula that will calculate the number of games a player has played when his name appears in both Chart1 and Arenas. Make sense? Thanks, Bob "T. Valko" wrote: I think we'll probably need to see an example. -- Biff Microsoft Excel MVP "bob" wrote in message ... The worksheet "Arenas" contains a list of names in colA. The worksheet "Chart1" contains a list of dates in colA and a list of names in colE. From the Arenas worksheet, I want to populate cell E4 with a value of 1 for when a name in Arenas:colA matches a name in Chart1:colE and has a unique date in Chart1:colA. If there are multple matches for a given date, I still want cell E4 to have a value of 1 (instead of the number of times a match occurs for that date). If no matches exist, E4 should have a value of 0. Can anyone kindly help? Thank you. Bob |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array (MATCH function?)
Thanks for clearing it up Bob.
Afraid I don't have time to think too far on this, but could you setup a PivotTable (Data - PivotTable) to display the data you want? Possibly combine a PivotTable with a COUNT function. I realize that's not a very elegant solution, but hopefully it provides some ideas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "bob" wrote: I am providing an example as to the above question. I am trying to calculate how many games a basketball player has played based on the entries in the worksheets "Arenas" and "Chart1." In Arenas, the entries in col A are as follows: COL A Arenas Butler Haywood Thomas In Chart1 the entries in cols A and E are as follows: COL A COL E 4/23/08 Arenas 4/24/08 Butler 4/24/08 Arenas 4/23/08 Haywood 4/23/08 Arenas 4/26/08 Thomas In this example, it is evident from the data in Chart1 that Arenas has played in 2 games (4/23 and 4/24) even though his name populates 3 cells, 2 of which are on the same date (4/23). In the Arenas sheet, I want a formula that will calculate the number of games a player has played when his name appears in both Chart1 and Arenas. Make sense? Thanks, Bob "T. Valko" wrote: I think we'll probably need to see an example. -- Biff Microsoft Excel MVP "bob" wrote in message ... The worksheet "Arenas" contains a list of names in colA. The worksheet "Chart1" contains a list of dates in colA and a list of names in colE. From the Arenas worksheet, I want to populate cell E4 with a value of 1 for when a name in Arenas:colA matches a name in Chart1:colE and has a unique date in Chart1:colA. If there are multple matches for a given date, I still want cell E4 to have a value of 1 (instead of the number of times a match occurs for that date). If no matches exist, E4 should have a value of 0. Can anyone kindly help? Thank you. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Indirect function in Index/Match Array | Excel Discussion (Misc queries) | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
1:1 as the Array using the MATCH function | New Users to Excel | |||
Match as well as does not match array function | Excel Discussion (Misc queries) |