Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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. Senior Member
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
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

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. I agreed to these rules
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. Established Member
Join Date
14th July 2007
Posts
759

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.

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
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. Established Member
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. I agreed to these rules
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. 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. Established Member
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.
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.

Excel Video Tutorials / Excel Dashboards Reports

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

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