Trying to remove variables

[jujubeans69]

Hi all. I currently have files that I track M-F, and some files come in on the weekend. I've been using a macro for a while, but have never been able to tweak it correctly to get the right counts. Below is a sample of files received, and then the macro used to get a count. Shown in orange are files received on the weekend, but are counted on Monday's date. For the most part, all other files are "standard" with 3 coming per day, M-F.

05/06/2013 05:38 AM 108,773,369 100296.20130504120418.zip
05/06/2013 05:39 AM 149,015,522 100296.20130505104307.zip
05/06/2013 05:40 AM 66,485,336 100296.20130506010104.zip
05/06/2013 08:08 AM 26,646,392 100296.20130506063449.zip
05/06/2013 03:48 PM 28,126,364 100296.20130506151928.zip

05/07/2013 01:22 AM 36,114,146 100296.20130507004941.zip
05/07/2013 07:05 AM 346,444 100296.20130507063117.zip
05/07/2013 03:13 PM 173,273,267 100296.20130507144618.zip

05/08/2013 12:57 AM 159,487 100296.20130508003649.zip
05/08/2013 06:51 AM 41,235,506 100296.20130508063742.zip
05/08/2013 03:02 PM 43,185,845 100296.20130508142424.zip

05/09/2013 12:15 AM 1,841,861 100296.20130509001029.zip
05/09/2013 07:16 AM 26,883,396 100296.20130509065859.zip
05/09/2013 04:23 PM 79,716,102 100296.20130509155314.zip

05/10/2013 12:52 AM 26,392,791 100296.20130510002627.zip
05/10/2013 07:06 AM 55,229,309 100296.20130510062450.zip
05/10/2013 02:58 PM 52,153,832 100296.20130510142423.zip


Below is the macro being used. It searches the folder \test for the files, and MOST of the naming format is the same (as shown above), except the last 6 digits before the .zip.

Please Login or Register  to view this content.

My sheet is set up like this. As there is no A, B, C, etc. in the actual file names, it puts 3 for each count in column E. What I NEED it to do is count each file separately, but I do not know how to factor in the "??????" digits to make each row count as 1 file instead of 3. I would LIKE to factor in the date the files come in (FIFO) OR simply use the last 6 digits (as they appear to be in ascending order, with lowest received 1st, mid received 2nd, etc.). Sounds confusing? In summary, I need the 1st file received to be counted as A-cut, 2nd file as B-cut, 3rd file as C-cut, etc. Using Monday's date, I SHOULD have cuts A-F, and any date Tue-Fri, SHOULD have cuts A-B. I hope this explanation and the examples were thorough enough for someone to help me tweak my current macro, or even simplify it. Thanks!!!

Excel sample.jpg

[AlphaFrog]

I'm not sure I follow. It looks like A5:A9 are all the same values. If correct, no need to loop through each of those rows and count all the same files for each row. Just count all the matched files once and put a 1 count in each row.

This will put a count of 1 in each row for each file found. I have a feeling though I totally missed what you are asking.

Please Login or Register  to view this content.

I don't see how A,B,C,D,E are associated with a specific six digits at the end of the file names?

[jujubeans69]

Thanks for the reply, AlphaFrog!

Column A, C, and D are already filled in the spreadsheet. I found this macro from another thread a long time ago, and tweaked it the best I could. The 1st file received for the input date (C1) should be counted as cut A, the 2nd file as cut B, etc. For some reason, since my current macro is still using the variable of ??????, it is counting all 3 files (since all three DO match for the first part of the search string) and placing a 3 into each D column for cuts A, B, C, etc.

[jujubeans69]

AlphaFrog, I tried your change, and it counted each file and placed a 1 into column E, as expected, but it stopped counting for the remaining files. There are many, many more files. I just gave a sample of the files for 100296. Other files are also 6 digits at the beginning, with the same format. nnnnnn.yyyymmdd??????.zip.

Using Monday's date, each file should show 1 for cut A, 1 for cut B, 1 for cut C, 1 for cut D, 1 for cut F (because 2 files were received over the weekend, but counted on Monday). Using any date T-F would only result in 1 for cut A, 1 for cut B, and 1 for cut C. The change only counted the first 3 files for date of 5/7, and then stopped. It did not continue for the rest of the files.

The spreadsheet is longer, but columns A and D is different (they list the next file 5 times, and then the next file 5 times, etc.) Column C goes from cut A-F, and repeats.

Please let me know if you need to see a longer list of the file samples, or a longer version of the spreadsheet. Thanks.

[AlphaFrog]

Please let me know if you need to see a longer list of the file samples, or a longer version of the spreadsheet.

An example workbook with a couple of file numbers in column A would be best.

[jujubeans69]

Here is a longer sample list of files, so i2 = 3 To 150 (or end of column A on spreadsheet).

05/06/2013 09:06 AM 327,743,900 749667.20130505163739.zip
05/06/2013 10:35 AM 30,290,002 749667.20130506045111.zip
05/06/2013 02:43 PM 159,463 749667.20130506112203.zip
05/06/2013 08:20 PM 158,956,279 749667.20130506164508.zip
05/07/2013 09:10 AM 89,413,469 749667.20130507045538.zip
05/07/2013 02:51 PM 159,463 749667.20130507111615.zip
05/07/2013 07:28 PM 155,942,748 749667.20130507162129.zip
05/08/2013 09:02 AM 84,604,060 749667.20130508045440.zip
05/08/2013 02:43 PM 9,366,036 749667.20130508110926.zip
05/08/2013 07:48 PM 24,828,284 749667.20130508160552.zip
05/09/2013 08:48 AM 45,419,854 749667.20130509045551.zip
05/09/2013 02:32 PM 159,463 749667.20130509110806.zip
05/09/2013 08:01 PM 60,030,848 749667.20130509162040.zip
05/10/2013 10:16 AM 159,464 749667.20130510045038.zip
05/10/2013 03:33 PM 87,853,158 749667.20130510112218.zip
05/10/2013 08:36 PM 159,466 749667.20130510162424.zip

05/06/2013 08:01 AM 44,228,973 056135.20130506042925.zip
05/07/2013 08:20 AM 17,536,115 056135.20130507043845.zip
05/08/2013 08:52 AM 11,285,050 056135.20130508043914.zip
05/09/2013 08:01 AM 19,752,754 056135.20130509044010.zip
05/10/2013 07:52 AM 3,630,005 056135.20130510043659.zip

Excel sample 2.jpg

[jujubeans69]

As seen from the longer sample, not all sites will require A-F cuts. It just depends on the number of files received for that day, and the it's the FIFO rule. 1st file needs to be counted as A, 2nd as B, etc. and marked only once in column E, as there are no repeated files.

[AlphaFrog]

This is not tested.

Please Login or Register  to view this content.

[jujubeans69]

Still quite not there. I think the step 5 is messing it up. There will not always be 5 files received for each site. It counted correctly for the first 3 sites, but started messing up after that. I tried changing the step 5 to step 1, and it put 1's all the way down column E.

[AlphaFrog]

I thought you said each one was five rows.

The spreadsheet is longer, but columns A and D is different (they list the next file 5 times, and then the next file 5 times, etc.) Column C goes from cut A-F, and repeats.

Anyway, this should fix it I think.

Please Login or Register  to view this content.

[jujubeans69]

Wow! I barely could understand the original script when I tweaked it, but this is way over my head. I'm about to test it out now, but I don't see where it shows to put the count in column E.

So, I just tested it, and it didn't work. Nothing was counted and placed in column E, and it actually deleted some info in column A. The first version was better, only if the step 5 issue wasn't a problem. I just don't know. I really appreciate all the time you've put in trying to help me, though.

I think it comes down to those last 6 digit variables. If only it would recognize the lowest numeric one as the 1st file received, and the higher the last 6 digits, the later the file was received. I just can't figure out a consistent search pattern for those 6 digits, or how to incorporate them in the search.

What about the date the file is received? Can that be incorporated into the search to show that each file is unique? O.o

[jujubeans69]

Okay, can someone please help me figure out how to count x spaces over, use the data from the search string (highlighted in green below) in a COUNTIF() statement and add it to my current macro? Thanks.
What it SHOULD do is use the search string, find the 1st instance and put a "1" in column E. On the 2nd instance, it would COUNTIF (the 6 digits are > than the 1st instance) and place a "1" into column E. and continue until all found items for that search string are completed.

100296.20130504120418.zip
100296.20130505104307.zip
100296.20130506010104.zip
100296.20130506063449.zip
100296.20130506151928.zip

100296.20130507004941.zip
100296.20130507063117.zip
100296.20130507144618.zip

100296.20130508003649.zip
100296.20130508063742.zip
100296.20130508142424.zip

100296.20130509001029.zip
100296.20130509065859.zip
100296.20130509155314.zip

100296.20130510002627.zip
100296.20130510062450.zip
100296.20130510142423.zip


Below is the macro being used. It searches the folder \test for the files, and MOST of the naming format is the same (as shown above), except the last 6 digits before the .zip.

Dim srch2 As String, found2 As Integer, i2 As Integer
For i2 = 5 To 9
srch2 = "\\mb05a\test\" & Range("A" & i2) & "." & Format(Range("C1"), "yyyymmdd") & "??????" & ".zip"
found2 = 0
If Dir(srch2) <> "" Then 'is there such a file?
Do
found2 = found2 + 1
Loop While Dir() <> "" 'are there more such files
End If
Sheets("AMR").Range("E" & i2) = found2
Next i2

[AlphaFrog]

At this point, I think you really need to post an actual example Excel file. Your jpg images and description of what you want don't always jive and can be interpreted a couple of ways. The layout of your worksheet would be useful for testing, and I don't want to reconstruct it from an image.

The example file should have two sheets with the before and after result and a list of the files somewhere.

[jujubeans69]

AlphaFrog, I only provided the part of the macro I needed help on, as the rest of the macro works. Below is the screen shot of before and after running the macro. Everything is as noted in the macro above, except minor changes as to where the data is placed (E instead of N, i2 = 5 To 138 instead of 9, etc.) These minor changes did not affect the results. The problem is with the variables. Since the search string is exact except the last 6 digits (which are variables) it puts a TOTAL count into each row, instead of a SINGLE count into the rows (for date of 5/7, 3 files match the search string). It should show 1, 1, 1, 0, 0. Instead, it counts 3, 3, 3, 3, 3. Thanks!

before.jpg after.jpg

[jujubeans69]

I looked at your last macro, and see what you were doing. You put a range in there (which I could not determine) and searched for a "unique" string, and it was supposed to count only 1 for each one. I copied and pasted it into the first section of my macro, and it did not work.

[jujubeans69]

Excel sheet included. My desired logic that the computer does not understand.

Sub DAILY()

For i = 5 To 100
srch = "\\network server\history\" & Range("A" & i) & "." & Format(Range("C3"), "yyyymmdd") & "*.zip"

- IF Date entered in C3 = (Any Monday), Then include files C3-1 and C3-2 in srch range
Else Use only C3 in srch range
- Using srch mask, List found Files in Ascending order (FileDateTime) in (c, "F")
- For Each "F"
- IF FileLen(srch) > 160 KB Then
Range("B" & i).Value = FileDateTime(srch)
Else
Range("B" & i).Value = "Empty"
For FileLen(srch) > 160 KB
Do
Open zip file, check if "CORRESP.CSV" exists = True
IF True AND FileLen(CORRESP.CSV) <= 65 B Then
Range("E" & i).Value = "Empty"
Else
Range("E" & i).Value = "YES"
IF False Then
Range("E" & i).Value = "Missing"
Next "F"
Loop?

End Sub

Sample.xls File sample.png fixed.png Monday.png Tue-Fri.png

[jujubeans69]

Actual file attached (with all sensitive data stripped). Thanks.

405499.20130514003540.zip