1

I have a column that contains the final output data that I am interested in processing, the data in this column gives results that fall into groups that are very close in value. Once I sort them in ascending order they look as the below example:

Values
  1.1
  1.0
  1.3
  3.3
  3.1
  3.5
  8.7
  8.8
  8.8

The discrepancy in value depends on the calculation, but they are usually pretty close. Is there a way of splitting this column automatically into an array like the one below?

Value 1   Value 2   Value 3
  1.1       3.3       8.7
  1.0       3.1       8.8
  1.3       3.5       8.8

The number of results that fall in each "group" is not predictable but the minimum difference between groups is.

is there a function that can help?

Realistic Data sorted into groups after macro usage:

3.314496   4.707067   5.765178   6.659030   7.449414   8.155307   9.981672   10.527740
3.315203   4.709271   5.765736   6.660503   7.449503   8.157916   9.981750   10.528012
3.315863   4.710029   5.766142   6.660533   7.449609   8.157919   9.982425   10.529845
3.315932   4.710119   5.766472   6.660641   7.449958   8.159919   9.982623   10.531364
3.316198   4.710421   5.766765   6.660654   7.451202   8.160223   9.984346   10.531996
3.316321   4.712422   5.766781   6.661423   7.451525   8.808907   9.984503   10.532077
3.316874              5.767053   6.662583   7.452031   8.809137   9.985143   10.532135
3.317010              5.767273   6.663421   7.452424   8.809198   9.986184   10.532675
3.317121              5.767418   6.663555   7.452803   8.810217   9.986405   10.532898
3.317911              5.767530              7.452806   8.810217   9.986435   10.533993
3.318345              5.767669              7.453056   8.810876   9.986791   10.535355
3.319082              5.768146              7.453588   8.811238   9.986828   10.535844
                      5.768337              7.453770   8.811370   9.986854   
                      5.768448              7.453845   8.811435   9.987180   
                      5.768824              7.453880   8.811577   9.987482   
                      5.768931              7.453912   8.812060   9.987610   
                      5.768971              7.453966   8.812245   9.987809   
                      5.769521              7.453979   8.812363   9.987816   
                      5.769725              7.454064   8.812508   9.987951   
                      5.769920              7.454538   8.812781   9.988456   
                      5.770123              7.455970   8.813038   9.990445   
                      5.770187              7.456215   8.813130   9.990558   
                      5.771034              7.456421   8.813236   9.990600   
                      5.771230              7.456464   8.813414   9.991091   
                                                       8.814133      
                                                       8.814462      
                                                       8.814524      
                                                       8.814553      
                                                       8.815093      
  • 1
    Is it just coincidence, or are the 'discrepancies' all to the right of the decimal and the group identified by the value to the left of the decimal? – geoB May 19 '14 at 0:58
  • No it is not that is representative of the data case. Sometimes in a group they may vary by the second decimal place sometimes the first as well. – Fiztban May 19 '14 at 7:04
  • 1
    Is the minimum difference between groups known before output or must it be determined afterwards by inspection? – geoB May 19 '14 at 12:20
  • Hi @geoB, no it is not, I have a vague idea of what to expect, but sometimes have to test it to visually determine (wrt my operations) whether the difference vouches for a new data group or not. The data you see are vector magnitudes of relative atom distances from one that I choose in a 3d atom array. I am dividing them into distances associated to nearest neighbour atom groups. – Fiztban May 19 '14 at 15:09
2

EDIT

Based upon your comments this is what you're looking for as you can set the threshold (the difference between 2 numbers that creates a new 'group')

Sub Button1_Click()

Dim threshold As Double
threshold = 0.4    ' UPDATE THIS AS YOU SEE FIT

Dim column As Integer
column = 66 ' assumes the next column is B. 65 = A, 66=B, 67= C etc

Dim aRow As Integer
aRow = 1 ' The starting row where the data is

Dim otherRow As Integer
otherRow = aRow

Dim previousCol As Integer
previousCol = 64


Do While (True)

    If (column = 90) Then
        previousCol = previousCol + 1
        column = 65
    End If


    If (Range("A" & aRow).Value = "") Then
        Exit Do
    End If

    If (aRow <> 1) Then

        If Range("A" & aRow).Value - Range("A" & aRow - 1).Value >= threshold Then

            otherRow = 1
            column = column + 1

        End If

    End If


    If (previousCol < 65) Then
        Range(Chr(column) & otherRow).Value = Range("A" & aRow).Value
    Else
        Range(Chr(previousCol) & Chr(column) & otherRow).Value = Range("A" & aRow).Value
    End If

    aRow = aRow + 1
    otherRow = otherRow + 1

Loop

End Sub

The only difference now is, you can set the threshold in the code (see above) but it no longer removes the original values from Column A (see screen shot below). You can remove this manually I guess.

The above is also limited to column ZZ, which gives you about 700 columns

enter image description here


Previous answer for history:

This macro does what you want

Sub Button1_Click()

Dim column As Integer
column = 66

Dim aRow As Integer
aRow = 1

Dim otherRow As Integer
otherRow = 1

Dim current As Integer
current = -99

Do While (True)

    If (Range("A" & aRow).Value = "") Then
        Exit Do
    End If

    If (current < 0) Then
        current = Split(Range("A" & aRow), ".")(0)
    End If

    If (Split(Range("A" & aRow), ".")(0) <> current) Then
        current = Split(Range("A" & aRow), ".")(0)
        otherRow = 1
        column = column + 1
    End If

    Range(Chr(column) & otherRow).Value = Range("A" & aRow).Value
    Range("A" & aRow).Value = "" 'Clear A column

    otherRow = otherRow + 1
    aRow = aRow + 1

Loop

End Sub

Before

enter image description here

After

enter image description here

So, things to note about this are:

  1. It has assumed the A column has all the content and that there will be empty cells (empty cell will indicate end of the list)
  2. It splits on the decimal (.). If no decimal is present, it will treat it as N.0
  3. Column A becomes an empty column, but I'll assume it's no hardship to delete the column afterwards ... Or, as per the code, you could comment out the line Range("A" & aRow).Value = "" 'Clear A column which will keep column A as is
  4. In the example you've given, you have 3 entries per number group (3 started with 1.N, 3 started with 3.N and 3 started with 8.N). This doesn't need it to be in groups of 3
  5. It assumes your list is already sorted
  • 1
    OK, and again for me being slow, can you please explain which part is incorrect ... Or are you saying that if the distance between values is what should cause the split. EG 1.01 -> 1.05 can remain on the same column, but 1.01 and 1.81 has a bigger distance and so should be split? If so what is the threshold. (Sorry, I'm still lost here as I can't see why it doesn't do what you were hoping. Maybe if I said what is the minimum value between 2 numbers which means it's a new group makes more sense?) – Dave May 19 '14 at 13:07
  • 1
    @Fiztban, all done. Let me know if the change works and how important removing ColumnA is (or isn't) – Dave May 19 '14 at 13:47
  • 1
    Dave, it works beautifully, I simply add a Selection.Copy Sheets.Add After:=ActiveSheet ActiveSheet.Paste section in front of your script and have the data copied to a new sheet for me to view so that I preserve the original anyway so it is useful but not essential. Thank you for your help :) you made my job easier, I will be reading through it to learn how you did it. Thank you again – Fiztban May 19 '14 at 15:07
  • 1
    Do you mean of the column is AA or AB? – Dave May 19 '14 at 18:28
  • 1
    @Fiztban the first source block is updated. It is limited to about 700 columns... – Dave May 20 '14 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.