Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 27 of 27

Thread: Filter a text file and produce a new one

  1. #21
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Why don't you use an arrayformula in O33 ?
    PHP Code:
    ={SUM(COUNTIF(N10:P31;$A$2:$F$2))} 

  2. #22
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Sorry yujin,

    in my previous post I have attached a wrong file _80.... instaed of the one here joined

    RIC
    Attached Files Attached Files

  3. #23
    VBAX Regular
    Joined
    Jan 2018
    Posts
    55
    Location
    RIC,

    I'm not sure this is what you want but please give it a try.

    Sub PuregeFile()
        Dim vPar As Integer
        Dim vMin(2) As Integer, vMax(2) As Integer
        Dim vTotalMax As Integer
        Dim rng(16) As Range
        Dim i As Integer, j As Integer, n As Integer
        Dim ReadLine As String
        Dim buf
        Dim cnt1 As Integer, cnt2 As Integer, cnt3 As Integer
        
        vPar = Range("Y8")
        vMin(0) = Range("AV4"): vMin(1) = Range("AV6"): vMin(2) = Range("AV8")
        vMax(0) = Range("AX4"): vMax(1) = Range("AX6"): vMax(2) = Range("AX8")
        vTotalMax = Range("AZ7")
        
        For i = 0 To 16
            Set rng(i) = Range("H10").Offset(, i * 3).Resize(15, 3)
        Next i
        
        Open ThisWorkbook.Path & "\_ToPurgeFile.csv" For Input As #1
        Open ThisWorkbook.Path & "\OutputFile.csv" For Output As #2
        
        Do Until EOF(1)
            Line Input #1, ReadLine
            buf = Split(ReadLine, " ")
            cnt3 = 0
            For n = 0 To 2
                cnt1 = 0
                For i = n * 4 + 2 To n * 4 + 5
                    cnt2 = 0
                    For j = 0 To 5
                        cnt2 = cnt2 + WorksheetFunction.CountIf(rng(i), buf(j))
                    Next j
                    If cnt2 = vPar Then
                        cnt1 = cnt1 + 1
                    End If
                Next i
                If (cnt1 >= vMin(n) And cnt1 <= vMax(n)) Then
                    cnt3 = cnt3 + cnt1
                End If
            Next n
            If cnt3 > 0 And cnt3 <= vTotalMax Then
                Print #2, ReadLine
            End If
        Loop
        
        Close #2
        Close #1
    End Sub
    Attached Files Attached Files

  4. #24
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hi yujin


    surely I made mistakes in trying to explain what I would like


    the check that I would like to do must be limited to groups containing data, therefore 12 and not 17 groups, I tried to adjust the code - see the three comments I entered - but it stops at the line that contains


    cnt2 = cnt2 + WorksheetFunction.CountIf (rng (i), buf (j))


    and based o my poor knowledge I can't find where the problem lies


    based on the check that I did at least the two groups of six numbers:


    49 55 66 78 85 87
    9 23 36 55 83 90


    they should be accepted therefore present in the Outputfile file


    RIC
    Attached Files Attached Files

  5. #25
    VBAX Regular
    Joined
    Jan 2018
    Posts
    55
    Location
    Hi Ric,


    You don't have to modify my code.
    I defined the range array rng(16) but only data-containing ranges, from rng(2) to rng(13), are checked.


    If you run the code, OutputFile.csv gets 44 groups of six numbers containing "9 23 36 55 83 90" and "49 55 66 78 85 87".

  6. #26
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    morning yujin



    as usual you're right ... I didn't pay the right attention in studying the code you wrote ... in my opinion it didn't return the strings of expected numbers because I hadn't noticed that the instruction:


    If cnt3> 0 And cnt3 <= vTotalMax Then




    accepts range of values from 0 up to the value of the variable vTotalMax when instead only strings that satisfy only the value of vTotalMax must be accepted


    I changed the line to


    If cnt3 = vTotalMax Then


    and now I get what I wanted !! :-)




    ;-) thanks again for your availability and patience

    sincerely
    RIC63

  7. #27
    VBAX Regular
    Joined
    Jan 2018
    Posts
    55
    Location
    Hello, RIC

    I'm pleased that I could be of any help of you :-)
    It's kind of fun for me to find out the code someone needs.

Posting Permissions

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