Isolating values in a cell.

[Keojey]

Hello,


I'm trying to isolate/ignore the values that get entered in a cell in order to keep things neat within the program I'm making.

Currently, I have multiple values in "Sheet1" going into "Sheet1 Data" with the use of a VBA Macro. These values are then compared to other values within the same sheet and ignored if certain values are equal to each other. In a normal case where there are two equal values, the second one would be ignored when this information gets used in a spreadsheet.

The issue that I'm seeing is that the first value, for example, is "123, 124" and a second value would be just "124." If it happens like this, then nothing would be ignored because the values are not equal and the end result on the spreadsheet would be "123, 124, 124." The ideal would be to have the second value get ignored because the first value has the "124" within its value already and a result of just "123, 124."

Is it possible to do this in any way with or without VBA?

Thank you!

 

[Akuini]

Can you post some data sample showing some "variations" of the data?
Your example: the first value, for example, is "123, 124"
Do you mean it is in a cell & the value are numbers separated by comma & space?
Is it possible the first value & the second value look like this? And what the result should look like?
"124" & "123, 124"
"123, 124, 125" & "124, 125, 126"
"123, 124" & "124, 123"

 

[Keojey]

Sorry if I wasn't clear enough. But you are 100% correct, I would have these variations of data be separated by a comma and a space. There are, however, a few single values that I have working perfectly. Hopefully this explanation can help make a little more sense of it :

So in a sheet, I have two columns of data. As an example, this would be C4:C7 :

123, 124
125
126
127, 128

Then I would have a second column of data that would be pretty similar, but have a few less numbers from F4:F7 :

124
125
126
129

So the end result I want to reach in a spreadsheet would enter data from C4+F4, C5+F5, etc. in such a way as :

123, 124
125
126
127, 128, 129

I currently use a formula like this :

=IF('Data'!C4='Data'!F4, 'Data'!C4, CONCAT('Data'!C4,", ",'Data'!F4))

This would make the C4+F4 cell end up being "123, 124, 124." But the end result I'm trying to reach is so that the 124 doesn't repeat and ends up as a simple "123, 124." This is just a sample of course, the numbers I actually work with are not in chronological order like this and have a few more digits in them.

Thank you!

 

[Akuini]

I don't know how to create formula to solve your problem but if you're ok to use vba then try this code below:
I assume the data start at row 4.
I put the result in col G.

[COLOR=blue]Sub[/COLOR] a1074556a[B]()[/B]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1074556-isolating-values-cell.html[/COLOR][/I]
[COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] rr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] va [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR][B],[/B] vb [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR][B],[/B] vf [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR][B],[/B] x [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
[COLOR=blue]Dim[/COLOR] d [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]
 
rr [B]=[/B] Range[B]([/B][COLOR=brown]"C:C"[/COLOR][B]).[/B]Find[B]([/B][COLOR=brown]"*"[/COLOR][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
va [B]=[/B] Range[B]([/B][COLOR=brown]"C4:C"[/COLOR] [B]&[/B] rr[B])[/B]
vf [B]=[/B] Range[B]([/B][COLOR=brown]"F4:F"[/COLOR] [B]&[/B] rr[B])[/B]
[COLOR=blue]ReDim[/COLOR] vb[B]([/B][B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
   
    [COLOR=blue]For[/COLOR] i [B]=[/B] LBound[B]([/B]va[B])[/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B])[/B]
        [COLOR=blue]Set[/COLOR] d [B]=[/B] CreateObject[B]([/B][COLOR=brown]"scripting.dictionary"[/COLOR][B])[/B]
        d.CompareMode [B]=[/B] vbTextCompare
            [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] x [COLOR=blue]In[/COLOR] Split[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]&[/B] [COLOR=brown]", "[/COLOR] [B]&[/B] vf[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [COLOR=brown]", "[/COLOR][B])[/B]
                d[B]([/B]x[B])[/B] [B]=[/B] [B][COLOR=crimson]0[/COLOR][/B]
            [COLOR=blue]Next[/COLOR]
        vb[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] [COLOR=blue]Join[/COLOR][B]([/B]d.Keys[B],[/B] [COLOR=brown]", "[/COLOR][B])[/B]
    [COLOR=blue]Next[/COLOR]
Range[B]([/B][COLOR=brown]"G4"[/COLOR][B]).[/B]Resize[B]([/B]UBound[B]([/B]vb[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] vb
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Akuini]

Just a thought:
Is it possible that there are some typos in your data like no space or add extra space between numbers.
Ex: '123, 124,125'
If yes, then we need to modify the code.

 

[Keojey]

I will try that formula first thing in the morning when I can and let you know.

But as for your thought, yes. There was actually another issue I was thinking that might pop up. This information gets imputed by two other people, so I'm trying to consider the variation they might add to how they'll enter in these numbers. For example, some of their variations might end up as "123/124" or simply just forgetting a space and making "123,124." I was thinking I could just add a VBA macro to cycle through all the variants they might end up doing, unless you could possibly add something to the code for such a thing as well!

 

[Akuini]

Ok, this code is to deal with the typo in col C & F.
This code will replace all non number character with ", ".
Example:
Before
126/127
127, 128,129,130

Result
126, 127
127, 128, 129, 130

There are 2 Subs, just run 'Sub a1074556rx' it will call the other sub. Then run the previous code (in post #4 ).

[COLOR=blue]Sub[/COLOR] a1074556rx[B]()[/B]
[COLOR=blue]Dim[/COLOR] rr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
rr [B]=[/B] Range[B]([/B][COLOR=brown]"C:C"[/COLOR][B]).[/B]Find[B]([/B][COLOR=brown]"*"[/COLOR][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
[COLOR=blue]Call[/COLOR] a1074556Regex[B]([/B]Range[B]([/B][COLOR=brown]"C4:C"[/COLOR] [B]&[/B] rr[B]))[/B]
[COLOR=blue]Call[/COLOR] a1074556ThereRegex[B]([/B]Range[B]([/B][COLOR=brown]"F4:F"[/COLOR] [B]&[/B] rr[B]))[/B]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
 
 
[COLOR=blue]Sub[/COLOR] a1074556Regex[B]([/B]rng [COLOR=blue]As[/COLOR] Range[B])[/B]
    [COLOR=blue]Dim[/COLOR] strPattern [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]Dim[/COLOR] strReplace [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]Dim[/COLOR] regEx [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]
    [COLOR=blue]Dim[/COLOR] strInput [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]Dim[/COLOR] va
  
va [B]=[/B] rng
    [COLOR=blue]For[/COLOR] i [B]=[/B] LBound[B]([/B]va[B])[/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B])[/B]
        [COLOR=blue]Set[/COLOR] regEx [B]=[/B] CreateObject[B]([/B][COLOR=brown]"VBScript.RegExp"[/COLOR][B])[/B]
        strPattern [B]=[/B] [COLOR=brown]"[^0-9]+"[/COLOR]
        strReplace [B]=[/B] [COLOR=brown]", "[/COLOR]
      strInput [B]=[/B] Trim[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))[/B]
        [COLOR=blue]With[/COLOR] regEx
            [B].[/B][COLOR=blue]Global[/COLOR] [B]=[/B] [COLOR=blue]True[/COLOR]
            [B].[/B]MultiLine [B]=[/B] [COLOR=blue]True[/COLOR]
            [B].[/B]IgnoreCase [B]=[/B] [COLOR=blue]True[/COLOR]
            [B].[/B]Pattern [B]=[/B] strPattern
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
        [COLOR=blue]If[/COLOR] regEx.Test[B]([/B]strInput[B])[/B] [COLOR=blue]Then[/COLOR]
           va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] regEx.Replace[B]([/B]strInput[B],[/B] strReplace[B])[/B]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR]
rng [B]=[/B] va
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Keojey]

That would be perfect! But.. when I try to run that code, it gets an error saying the Sub or Function is not defined for the "Call a1074556ThereRegex(Range("F4:F" & rr))" line. When I tried adding the other code from post #4 it did the same thing, so I thought maybe changing the sub name to be the "a1074556ThereRegex" would do something. But it gave me an error saying: "Wrong number of arguments or invalid property assignment."

Did I miss something weird or is there something missing with the a1074556ThereRegex I need to add somewhere?
Also! I do this comparison over several columns all the way down to column FG, is there a way I could sneak a few ways to have this code repeat down for more columns?

 

[Akuini]

Sorry, that line should be:
Call a1074556Regex(Range("F4:F" & rr))

Also! I do this comparison over several columns all the way down to
column FG, is there a way I could sneak a few ways to have this code
repeat down for more columns?

I believe that can be done.
Just try it first, see what happen.

 

[Keojey]

Okay, so I see how this is working. So the code from post#7 will try to fix everything then the code from #4 will do the text comparisons.

So #7 worked with the initial thought of changing most variances that I tried, other than when they add no spaces such as: "123,124" making it so the code from #4 won't pick it up and submit as : "123,124, 124"

Also, is there a way to put a cap on the code where it puts the results from #4 ? Like have the range end at G23? Otherwise it looks to all be working wonders so far!

Edit : It seems having "123,124" wont change, but if I add a few more numbers and make it "123, 124,125" then it will get corrected. Just having two digits being separated by only a comma doesn't seem to work.
... Probably because it's seeing it as one big number... So this shouldn't be an issue at all!