Isolating values in a cell.

Keojey · Oct 17, 2018

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!

Keojey · Oct 20, 2018

*Sorry for double posting, I can't edit my post anymore*

For putting the cap on #4 like I was saying, would there be a way to jump over certain cells as well? Some of the numbers I work with have a few dashes and stars in them that don't see any multiple numbers like this, so they shouldn't have any issues like this. So get a weird range like "G4:G10, G12:G15,G18:G23" or the such?

Otherwise, everything looks perfect other than being able to have the code do it over more columns at once!

Akuini · Oct 20, 2018

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"

Do you mean when you run the code #7, when there was no space then it didn't change to have one space?
I tried using my own sample, and got the result as expected.
The first line (row 4), the data has no space, then after run code #7, a space is added.

BEFORE
Excel 2007 32 bit

	C	F
4	123,124,125	123
5	125\126	127
6	126	126
7	127, 128,129	128, 129

Sheet: Sheet1

AFTER RUNNING CODE #7
Excel 2007 32 bit

	C	F
4	123, 124, 125	123
5	125, 126	127
6	126	126
7	127, 128, 129	128, 129

Sheet: Sheet1

AFTER RUNNING CODE #4

Excel 2007 32 bit

	C	F	G
4	123, 124, 125	123	123, 124, 125
5	125, 126	127	125, 126, 127
6	126	126	126
7	127, 128, 129	128, 129	127, 128, 129

Sheet: Sheet1

Akuini · Oct 20, 2018

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!

Hmm, try changing the cell format to TEXT.

Akuini · Oct 20, 2018

Some of the numbers I work with have a few dashes and stars in them that don't see any multiple numbers like this, so they shouldn't have any issues like this. So get a weird range like "G4:G10, G12:G15,G18:G23" or the such?

I don't quite understand this, can you post some sample showing the data sample & the expected result.

Otherwise, everything looks perfect other than being able to have the code do it over more columns at once!

Can you specify the columns?

Keojey · Oct 20, 2018

My bad, I was able to get everything from #7 to do what it was suppose to do. Thank you for that!

I'm not quite sure how you make the box you did so I'll just give you a list of some live data that I have in my actual page. Most of them are just single quantities, but there is a rare occurrence of this issue we're trying to resolve.

So in Column U I from U4 down to U10 with this data respectively :
202904-*-*
9336956
8762156
1331912
190133-*-*
195524-*-*
146676

In Column X I have this these quantities from X4 to X10 :
202904-*-*
9336956/9336957
8762156/8762157
13319121
190133-*-*
195524-*-*
146676

Using your VBA code, I would like the range to go from U5 to U7, then U10 down to other numbers until it hits U24. Skipping over U4 and U8:U9 because those numbers will always be the same and never have more than one set of numbers. But this would allow the above to turn into this :
202904-*-*
9336956, 9336957
8762156, 8762157
13119121
190133-*-*
195524-*-*
146676
Just to make things a little less complex, we can have the results go down into a chart below. So for C&F, it can put it's results in B60. For U&X it can go into E60.

All of the columns I have being compared are C&F, I&L, O&R, U&X, AA&AD, AG&AJ, AM&AP, AS&AV, AY&BB, BE&BH, BK&BN, BQ&BT, BW&BZ, CC&CF, CI&CL, CO&CR, CU&CX, DA&DD, DG&DJ, DM&DP, DS&DV, DY&EB, EE&EH, EK&EN, EQ&ET, EW&EZ, FC&FF.

I know there's a lot to go through and it's tedious to have the code repeat for all of these, if you have the code so it even repeats for two of these, then I can go ahead and do all the tedious work to have to go down for all of these!

Akuini · Oct 20, 2018

Ok, I'll get back to you tomorrow, it's past midnight in my part of the world.

But quick question:
1. So in every column you mention above, row 4,8 & 9 always have the ones with -*-* and it don't need to change?
2. Is your data a fixed range? from row 4 to 24?

Keojey · Oct 20, 2018

That's fine! But I won't be able to work on this tomorrow, so I'll let you know what kinds of results I get as soon as I can. Thank you for all the help!

There are a few more rows that will "-*-*" and there's one that has a 325965/1/A. If the coding isn't too complicated I can just modify it to match up, right? I was actually going to use this for two different sheets that has two different sets of data. One of them goes from 4 to 24, the other goes from 4 to 23.

The first sheet goes from row 4 to 24 and the specific rows that have -*-* are 4, 8 and 9. Row 24 has the /1/A at the end.

On the second sheet, it goes from row 4 to 23. Row 14 and 15 have decimals in it's number. Row 16 has the -*-*. And Row 23 has the /1/A.

These are fixed ranges that should never alter and are always true.

Akuini · Oct 21, 2018

Ok, I changed the codes so:
It will loop through col C&F to FC&FF .
It will skip any cell with "-*" or "/1/A" .

Run Sub a1074556rx to clean up typos then Sub a1074556b to join the data in 2 columns.

Code:

[COLOR=blue]Sub[/COLOR] a1074556rx[B]()[/B]
[COLOR=blue]Dim[/COLOR] rr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
 
[COLOR=blue]For[/COLOR] x [B]=[/B] [B][COLOR=crimson]3[/COLOR][/B] [COLOR=blue]To[/COLOR] [B][COLOR=crimson]159[/COLOR][/B] [COLOR=blue]Step[/COLOR] [B][COLOR=crimson]6[/COLOR][/B] [I][COLOR=seagreen]' looping: 3 is col C & 159 is col AF[/COLOR][/I]
[I][COLOR=seagreen]'rr is last row with data[/COLOR][/I]
rr [B]=[/B] Columns[B]([/B]x[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]If[/COLOR] rr [B]>[/B] [B][COLOR=crimson]3[/COLOR][/B] [COLOR=blue]Then[/COLOR] [I][COLOR=seagreen]'row 3 is the header, if no data available below it then the column will be skipped[/COLOR][/I]
[COLOR=blue]Call[/COLOR] a1074556Regex[B]([/B]Range[B]([/B]Cells[B]([/B][B][COLOR=crimson]4[/COLOR][/B][B],[/B] x[B]),[/B] Cells[B]([/B]rr[B],[/B] x[B])))[/B]
[COLOR=blue]Call[/COLOR] a1074556Regex[B]([/B]Range[B]([/B]Cells[B]([/B][B][COLOR=crimson]4[/COLOR][/B][B],[/B] x [B]+[/B] [B][COLOR=crimson]3[/COLOR][/B][B]),[/B] Cells[B]([/B]rr[B],[/B] x [B]+[/B] [B][COLOR=crimson]3[/COLOR][/B][B])))[/B]
[COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]Next[/COLOR]
 
[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]
  
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]If[/COLOR] InStr[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [COLOR=brown]"-*"[/COLOR][B])[/B] [COLOR=blue]Or[/COLOR] InStr[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [COLOR=brown]"/1/A"[/COLOR][B])[/B] [COLOR=blue]Then[/COLOR]
    [I][COLOR=seagreen]'do nothing, It will skip any cell with "-*" or "/1/A"[/COLOR][/I]
   
    [COLOR=blue]Else[/COLOR]
        [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]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR]
rng [B]=[/B] va
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Code:

[COLOR=blue]Sub[/COLOR] a1074556b[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]
 
[COLOR=blue]For[/COLOR] x [B]=[/B] [B][COLOR=crimson]3[/COLOR][/B] [COLOR=blue]To[/COLOR] [B][COLOR=crimson]159[/COLOR][/B] [COLOR=blue]Step[/COLOR] [B][COLOR=crimson]6[/COLOR][/B]  [I][COLOR=seagreen]' looping: 3 is col C & 159 is col AF[/COLOR][/I]
    rr [B]=[/B] Columns[B]([/B]x[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]If[/COLOR] rr [B]>[/B] [B][COLOR=crimson]3[/COLOR][/B] [COLOR=blue]Then[/COLOR] [I][COLOR=seagreen]'row 3 is the header, if no data available below it then the column will be skipped[/COLOR][/I]
        va [B]=[/B] Range[B]([/B]Cells[B]([/B][B][COLOR=crimson]4[/COLOR][/B][B],[/B] x[B]),[/B] Cells[B]([/B]rr[B],[/B] x[B]))[/B]
        vf [B]=[/B] Range[B]([/B]Cells[B]([/B][B][COLOR=crimson]4[/COLOR][/B][B],[/B] x [B]+[/B] [B][COLOR=crimson]3[/COLOR][/B][B]),[/B] Cells[B]([/B]rr[B],[/B] x [B]+[/B] [B][COLOR=crimson]3[/COLOR][/B][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]If[/COLOR] InStr[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [COLOR=brown]"-*"[/COLOR][B])[/B] [COLOR=blue]Or[/COLOR] InStr[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [COLOR=brown]"/1/A"[/COLOR][B])[/B] [COLOR=blue]Then[/COLOR]
                vb[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
           
            [COLOR=blue]Else[/COLOR]
                [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] z [COLOR=blue]In[/COLOR] Split[B]([/B]WorksheetFunction.Trim[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))[/B] [B]&[/B] [COLOR=brown]", "[/COLOR] [B]&[/B] _
                    WorksheetFunction.Trim[B]([/B]vf[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])),[/B] [COLOR=brown]", "[/COLOR][B])[/B]
                        d[B]([/B]z[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]End[/COLOR] [COLOR=blue]If[/COLOR]
            [COLOR=blue]Next[/COLOR]
       
        Range[B]([/B]Cells[B]([/B][B][COLOR=crimson]4[/COLOR][/B][B],[/B] x [B]+[/B] [B][COLOR=crimson]4[/COLOR][/B][B]),[/B] Cells[B]([/B][B][COLOR=crimson]4[/COLOR][/B][B],[/B] x [B]+[/B] [B][COLOR=crimson]4[/COLOR][/B][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]If[/COLOR]
[COLOR=blue]Next[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Keojey · Oct 22, 2018

So initially trying this it didn't work because the line
'rr = Columns(x).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row'
Would give me error saying the "Object variable or With block variable not set" unless I set the value of 'x' to anywhere between 1 and 10? So it works, but could you possibly explain why it won't work unless the value of 'x' is set between these numbers? You said 'rr' is equal to the last row, so I first tried setting 'x' to 23 and 24 respectively, but had the above results.
On that note, there are some weird inconsistencies when I use the same code for two sheets. On the first sheet, both codes will only work between row 4 and 24. But on the second sheet, it'll go all the way to row 52 for the first code and way past Row 80 with the second code. Is there not a variable to change which row it should end on?

Keojey · Oct 22, 2018

Keojey said:
On that note, there are some weird inconsistencies when I use the same code for two sheets. On the first sheet, both codes will only work between row 4 and 24. But on the second sheet, it'll go all the way to row 52 for the first code and way past Row 80 with the second code. Is there not a variable to change which row it should end on?

Scratch this, I figured this part out. If column A has any information in it, everything to the right of it will fall under this code.

Isolating values in a cell.

Keojey

New Member

Keojey

New Member

Excel Facts

Akuini

Well-known Member

Akuini

Well-known Member

Akuini

Well-known Member

Keojey

New Member

Akuini

Well-known Member

Keojey

New Member

Akuini

Well-known Member

Keojey

New Member

Keojey

New Member

Similar threads

Forum statistics

Share this page

Isolating values in a cell.

New Member

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock