"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.
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
RE: "Parsing" a number of data sets
RE: "Parsing" a number of data sets
RE: "Parsing" a number of data sets
RE: "Parsing" a number of data sets
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
RE: "Parsing" a number of data sets
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
I de-bugged your method, see attached.
Thanks for providing the method.
RE: "Parsing" a number of data sets
See attached version 4.
RE: "Parsing" a number of data sets
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 FunctionDoug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/