Filter a txt file based on an Excel template

I have a csv file composed of n records each of which consists of six numbers, I need to filter this file to generate a new file that contains only the records that satisfy a specific condition set through a template made in excel .

Each line of the file to be filtered must comply with a condition that is :
the total of numbers present in each group as set in row 44 (I44, L44, O 44, R44 .... BE44) must satisfy a minimum and a maximum of presence as well as specified in BB7 / BC7 for the specific record analyzed to be validated.

For example, the record reported as test (3 30 47 67 68 89) highlights a string of attendances equal to 0 0 0 1 0 4 3 3 3 3 1 0 0 (groups in which there are no numbers are not considered) which to the conditional string of line 44 it confirms the presence of a min of 4 and a max of 4 conditions met (see the 4 green stripes placed in correspondence with the occurrences that represent compliance with the condition).

While for example the record 3 8 10 47 48 84 will be discarded and therefore not present in the Outputfile.csv file as it satisfies only 3 conditions instead of the minimum 4 provided.

The file provided as an example 'ToPurgeFile.csv' consists of 6545 records and theoretically should be reduced by more than 50% with the excel template attached.

as I can't write the code, my knowledge is too poor, I ask if someone can write it for me

thanks in advance

I sent you a PM.

In the attached, new formulae at range H47:AB48.
Macro called PurgeFile2.

Update manually:

1. rows 10:31 grid values
2. row 44 (which will transfer data to row 47)
3. run the macro

You don't need formulae in row 33, removing them might speed things up.

Hello p45cal


thank you for your support, I'm going to do some tests to check if the result of the process returns the expected values and I inform you, ... really impressive for me as you have solved in a few lines a process that I thought required a lot more code ... thanks again

It can be done mucht faster in RAM without any worksheet interaction.

Quote:

---

Originally Posted by snb

It can be done mucht faster in RAM without any worksheet interaction.

---

Yes, it could be almost instant, whereas the time to write and test such a routine (macro or whatever) wouldn't be.
RIC63, I merely tweaked your worksheet formulae and let them do the donkey work…

Hello p45cal


that's what I wanted, I sincerely thank you for me it's okay


thanks again for your support

Hello p45cal


Based on the routine you provided me I tried to modify it to evaluate further parameters but the output file does not seem to have a consistent content.
Through the debug window I tried to check the values assumed from time to time by the variables but for AA48 AA53 AA58 BE6 AA48 AA53 AA58 and BF6 no value is displayed.
Can you please tell me if what i intend to do is really feasible and if you can correct where am i wrong?


Thanks

...I hope it is correct to write here, I looked in the FAQ if there was something to reopen a thread but I could not find anything

Quote:

---

Originally Posted by RIC63

Through the debug window I tried to check the values assumed from time to time by the variables but for AA48 AA53 AA58 BE6 AA48 AA53 AA58 and BF6 no value is displayed.

---

You remember I said:

Quote:

---

Originally Posted by p45cal

RIC63, I merely tweaked your worksheet formulae and let them do the donkey work…

---

What the macro does is simple:
1. it updates cells A2:F2
2. lets the formulae do their calculations resulting in a single cell (AB48) being TRUE/FALSE
3. only if it's TRUE, write the data to the output file

So what you need to do is change the formulae on the sheet finishing with a TRUE/FALSE in cell AB48.

Now how you change/add formulae on the sheet depends on:

Quote:

---

Originally Posted by RIC63

what i intend to do is really feasible

---

which I don't really know! -despite this:

Quote:

---

Originally Posted by RIC63

to evaluate further parameters

---

which isn't very explanatory…

Quote:

---

Originally Posted by RIC63

...I hope it is correct to write here, I looked in the FAQ if there was something to reopen a thread but I could not find anything

---

Yes it's correct to write here.

good morning p45cal


in the file that I have attached I tried to modify the routine that you have developed for me so that it can do other conditional checks that determine if a string written in A2:F2 goes into the output file or not, sorry I assumed it was clear.


What I would like to do contextually to verify the true / false condition of AB48 is:


AA48 is between the two values min BB5 and max BC5
AA53 is between the two values min BB6 and max BC6
AA58 is between the two values min BB7 and max BC7


and finally that the sum of AA48 + AA53 + AA58 is included between the two values min BE6 and max BF6


only if all conditions are met then writes / adds to the output file


thank you for what you can let me know

You seem to be getting it all fairly correct.
In your file, I think:
cell I48 has the same formula as J48.

• the 3 ranges H48:S48,H53:S53,H58:S58 are identical
• the formulae on row 33 carry out the same function as the previous point

So we have 48 cells with formulae when we only need 12


In the attached:

• I've moved the more compact formulae to row 33 and created links to them in row 48
• Brought the 3 rows Dist 1,2 & 3 next to each other
• Adjusted the formulae in the 3 rows below row 48
• Added formulae to AD49:51; check this logic is correct!
• Added a final TRUE/FALSE formula in cell AI48 to ensure all conditions are met. You must check that this formula and its precedent formulae are all correct because this is the cell that the (adjusted) macro will look at to decide whether to include the data in the output file.

hello p45cal


I agree with all of your observations and also with the logic of the formulas and the simplifications you have introduced
it is impressive for me to see how you solve and connect concepts with simplicity
thanks for your help and support sincerely