Is it possible to remove unnecessary textual deliminated cell contents with VBA?

[Isis84]

I am trying to reconcile two lists containing task numbers in an XLS spreadsheet, and would like to sort both lists in ascending order and highlight the duplicates. The problem is that these two lists are copied and pasted into an Excel worksheet from two different applications, and one application sometimes includes multiple tasks in the same field as the task of interest. These other tasks are unnecessary and prevent me from sorting the list by the task of interest as they appear first in the cell and begin with a different prefix. (This is due to the manner in which the data gets entered into that particular application, and there is no hope of changing our procedure for that at this time.)

For example, List 1 includes the following tasks: A1(XXX-12345); A2(XXX-56789); A3(XXX-112233), etc. In other words, all tasks in List 1 contain the prefix "XXX" with a unique task number following it.

List 2 may look like this: B1(YYY-12345; ZZZ-56789; XXX-12345); B2(ZZZ-12345; XXX-112233); B3(XXX-123444); etc.

To reiterate, my goal is to remove all of the unnecessary "YYY-" and "ZZZ-" tasks from List 2, so that I may be able to sort that column by the "XXX-" tasks, only. My first thought was to try and utilize the Find and Replace functionality offered by Excel; however, this seems too complex for this functionality. Is this possible using VBA?

The only alternative solution that I could come up with is to select the column containing List 2, utilize the Convert Text to Column Wizard to separate the delimited tasks into multiple columns, sort the columns alphabetically, and manually delete unnecessary tasks. (This is very messy, and I am certain there is an easier way to do this; or rather, have Excel do this for me.) I would greatly appreciate any help and/or suggestions!

 

[etaf]

do all the numbers start with XXX?
are they always the last entry and how many characters

then in a new column do the following

=MID(A1,FIND("XXX", A1,1), LEN(A1)-FIND("XXX", A1,1)+1)
and copy down

then copy that column and paste > special - now you will have just the XXX values

 

[wigi]

A oneliner in VBA (or almost oneliner) is oftentimes an option too:

Sub WIGI()

    For Each r In Columns(2).SpecialCells(2, 2)
        r.Value = Trim(Join(Filter(Split(r, ";"), "XXX", 1), ";"))
    Next

End Sub

 

[hiker95]

Isis84,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

If I understand you correctly.

Sample raw data:

Excel 2007
	A	B
1	XXX-12345	YYY-12345; ZZZ-56789; XXX-12345
2	XXX-56789	ZZZ-12345; XXX-112233
3	XXX-112233	XXX-123444
4
5
6
7
Sheet1

After the macro:

Excel 2007
	A	B
1	12345	12345
2	56789	12345
3	112233	12345
4		56789
5		112233
6		123444
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExtractNumbers()
' hiker95, 12/24/2013
' http://www.mrexcel.com/forum/excel-questions/746887-possible-remove-unnecessary-textual-deliminated-cell-contents-visual-basic-applications.html
Dim o, c As Range, s1, s2, i As Long, ii As Long, n As Long
n = 1
ReDim o(1 To n)
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If InStr(c, "; ") Then
    s1 = Split(c, "; ")
    For i = LBound(s1) To UBound(s1)
      s2 = Split(s1(i), "-")
      ReDim Preserve o(1 To n)
      o(n) = s2(1)
      n = n + 1
    Next i
  Else
    s1 = Split(c, "-")
    ReDim Preserve o(1 To n)
    o(n) = s1(1)
    n = n + 1
  End If
Next c
Range("A1", Range("A" & Rows.Count).End(xlUp)).ClearContents
Range("A1").Resize(n - 1) = Application.Transpose(o)
Range("A1:A" & n - 1).Sort key1:=Range("A1"), order1:=1
n = 1
ReDim o(1 To n)
For Each c In Range("B1", Range("B" & Rows.Count).End(xlUp))
  If InStr(c, "; ") Then
    s1 = Split(c, "; ")
    For i = LBound(s1) To UBound(s1)
      s2 = Split(s1(i), "-")
      ReDim Preserve o(1 To n)
      o(n) = s2(1)
      n = n + 1
    Next i
  Else
    s1 = Split(c, "-")
    ReDim Preserve o(1 To n)
    o(n) = s1(1)
    n = n + 1
  End If
Next c
Range("B1", Range("B" & Rows.Count).End(xlUp)).ClearContents
Range("B1").Resize(n - 1) = Application.Transpose(o)
Range("B1:B" & n - 1).Sort key1:=Range("B1"), order1:=1
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractNumbers macro.


If the above is correct, then what next did you want to do the data?

 

[Isis84]

Hi Wayne,<br/><br/>Yes, the numbers always begin with the same prefix. It is a three character prefix, and "XXX" was merely used as a general example. The number of characters following the "XXX-" varies.<br/><br/>Thank you so much for providing that calculation! It works like a charm. After reading all responses and trying out the different suggestions, I now have a couple of different methods to utilize to facilitate the process of reconciling my lists.<br/><br/>Best Regards,<br/>Isis

 

[Isis84]

Hi Wigi,<br/><br/>Thank you so much for the almost oneliner. It works perfectly, and the ease of running one macro to accomplish my goal of editing all the data in my xls worksheet to only display the tasks beginning with the prefix "XXX" is a beautiful thing. I greatly appreciate everyone's time and speed in responding. I certainly have a lot to learn about VBA code.<br/><br/>Best Regards,<br/>Isis

 

[hiker95]

Isis84,

After reading all responses and trying out the different suggestions, I now have a couple of different methods to utilize to facilitate the process of reconciling my lists.

Thanks for the feedback.

You are very welcome. Glad we could help.

And, please come back to this forum for any other questions you might have.

 

[Isis84]

Hi hiker95,<br/><br/>I am working out of Excel 2010, version 14.0.6129.5000, and using Windows 7. I will try to remember to include that information in future posts.<br/><br/>Thank you for providing your code. It worked as you stated; however, I need to keep the prefixes attached to the numerical values, as further investigation is necessary after reconciling the lists, and the prefix tells me what application I will need to open the task. After running a macro, or utilizing a calculation, to filter out all but the "XXX-" tasks, I will be highlighting the duplicate task numbers via Conditional Formatting in Excel, and sorting the columns by cell color to get those without a matching task in the other column on top.<br/><br/>As you read, I have found a couple of different solutions, so I do not believe you need to worry about spending anymore of your time on this. I appreciate all of your help!<br/><br/>Thanks again,<br/>Isis

 

[Isis84]

Hi Wigi,<br/><br/>Would you mind writing a second macro, or including this in the first, to delete duplicate values from List 1 and List 2? As you can read in my response to hiker95, after running the macro to filter out all but the "XXX-" tasks, I will be highlighting the duplicate task numbers via Conditional Formatting in Excel, and sorting the columns by cell color to get those without a matching task in the other column on top. Having a macro to do the second portion of my analysis would be very helpful.<br/><br/>Thanks,<br/>Isis

 

[wigi]

Hello Isis

Please stick to the adagium of "1 topic = 1 question".
When there is a new question, please start a new topic.