INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

"Parsing" a number of data sets

"Parsing" a number of data sets

(OP)
I am not sure the description is parsing - what I have is an Excel workbook in which I am trying to avoid re-entering the same data on different sheets.

I don't know how to program the Excel to list only "relevant" data sets (indicated by the flag 1), and not list the "irrelevant" sets flagged as 0 (zero).

See attached example where there are 4 sets, and Excel needs to list only the ones flagged 1, in consecutive order.

RE: "Parsing" a number of data sets

Sounds like FILTER and SORT.

RE: "Parsing" a number of data sets

(OP)
No - the operation has to be done along with all of the workbook's automatic calculations.

RE: "Parsing" a number of data sets

So?

RE: "Parsing" a number of data sets

(OP)
There may be as many as 40 datasets (charts) and it would defeat the purpose to manually FILTER and SORT. This is large workbook for project design purposes and it needs to be fast and easy to use. Not like the "good" old days with calculator and pencil.

RE: "Parsing" a number of data sets

Are you happy using VBA, or are you looking for an entirely "on sheet" solution?

Both are possible, but (in my opinion) the VBA way would be much easier to set up and much more robust.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: "Parsing" a number of data sets

(OP)
IDS : I can't do VBA, and if I could, I would be worried it would be difficult to deal with when I go in and do any refining modifications of this workbook in the future.

Drew: Your method looked good, but when I set the first to third datasets as Flag=0, it doesn't work. (see attached)

RE: "Parsing" a number of data sets

Quote:

VBA may be more robust, but certainly can't be easier than the following.

OK, that's simpler than what I had in mind. My only suggestion would be to set up the first block of data in column B using absolute references, then remove the $ signs, so you can block copy down as far as you need in one operation.

Creating a VBA user defined function does take a bit longer the first time, but once it is written it is much easier to change the range of data, or use on data in a different format (a different number of rows in each block for instance.

Just for my own amusement, I did it the VBA way (code below, and example in the attached file).

CODE --> VB

Function FilterData(Datarange As Variant)
Dim SRow As Long, TRow As Long, NumRows As Long, TRange() As Variant, i As Long

    Datarange = Datarange.Value2
    NumRows = UBound(Datarange)

    ReDim TRange(1 To NumRows, 1 To 2)

    SRow = 1
    TRow = 0
    Do
        If Datarange(SRow, 1) = 1 Then
 ' Copy Datarange values to Trange
            Do
                SRow = SRow + 1
                TRow = TRow + 1
                TRange(TRow, 1) = Datarange(SRow, 1)
                TRange(TRow, 2) = Datarange(SRow, 2)
            Loop While WorksheetFunction.IsText(Datarange(SRow, 1)) And SRow < NumRows
            ' Increment Trow if SRow at end of Datarange
            If SRow = NumRows Then TRow = TRow + 1
            SRow = SRow + 1
            ' Insert two blank rows in TRange
            If TRow <= NumRows Then
                TRange(TRow, 1) = ""
                TRange(TRow, 2) = ""
                TRow = TRow + 1
                If TRow <= NumRows Then
                    TRange(TRow, 1) = ""
                    TRange(TRow, 2) = ""
                End If
            End If
        Else
    ' Loop to next block of data
            Do
                SRow = SRow + 1
            Loop While WorksheetFunction.IsText(Datarange(SRow, 1)) And SRow < NumRows
            SRow = SRow + 1
        End If
    Loop While SRow < NumRows
    ' Insert blank rows to end of Trange
    For i = TRow + 1 To NumRows
        TRange(i, 1) = ""
        TRange(i, 2) = ""
    Next i
    
    FilterData = TRange

End Function 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close