Announcement

Collapse
No announcement yet.

Check & Add Sequential Numbers In Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Check & Add Sequential Numbers In Column

    This is probably an easy problem for everyone but I have no clue.

    I have two columns A and B filled with numbers. Column A has the identifier number and Column B has number that represents intensity for the data set.

    Column A can be anything from 75-1000, however, sometimes the adjacent rows are not sequential (i.e. 101, 102, 104).

    1) Is there a way to automatically check for skipping numbers in Column A.
    2) When it finds a skipped number, insert a row for column A and B.
    3) Then put the correct number in Column A (i.e. 103) then put 0 in for column B

    Any help would be tremendous.
    I'm studying chemistry and I need to sort thousands of data sets in order to input them for statistical analysis.

    Jerry-

  • #2
    Re: Checking/adding Numbers In Sequential Rowsss

    Hi,

    Here's something from my "need to crunch these numbers so often might as well write a macro for it" libraries, slightly modified for your setup.

    Doesn't do exactly what you asked, as it creates a new sheet instead. Figured this would work for you as well.

    Code:
    Public Sub GetOrderedList_CreateSheet()
    
        Dim wksSource As Excel.Worksheet
        Dim wksTarget As Excel.Worksheet
        
        Dim rngSource As Excel.Range
        Dim rngTarget As Excel.Range
        
        Dim lMin As Long
        Dim lMax As Long
        Dim lRows As Long
    
        Set wksSource = ActiveSheet
        
        'Get reference to source range
        With wksSource.Columns(1)
            Set rngSource = wksSource.Range(.Cells(1), .Cells(.Cells.Count).End(xlUp))
        End With
        
        'Get start and end values for list
        lMin = Application.WorksheetFunction.Min(rngSource)
        lMax = Application.WorksheetFunction.Max(rngSource)
        
        'Check that there are numbers to generate list from
        lRows = lMax - lMin
        If lRows = 0 Or lRows > wksSource.Rows.Count Then Exit Sub
        
        Set rngSource = rngSource.Resize(rngSource.Rows.Count, 2)
        
        'Create new worksheet
        Set wksTarget = ThisWorkbook.Worksheets.Add()
        wksTarget.Name = "Ordered List"
        
        'Get reference to the target range
        Set rngTarget = wksTarget.Range(wksTarget.Cells(1, 1), wksTarget.Cells(lRows + 1, 2))
    
        With rngTarget
        
            'Put in initial values
            .Cells(1, 1).Value = lMin
            .Cells(1, 2).FormulaR1C1 = "=VLOOKUP(RC[-1]," & rngSource.Address(True, True, xlR1C1, True) & ",2,0)"
               
            'Fill down consecutive numbers
            rngTarget.Columns(1).DataSeries _
                    Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
                    Step:=1, Trend:=False
            
            'Fill down formulas
            .Columns(2).FillDown
            
            'Replace errors (lookup keys not found) with zeroes
            On Error Resume Next
            .SpecialCells(xlCellTypeFormulas, 16).Value = 0
            On Error GoTo 0
            
            'Convert formulas to values
            .Value = .Value
            
        End With
      
    End Sub

    Comment


    • #3
      Re: Check & Add Sequential Numbers In Column

      Hi,
      Thank you for the code. I've been trying to use it (I'm not at all good at code or macros or vba). I've been searching all over on how to load it.

      finally opened up visual basic editor.
      inserted code.
      configured my macro button.

      nothing....data wasn't in the first two columns.

      once the data was loaded into the column A and B it works great...

      I thought i had to highlight all the numbers but once loaded into the A and B
      it spits them out exactly how i wanted it.

      now i just transfer data in column A and B (a burden, but not nearly as close to doing it by eye.)
      and hit my macro button.
      thanks...

      Comment


      • #4
        Re: Check & Add Sequential Numbers In Column

        The solution given was based on your original statement that
        Originally posted by jerrychem
        I have two columns A and B filled with numbers
        Then you stated
        Originally posted by jerrychem
        now i just transfer data in column A and B(a burden...
        If your data wasn't in Columns A and B, you shouldn't have stated that it was.

        Comment


        • #5
          Re: Check & Add Sequential Numbers In Column

          oh, i'm not complaining. don't take it the wrong way... i'm very thank you to the person.
          i just wanted to say thank you to who provided the code.

          Comment


          • #6
            Re: Check & Add Sequential Numbers In Column

            My point is this:
            If you had told us where your data actually was, instead of stating that it was in Columns A and B when this is clearly not the case, then the solution would have worked without your having to move the data to Columns A and B first.

            Comment


            • #7
              Re: Check & Add Sequential Numbers In Column

              well..not a big deal...thank you again Fencliff.

              I thought when I said I had thousands of data sets within a spreadsheet, and where A and B can only consist of 2 columns, then I figured it was assumed that the data did not only exist in columns A and B. My mistake then. I was just giving an example since my data starts from Column A and runs hundreds of columns.

              It was an example when I was asking for help. I was just replying to say think you to Fencliff and how much faster it is with his code than doing it by hand. It was a compliment to Fencliff.

              Lighten up turtle44. I'm okay with moving the columns. So if I'm the one doing the work, and it's no big deal to me, don't make it a big deal. So no more points need to be made, since I'm the one doing the work.

              Comment


              • #8
                Re: Check & Add Sequential Numbers In Column

                Lighten up turtle44. I'm okay with moving the columns. So if I'm the one doing the work, and it's no big deal to me, don't make it a big deal. So no more points need to be made, since I'm the one doing the work.
                No, turtle44 is also doing your work and made a simple and valid request. It's really not that hard and helps us help you.

                Comment


                • #9
                  Re: Check & Add Sequential Numbers In Column

                  I don't won't to turn this thread in a war or words, so I will make the following points once again then consider this matter closed.
                  Originally posted by jerrychem
                  I thought when I said I had thousands of data sets within a spreadsheet, and where A and B can only consist of 2 columns, then I figured it was assumed that the data did not only exist in columns A and B.
                  I wouldn't assume that for the following reasons:
                  1) A data set could easily consist of only 2 columns, so having thousands of them could easily mean that you have 2 columns and thousands of rows
                  2) Since your version of Excel has only 256 columns, "having thousands of data sets" obviously doesn't mean that you have thousands of columns
                  3) You explicitly stated that your data was in Columns A and B.
                  Originally posted by jerrychem
                  Lighten up turtle44. I'm okay with moving the columns.
                  You called moving the columns a "burden" (your word, not mine), so I was simply trying to help you by stating that if you had given an actual description of your problem, then the solution probably would have been tailored to fit your needs without the burden of moving data. And for the record, there is no need for me to lighten up as I am perfectly calm and was simply trying to help.

                  Comment

                  Working...
                  X