I have an Excel sheet containing thousands of rows. Each row has a start value in column A and an end value in column B. I need to generate all the unique values in the range.

For example, for start value = 1000 and end value equals 1199 I need to generate all then numbers between 1000 and 1199 inclusive. For start value = 1 and end value = 5 it’d be 1, 2, 3, 4, 5

I can’t find a decent way to do it in Excel – any ideas?

VBA?

When you say ‘generate,’ what do you mean? What are you going to do with 200 values from a single row, write one per cell? Stick them all in one huge one? Something else?

And I suppose the other question is, why?

Is whatever you’re trying to achieve not better approached by whatever the Excel equivalent is of a FOR… DO loop using A and B as endpoints? FOR f=A to B; DO [thing] with f; NEXT f.

It’s a list of obsolete terminal id ranges. It will be the input for a mainframe program that expects one value per line – it’ll then delete that terminal record.

VBA would work or exporting it as a CSV and writing a perl, python, java or javascript program

Right. I think I’d concur then that the approach I’d be investigating there would be to save the spreadsheet as a CSV and then iterate through it with a bash script.

Hang on while I phone a friend.

This is what said friend came back with.

#!/bin/bash
infile="$1"

OIFS=$IFS
IFS=","

if [ -z "$infile" ]; then echo "Syntax: $0 infile"; exit 1; fi
if [ ! -f "$infile" ]; then echo "Filename $infile does not exist."; exit 1; fi

while read rangestart rangeend; do
        seq $rangestart $rangeend
done < $infile

IFS=$OIFS

“sends to STDOUT, pipe to whatever file”

whats the biggest range?

firt colum to the right concatenate everything to the right ,

next column =start value

next column something like =if(cell to left-end value>0,”,”&cell to the left+1,””)

sprinkle som $ in to lock the start end vlaues, then drag it acrosss however many columns are needed

you concantenated column should be sorted

thats top of my head stuff that.

Thanks both, just knocked up some shonky VBA that works

Dim tid, startValue, endValue As Integer
Dim inputRows, currentRow, nextRow As Long
Sub TidGenerate()
inputRows = Worksheets(“Sheet1”).Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For currentRow = 1 To inputRows
startValue = Worksheets(“Sheet1”).Cells(currentRow, 1).Value
endValue = Worksheets(“Sheet1”).Cells(currentRow, 2).Value
For tid = startValue To endValue
nextRow = Worksheets(“Output”).Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Worksheets(“Output”).Cells(nextRow, 1).Value = tid
Next tid
Next currentRow

End Sub

I suspect you could just have done nextRow = nextRow + 1 but looks ok

where do the numbers have to go?

In your example above, if you have a start value of 1 in cell A1, and an end value of 5 in cell B1, then which cells do you want the numbers 1,2,3,4, and 5?