INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# "Parsing" a number of data sets2

## "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.

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!