Seperating Data and inserting cells 1

[MyFlight]

I have a text file (Switch Dial-Plan) that prior to import looks like below:
0,,,STN,N,,,,,;
1111&&1114,,,STN,N,,,,,;
1130,,,TIE,N,,,,,;
1131,,,TIE,N,,,,,;
1132,,,STN,N,,,,,;
1135&&1186,,,STN,N,,,,,;
120000,,,STN,N,,,,,;
120055&&120056,,,STN,N,,,,,;
134501&&136998,,,STN,N,,,,,;
136999,,,RCG,N,,,,,;
140998,,,STN,N,,,,,;
145,,,CO,N,,,,,;

These files ( I have to do this for about 30 files, range in size from 5KB to 991KB.

I can import the data in one of two ways:
1. With the 2 numbers (&& between them) in the same column.
2. With the 2 numbers (&& between them)in seperate columns.

the Main problem I have is:

1. How can I get excel to List these numbers Vertically in sequence witht the others|?

EXAMPLE 1111&&1114,,,STN,N,,,,,;
this should list out like:
1111,,,STN,N,,,,,;
1112,,,STN,N,,,,,;
1113,,,STN,N,,,,,;
1114,,,STN,N,,,,,;

Any and All suggestions will be welcome.

 

[ck1999]

So you are trying to make a row for each number between the two numbers?

If so I would think I would import them into a temporary spreadsheet then run a macro on that spreadsheet to make your rows.

ck1999

 

[SkipVought]

No need for VBA. Use MS Query.

Here's the results I got in about 5 minutes...
[tt]
Expr1000 f2 f3 f4 f5
0 STN N
1111 STN N
1114 STN N
1130 TIE N
1131 TIE N
1132 STN N
1135 STN N
1186 STN N
120000 STN N
120055 STN N
120056 STN N
134501 STN N
136998 STN N
136999 RCG N
140998 STN N
145 CO N
[/tt]
Here's the SQL code...

SELECT iif(instr(F1,'&&')=0,F1,left(F1,instr(F1,'&&')-1)), f2, f3, f4, f5
FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbsplitrows`.`Data$` 

UNION

SELECT iif(instr(F1,'&&')=0,F1,right(F1,instr(F1,'&&')-1)), f2, f3, f4, f5
FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbsplitrows`.`Data$`

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[MyFlight]

Skip,

The problem is that I need it to list all the numbers between the "&&". EXAMPLE

1111&&1114,,,STN,N,,,,,;

should provide

0 STN N
1111 STN N
1112 STN N
1113 STN N
1114 STN N
1130 TIE N

the query you forwarded does not expand (ie, 1112 & 1113) are not in the list.

Does this make any sense? I'm a little tired an don't know if I explained it well.

Please let me know if you have any questions or need clarification.

 

[SkipVought]

"the query you forwarded does not expand (ie, 1112 & 1113) are not in the list."

Ah, the requirement is to FILL IN the values between the x&&y values. Your statement of the requirement left a bit to be desired.
[tt]
134501&&136998,,,STN,N,,,,,;
[/tt]
So are there 2496 values to be "filled in" between these two?






Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

Got this to work with 2 queriws and one small procedure.

IMPORT:
Import data beginning in row 2.
ROW 1 heading names F1, F2, etc.
Sheet Name Data

Query1:
Sheet Name Query1

SELECT
  iif(instr(F1,'&&')=0,F1,left(F1,instr(F1,'&&')-1))
, iif(instr(F1,'&&')=0,'',right(F1,instr(F1,'&&')-1))
, f2, f3, f4, f5
FROM `Data$` 
Where iif(instr(F1,'&&')=0,'',right(F1,instr(F1,'&&')-1))<>''

Procedure:
Sheet Name NumList
A1 NumList

Sub MakeNumberList()
    Dim r As Range, lRow As Long, i As Long
    lRow = 2
    For Each r In Sheets("Query1").Range(wsQuery1.[A2], Sheets("Query1").[A2].End(xlDown))
        If Len(r.Offset(0, 1).Value) > 0 Then
            For i = r.Value To r.Offset(0, 1).Value
                Sheets("NumList").Cells(lRow, "A").Value = "'" & i
                lRow = lRow + 1
            Next
        End If
    Next
End Sub

Query2:
Sheet Name Query2

SELECT `Query1$`.Expr1000, `Query1$`.F4, `Query1$`.F5, ''

FROM  `Query1$`

Where Expr1001=''

UNION

SELECT NumList, `Query1$`.F4, `Query1$`.F5, 'FILL'

FROM  `NumList$`
,  `Query1$`

Where Expr1001<>''
  and Numlist Between Expr1000 and Expr1001

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}