Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing text cells
I want to compare two lists of comma delimited text items in two separate
cells and then determine which items are in one cell and not in the other, and visa versa. The available functions do not seem to support this, except EXACT, which simply returns TRUE or FALSE. I want the items that represent the difference between the cells. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing text cells
You may use and UDF like this:
Function CompareStr(rng1 As Range, rng2 As Range) Application.Volatile Dim rng1Values, rng2Values, d, result, i 'The exists method of the dictionary object will be useful here Set d = CreateObject("Scripting.Dictionary") 'Create arrays with the range values rng1Values = Split(rng1.Value, ",") rng2Values = Split(rng2.Value, ",") 'Loop over the values of the second array, and fill the dictionary For i = 0 To UBound(rng2Values) d.Add rng2Values(i), 1 Next 'Loop over the values of the first array, and check if they are in the dictionary For i = 0 To UBound(rng1Values) 'If the value is not present, add it to the result If Not d.exists(rng1Values(i)) Then result = result & "," & rng1Values(i) End If Next 'Present the result, if any If Len(result) 0 Then CompareStr = Right(result, Len(result) - 1) Else CompareStr = 0 End If End Function This function needs two cell references, and it will show the elements on the first cell that are not in the second one. If you want to have the other way, just swap the references. Hope this helps, Miguel. "Comander" wrote: I want to compare two lists of comma delimited text items in two separate cells and then determine which items are in one cell and not in the other, and visa versa. The available functions do not seem to support this, except EXACT, which simply returns TRUE or FALSE. I want the items that represent the difference between the cells. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing text cells
Thank you Miguel!
"Miguel Zapico" wrote: You may use and UDF like this: Function CompareStr(rng1 As Range, rng2 As Range) Application.Volatile Dim rng1Values, rng2Values, d, result, i 'The exists method of the dictionary object will be useful here Set d = CreateObject("Scripting.Dictionary") 'Create arrays with the range values rng1Values = Split(rng1.Value, ",") rng2Values = Split(rng2.Value, ",") 'Loop over the values of the second array, and fill the dictionary For i = 0 To UBound(rng2Values) d.Add rng2Values(i), 1 Next 'Loop over the values of the first array, and check if they are in the dictionary For i = 0 To UBound(rng1Values) 'If the value is not present, add it to the result If Not d.exists(rng1Values(i)) Then result = result & "," & rng1Values(i) End If Next 'Present the result, if any If Len(result) 0 Then CompareStr = Right(result, Len(result) - 1) Else CompareStr = 0 End If End Function This function needs two cell references, and it will show the elements on the first cell that are not in the second one. If you want to have the other way, just swap the references. Hope this helps, Miguel. "Comander" wrote: I want to compare two lists of comma delimited text items in two separate cells and then determine which items are in one cell and not in the other, and visa versa. The available functions do not seem to support this, except EXACT, which simply returns TRUE or FALSE. I want the items that represent the difference between the cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing cells that contain numbers and text | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
question about removing text from cells, leaving numbers | Excel Discussion (Misc queries) |