Results 1 to 2 of 2

Thread: Formula fill when there are constant number of spaces between data

  1. #1

    Formula fill when there are constant number of spaces between data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,


    Background: I have a set of .txt data I've imported into Excel. It came off a R232 serial port of a Fire Alarm Panel and isn't formatted in comma or tab or even a space delimited pattern. However it is consistently spaced (5 rows between data sets). Each little group (5 rows) of information is for a single Device on the system (ex. Smoke Detector) that has an Device Address, Type, and Description.


    Problem: I'm trying to find, with no luck so far, a formula I can fill down that will take into account the spaced pattern.


    What I've Tried: Well, being dumb, I first tried doing the first three devices by simple references and seeing if Excel would recognize the pattern -- Nope.
    I then tried using the Offsets command but that didn't work either -- it doesn't compound the offset amounts.


    Goal: If I can get the devices into a nice list, I can then export a .csv to the Fire Alarm program and not have to manually enter each device (there are over 700 in total!). I've attached the Excel file if that may be of service to anyone able point me in the right direction.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screenshot 2015-04-13 17.12.34.png 
Views:	10 
Size:	62.0 KB 
ID:	3383  
    Attached Files Attached Files

  2. #2
    Good afternoon,

    This is not pretty, but I think it will work. One note, after the data gets beyond device #2, the spacing becomes irregular. I pulled the formulas down farther than they need to go to show that. Anyway, I used a bunch of indirects so I could calculate the cell where the next data point was. I'm almost certain there is a better way to do this, but this will hopefully function as duct tape in the interim.

    Best of luck,
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •