Need to add group to table from excel

[modglin]

In 8.5 I have text data that was previously imported into Excel.
The data comes across like
Store Reg Hours OT Hours
Store 0040 Ballas
1 C-Store 40 2
3 FoodService 10
StoreTotal 50 2
Store 0050 BigBend
1 C-Store 40 2
StoreTotal 40 2
Store 0100 Skyline
1 C-Store 40 5
3 FoodService 20
StoreTotal 60 5

I need to be be able to group by store# so that I can create a report by territory. In order to do that, I need to create a formula that looks at the line with the previous store # and enter that so I can sort.
I created a formula called Store # it is:
stringVar StoreNumb := if{StoreHours.Store} like "store 0*" then mid({StoreHours.Store},7,4). This returns the 0040 on the first line of the store data.
I then created another one to pull in the previous line:
if {StoreHours.Store} like "1*" or "3*" then previous({@Store}). This works for the first line after Store 0040 ****, however, the second and third lines reflect blank rather than 0040. I cannot figure out how to indicate it should go up 2 previous lines or 3 previous lines.
Appreciate any help!

 

[synapsevampire]

It appears that your data is already grouped by that field based on the lack of numbers on those rows. Which means you can use the

mid({StoreHours.Store},7,4)

Otherwise you have a big problem because the other data won't have a relationship to their group and so won't be in the grouping...

I'd fix the database by creating a second table and xfer the C-Store and FoodService children records of the Store number parents to it.

Then create another table with regions (number and name), and in the Store Number table, add the region numbers.

-k