listing all numbers in range eg 13-87

G

gooner

I use spreadsheets where I commonly have ranges that appear as C1-C45, R57-112

Is there a way that I can breakout this range so all numbers within the
range can be shown?
 
R

Ron Coderre

Try approach:

With
A2:B4 containing the below list of range start/end values
Start End
10 15
21 23
30 32

D1: =MIN($A$2:$A$4)

D2:
=IF(MAX($D$1:$D1)=MAX($B$2:$B$4),"",IF(ISNA(MATCH(D1,$B$2:$B$4,0)),D1+1,INDEX($A$2:$A$4,MATCH(D1,$A$2:$A$4,1)+1)))
Copy that formula down as far as needed

In the above example, the below list is returned:
D1: 10
D2: 11
D3: 12
D4: 13
D5: 14
D6: 15
D7: 21
D8: 22
D9: 23
D10: 30
D11: 31
D12: 32


Is that something you can work with?

***********
Regards,
Ron

XL2003, WinXP
 
Top