Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Check & Add Sequential Numbers In Column

  1. #1
    Join Date
    26th January 2008
    Posts
    4

    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-

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th July 2007
    Posts
    209

    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.

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2008
    Posts
    4

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2007
    Posts
    759

    Re: Check & Add Sequential Numbers In Column

    The solution given was based on your original statement that
    Quote Originally Posted by jerrychem
    I have two columns A and B filled with numbers
    Then you stated
    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th January 2008
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2007
    Posts
    759

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2008
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    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.

  9. #9
    Join Date
    14th July 2007
    Posts
    759

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Check Column Against List Of Numbers
    By Laminar in forum EXCEL HELP
    Replies: 9
    Last Post: June 26th, 2008, 06:53
  2. Enter Sequential & Random Numbers
    By PaulJChandler in forum EXCEL HELP
    Replies: 6
    Last Post: February 2nd, 2008, 10:30
  3. Add Sequential Numbers To Range
    By Jeff P in forum EXCEL HELP
    Replies: 11
    Last Post: August 17th, 2007, 15:25
  4. Check/Add Unique Non-Sequential Number
    By tlwatkins in forum EXCEL HELP
    Replies: 4
    Last Post: August 27th, 2006, 11:22
  5. Add Sequential Numbers to List Items
    By excel_for_fun in forum EXCEL HELP
    Replies: 3
    Last Post: September 20th, 2004, 11:09

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno