Convert UDF to a macro for extracting comma-delimited values

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
756
Office Version
  1. 365
Platform
  1. Windows
The following function does exactly what I need in terms of returning comparing cells A1 and B1 (both have comma delimited text with cell addresses) and returning in C1 those addresses that appear in B1 but not in A1.

Example: A1 D10,D11,E15,E17,E25,F3,G9,G16
B1 G16, E17, M32, H11, D11, D11
C1 M32, H11

Is is possible to convert this function to a macro where the results in C1 only appear when the macro is run? Columns A and B will have content in some (but not all) of the rows between 51 and 239, inclusive.

Thanks so much!

Code:
Function GetUnique(a As String, b As String) As String
   Dim i As Long
   Dim Sp As Variant, tmp As Variant
      Sp = Split(b, ",")
   tmp = Split(a, ",")
   For i = 0 To UBound(Sp)
      tmp = Filter(tmp, Sp(i), False, vbTextCompare)
   Next i
   GetUnique = Join(tmp, ",")
End Function

(From: Extracting differences between two comma-separated strings)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
This has StartRow and EndRow hard-coded as 51 and 239. Can be made more dynamic by looking at selection or other ways. Uses your existing function.

VBA Code:
Sub CompareAB()
    Dim a, AData, BData, StartRow As Long, EndRow As Long, i As Long
    StartRow = 51
    EndRow = 239
    AData = Range(Cells(StartRow, 1), Cells(EndRow, 1))
    BData = Range(Cells(StartRow, 2), Cells(EndRow, 2))
    ReDim CData(1 To EndRow - StartRow + 1, 1 To 1)
    i = 0
    For Each a In AData
        i = i + 1
        CData(i, 1) = GetUnique(BData(i, 1) & "", a & "")
    Next
    Range(Cells(StartRow, 3), Cells(EndRow, 3)) = CData
End Sub
 
Upvote 0
Solution
This has StartRow and EndRow hard-coded as 51 and 239. Can be made more dynamic by looking at selection or other ways. Uses your existing function.

VBA Code:
Sub CompareAB()
    Dim a, AData, BData, StartRow As Long, EndRow As Long, i As Long
    StartRow = 51
    EndRow = 239
    AData = Range(Cells(StartRow, 1), Cells(EndRow, 1))
    BData = Range(Cells(StartRow, 2), Cells(EndRow, 2))
    ReDim CData(1 To EndRow - StartRow + 1, 1 To 1)
    i = 0
    For Each a In AData
        i = i + 1
        CData(i, 1) = GetUnique(BData(i, 1) & "", a & "")
    Next
    Range(Cells(StartRow, 3), Cells(EndRow, 3)) = CData
End Sub
Awesome - thanks so much! It works perfectly. (I can work with the start/end rows hard coded). C
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top