Check & Add Sequential Numbers In Column

[jerrychem]

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-

[Fencliff]

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.

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 

[jerrychem]

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...

[turtle44]

The solution given was based on your original statement that

I have two columns A and B filled with numbers

Then you stated

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.

[jerrychem]

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.

[turtle44]

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.

[jerrychem]

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.

[Dave Hawley]

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.

[turtle44]

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.

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.

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.