"Parsing" a number of data sets 2

[AELLC]

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.

 

[MintJulep]

Sounds like FILTER and SORT.

 

[AELLC]

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

 

[MintJulep]

So?

 

[AELLC]

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.

 

[IDS]

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/

 

[Drew08]

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

 

[AELLC]

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)

 

[AELLC]

Drew,
I de-bugged your method, see attached.
Thanks for providing the method.

 

[AELLC]

I polished this a bit more because those #N/A's cause problems.
See attached version 4.

 

[IDS]

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).

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/