Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Identify Values in Common Between 2 cells
I have a spreadsheet that contains cells that each have a range of values.
For instance, 1 cell might have the values of 1-15. The next cell might be 11-20. I want to set up another table that compares each combination of 2 cells, and identifies the common values. In this example, that would be 11-15. The answer could either be expressed as 11-15 or 5, with 5 being the number of numbers in common between the 2 cells' values. I would prefer 5. I am new to this, and am hopeful that someone has dealt with this need before. Thank you for your assistance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Identify Values in Common Between 2 cells
When you say one cell has the values 1-15, is it literally "1-15" or is it 1,
2, 3, 4, 5, 6, etc (all in one cell)? Assuming the first scenario: A1 = 1 - 15 A2 = 11 - 20 try: =SUM(--ISNUMBER(MATCH(ROW(INDIRECT(--LEFT(A2,FIND("-",A2)-1)&":"&--RIGHT(A2,LEN(A2)-FIND("-",A2)))),ROW(INDIRECT(--LEFT(A1,FIND("-",A1)-1)&":"&--RIGHT(A1,LEN(A1)-FIND("-",A1)))),0))) entered with Control+Shift+Enter. If the second, I would probably try to split the data so that one number was in a cell. Say F1:F15 = 1 through 15 and G1:G10 = 11 through 20, then =SUM(--ISNUMBER(MATCH(F1:F15,G1:G10,0))) again entered with Control+Shift+Enter. Or, maybe use VBA to create a user defined function: Function Test(rng1 As Range, _ rng2 As Range) As Long Dim var1 As Variant Dim var2 As Variant Dim i As Long var1 = Split(rng1.Value, ",", -1, vbTextCompare) var2 = Split(rng2.Value, ",", -1, vbTextCompare) For i = LBound(var1) To UBound(var1) If IsNumeric(Application.Match(var1(i), var2, 0)) Then _ Test = Test + 1 Next i End Function syntax is =Test(A1, A2). You could change the name of the function to whatever you want. "ahofeld" wrote: I have a spreadsheet that contains cells that each have a range of values. For instance, 1 cell might have the values of 1-15. The next cell might be 11-20. I want to set up another table that compares each combination of 2 cells, and identifies the common values. In this example, that would be 11-15. The answer could either be expressed as 11-15 or 5, with 5 being the number of numbers in common between the 2 cells' values. I would prefer 5. I am new to this, and am hopeful that someone has dealt with this need before. Thank you for your assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum up values only in cells that are color filled? | Excel Worksheet Functions | |||
How do I define a range using values in cells? | Excel Worksheet Functions | |||
Is there way to enter multiple values into excel cells w/ a form? | Excel Discussion (Misc queries) | |||
Function to list values of last 3 non-blank cells in a vertical bl | Excel Worksheet Functions | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions |