Need to validate that all documents are appearing in a file

zookeepertx · Jul 2, 2019

Hello there!

I'm trying to help out a co-worker with an application that's randomly deleting documents from his files. He needs to be able to take a report showing a set of Reference File numbers and all documents that are currently in each file and parse them out onto a second sheet showing the documents in the file and the ones that have gone missing.

The initial report has 20 or so columns depending on who pulled it and how their window is set up, but I'm starting with a sort that will put the columns in the desired order. Then the analysis can start and it only concerns the first 5 columns.
Here's what the initial information looks like:

 [TABLE="class: MsoNormalTable, width: 7"]
<tbody>[TR]
[TD="width: 104"] Broker Reference Number[/TD]
[TD="width: 65"] Division[/TD]
[TD="width: 162"] Entry Number[/TD]
[TD="width: 125"] Imports - Document Type[/TD]
[TD="width: 203"] Name[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Broker's Invoice[/TD]
[TD="width: 203"]

805229

[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Bill of Lading[/TD]
[TD="width: 203"] BL ABT29S036162[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Packing List[/TD]
[TD="width: 203"] Packing (71)[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] CF 7501[/TD]
[TD="width: 203"] CF7501 (104)[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Receiving[/TD]
[TD="width: 203"] IFS REC (25)[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Invoice, Commercial[/TD]
[TD="width: 203"] 1915/2018 D2000/10100281521[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Payment (Debit) Advice[/TD]
[TD="width: 203"] 1915/2018[/TD]
[/TR]
[TR]
[TD]

180128707

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128707-1[/TD]
[TD="width: 125"] Spec Sheet[/TD]
[TD="width: 203"] Spec (31)[/TD]
[/TR]
[TR]
[TD]

180128730

[/TD]
[TD] DE[/TD]
[TD="width: 162"] KE3 0128709-7[/TD]
[TD="width: 125"] Invoice, Commercial[/TD]
[TD="width: 203"] 223/2018 D4401/10100289725[/TD]
[/TR]
[TR]
[TD]

180128730

[/TD]
[TD] DE[/TD]
[TD="width: 162"] KE3 0128709-7[/TD]
[TD="width: 125"] Payment (Debit) Advice[/TD]
[TD="width: 203"] 223/2018[/TD]
[/TR]
[TR]
[TD]

180128730

[/TD]
[TD] DE[/TD]
[TD="width: 162"] KE3 0128730-3[/TD]
[TD="width: 125"] Broker's Invoice[/TD]
[TD="width: 203"]

803932

[/TD]
[/TR]
[TR]
[TD]

180128730

[/TD]
[TD] DE[/TD]
[TD="width: 162"] KE3 0128730-3[/TD]
[TD="width: 125"] Broker's Invoice[/TD]
[TD="width: 203"]

10013524

[/TD]
[/TR]
[TR]
[TD]

180128900

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128900-2[/TD]
[TD="width: 125"] Broker's Invoice[/TD]
[TD="width: 203"]

805119

[/TD]
[/TR]
[TR]
[TD]

180128900

[/TD]
[TD] ABC[/TD]
[TD="width: 162"] KE3 0128900-2[/TD]
[TD="width: 125"] Broker's Invoice[/TD]
[TD="width: 203"]

10014022

[/TD]
[/TR]
[TR]
[TD]

10100412480

[/TD]
[TD][/TD]
[TD="width: 162"][/TD]
[TD="width: 125"][/TD]
[TD="width: 203"] Appr: grazia 12[/TD]
[/TR]
[TR]
[TD]

10100412480

[/TD]
[TD][/TD]
[TD="width: 162"][/TD]
[TD="width: 125"][/TD]
[TD="width: 203"] Grazia 12 desc page[/TD]
[/TR]
[TR]
[TD]

10100412480

[/TD]
[TD][/TD]
[TD="width: 162"][/TD]
[TD="width: 125"][/TD]
[TD="width: 203"] RFJA: Grazia Vozza 12E/9[/TD]
[/TR]
</tbody>[/TABLE]

So, looking down column A, there are 5 Broker Reference Files on this table. Each will get analyzed as a set and the results should display on 1 of 2 other sheets created by the macro; sheet 2 is called RESULTS and sheet 3 is called ERRORS. (I have this part working in the macro). These 2 sheets are each set up with a header row, starting in C1, that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Broker Reference Number[/TD]
[TD]Division[/TD]
[TD]Entry Number[/TD]
[TD]Broker's Invoice[/TD]
[TD]Broker's Invoice[/TD]
[TD]Broker's Invoice[/TD]
[TD]Broker's Invoice[/TD]
[TD]Broker's Invoice[/TD]
[TD]Broker's Invoice[/TD]
[TD]Bill of Lading[/TD]
[TD]Cargo Release[/TD]
[TD]Invoice, Commercial[/TD]
[TD]Packing List[/TD]
[TD]CF 7501[/TD]
[TD]Rated Invoice[/TD]
[TD]Delivery Order[/TD]
[TD]Receiving[/TD]
[TD]Payment (Debit) Advice[/TD]
[TD]FWS 3177[/TD]
[TD]CITES[/TD]
[TD]NMG Audit[/TD]
[TD]Checklist[/TD]
[TD]CF 7501-REVISED[/TD]
[/TR]
</tbody>[/TABLE]

The first Reference Number is 180128707, which should go in C2 of the RESULTS sheet.
The Division goes in D2 of RESULTS and the Entry Number goes in E2 of RESULTS. *
BROKER'S INVOICE:

If there's no document in the file called Broker's Invoice, that's a big problem, so F2 needs to get the word NO in caps and bolded on RESULTS.
If the document does exist in that file then the Name should go in F2 on RESULTS.
If there's only 1 Broker's Invoice, the the rest of the columns with Broker's Invoice in the header can stay blank on RESULTS.
If there is more than just the 1 Broker's Invoice, the name of each one should appear in G-K of RESULTS. (Only up to 6 Broker's Invoices. If there are more than that, we don't care.)

For the rest of the headers on RESULTS L-Y, if the document exists in the file on the "raw data sheet", YES should appear in the corresponding column on RESULTS. If they are not in the file on the "raw data sheet", then the cell in the corresponding column stays blank.

* If a file number has more than 1 Entry Number, that's a big problem, and ALL rows with that file number needs to go through the same process as above, except the data should show up on the ERRORS tab.

The 4th file number in the example - 10100412480 - has no Division and no Entry number so we can ignore those rows entirely.

When finished, the data from the above example should look like this:

 [TABLE="class: MsoNormalTable, width: 375"]
<tbody>[TR]
[TD="width: 162, colspan: 3"] RESULTS SHEET[/TD]
[TD="width: 37"][/TD]
[TD="width: 97"][/TD]
[TD="width: 57"][/TD]
[TD="width: 63"][/TD]
[TD="width: 57"][/TD]
[TD="width: 57"][/TD]
[TD="width: 57"][/TD]
[TD="width: 57"][/TD]
[TD="width: 47"][/TD]
[TD="width: 56"][/TD]
[TD="width: 81"][/TD]
[TD="width: 54"][/TD]
[TD="width: 35"][/TD]
[TD="width: 52"][/TD]
[TD="width: 59"][/TD]
[TD="width: 37"][/TD]
[TD="width: 97"][/TD]
[TD="width: 35"][/TD]
[TD="width: 40"][/TD]
[TD="width: 41"][/TD]
[TD="width: 44"][/TD]
[TD="width: 58"][/TD]
[/TR]
[TR]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] G[/TD]
[TD] H[/TD]
[TD] I[/TD]
[TD] J[/TD]
[TD] K[/TD]
[TD] L[/TD]
[TD] M[/TD]
[TD] N[/TD]
[TD] O[/TD]
[TD] P[/TD]
[TD] Q[/TD]
[TD] R[/TD]
[TD] S[/TD]
[TD] T[/TD]
[TD] U[/TD]
[TD] V[/TD]
[TD] W[/TD]
[TD] X[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="width: 116"] Broker Reference Number[/TD]
[TD="width: 37"] Division[/TD]
[TD] Entry Number[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 63"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 47"] Bill of Lading[/TD]
[TD="width: 56"] Cargo Release[/TD]
[TD="width: 81"] Invoice, Commercial[/TD]
[TD="width: 54"] Packing List[/TD]
[TD="width: 35"] CF 7501[/TD]
[TD="width: 52"] Rated Invoice[/TD]
[TD="width: 59"] Delivery Order[/TD]
[TD="width: 37"] Receiving[/TD]
[TD="width: 97"] Payment (Debit) Advice[/TD]
[TD="width: 35"] FWS 3177[/TD]
[TD="width: 40"] CITES[/TD]
[TD="width: 41"] NMG Audit[/TD]
[TD="width: 44"] Checklist[/TD]
[TD="width: 58"] CF 7501-REVISED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

180128707

[/TD]
[TD]ABC[/TD]
[TD]KE3 0128707-1[/TD]
[TD]

805229

[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]YES[/TD]
[TD]
[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]YES[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

180128900

[/TD]
[TD]ABC
[/TD]
[TD]KE3 0128900-2[/TD]
[TD]

805119

[/TD]
[TD]

10014022

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 [TABLE="class: MsoNormalTable, width: 375"]
<tbody>[TR]
[TD="width: 162, colspan: 3"] ERRORS SHEET[/TD]
[TD="width: 37"][/TD]
[TD="width: 97"][/TD]
[TD="width: 57"][/TD]
[TD="width: 63"][/TD]
[TD="width: 57"][/TD]
[TD="width: 57"][/TD]
[TD="width: 57"][/TD]
[TD="width: 57"][/TD]
[TD="width: 47"][/TD]
[TD="width: 56"][/TD]
[TD="width: 81"][/TD]
[TD="width: 54"][/TD]
[TD="width: 35"][/TD]
[TD="width: 52"][/TD]
[TD="width: 59"][/TD]
[TD="width: 37"][/TD]
[TD="width: 97"][/TD]
[TD="width: 35"][/TD]
[TD="width: 40"][/TD]
[TD="width: 41"][/TD]
[TD="width: 44"][/TD]
[TD="width: 58"][/TD]
[/TR]
[TR]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] G[/TD]
[TD] H[/TD]
[TD] I[/TD]
[TD] J[/TD]
[TD] K[/TD]
[TD] L[/TD]
[TD] M[/TD]
[TD] N[/TD]
[TD] O[/TD]
[TD] P[/TD]
[TD] Q[/TD]
[TD] R[/TD]
[TD] S[/TD]
[TD] T[/TD]
[TD] U[/TD]
[TD] V[/TD]
[TD] W[/TD]
[TD] X[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="width: 116"] Broker Reference Number[/TD]
[TD="width: 37"] Division[/TD]
[TD] Entry Number[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 63"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 57"] Broker's Invoice[/TD]
[TD="width: 47"] Bill of Lading[/TD]
[TD="width: 56"] Cargo Release[/TD]
[TD="width: 81"] Invoice, Commercial[/TD]
[TD="width: 54"] Packing List[/TD]
[TD="width: 35"] CF 7501[/TD]
[TD="width: 52"] Rated Invoice[/TD]
[TD="width: 59"] Delivery Order[/TD]
[TD="width: 37"] Receiving[/TD]
[TD="width: 97"] Payment (Debit) Advice[/TD]
[TD="width: 35"] FWS 3177[/TD]
[TD="width: 40"] CITES[/TD]
[TD="width: 41"] NMG Audit[/TD]
[TD="width: 44"] Checklist[/TD]
[TD="width: 58"] CF 7501-REVISED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

180128730

[/TD]
[TD] DE[/TD]
[TD] KE3 0128709-7[/TD]
[TD] NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

180128730

[/TD]
[TD] DE[/TD]
[TD]KE3 0128730-3[/TD]
[TD]

803932

[/TD]
[TD]

10013524

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope this is at least somewhat clear. If not, please feel free to let me know what other information I can provide.

Thank you!

Jenny

Rijnsent · Jul 8, 2019

Hi Jenny,
could you please add your code, as that would help massively in helping you? Please do so in [ CODE][/CODE] tags.
Thanks!
Koen

zookeepertx · Jul 10, 2019

Rijnsent said:
Hi Jenny,
could you please add your code, as that would help massively in helping you? Please do so in [ CODE][/CODE] tags.
Thanks!
Koen

I'm so sorry - I didn't get a notification that there was a message here! Thank you for replying!

I don't actually have any code yet; I've been struggling with the order in which things need to be done. However, I have made up a sheet with the steps as I THINK they need to happen. Here is that information. (I hope this will show up as a readable table.)

 [TABLE="class: MsoNormalTable, width: 563"]
<tbody>[TR]
[TD="width: 228, colspan: 5"] r = 1st Broker Ref Number (A2)[/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 228, colspan: 5"] d = 1st Division in that file (B2)[/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 240, colspan: 6"] e = 1st Entry Number in that file (C2)[/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 54"] i= 2[/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 718, colspan: 7"] Put the Broker Reference Number from A2 in C2 on RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 718, colspan: 7"] Put the Division from B2 in D2 on RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 718, colspan: 7"] Put the Entry Number from C2 in E2 on RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 102, colspan: 2"] For i = 2 to LR[/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 90, colspan: 2"] IF (A & i) = r[/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 90, colspan: 2"] If (B & i) = d[/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 84, colspan: 2"] If (C & i) = e[/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] If (D & i)= Broker's Invoice[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put invoice # from E in F2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] b = 1[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] If there's another Broker's Invoice in that file[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] b = b +1[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] AND b < 7 (We only need the first 6 of these, if there are that many)[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put invoice # from E in G2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Bill of Lading[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in L2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Cargo Release[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in M2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Packing List[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in N2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= CF 7501[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in O2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Rated Invoice[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in P2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Delivery Order[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in Q2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Receiving[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in R2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Payment (Debit) Advice[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in S2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= FWS 3177[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in T2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= CITES[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in U2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= NMG Audit[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in V2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= Checklist[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in W2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 526, colspan: 3"] ElseIf (D & i)= CF 7501-Revised[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"] Put "YES" in X2 of RESULTS sheet[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 574, colspan: 4"] If Entry Number does not = number above
[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"]
[/TD]
[TD="width: 526, colspan: 3"] FOLLOW INSTRUCTIONS AS ABOVE, BUT ON SHEET NAMED "ERRORS"[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 616, colspan: 5"] If Division does not = above
[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"][/TD]
[TD="width: 42"]
[/TD]
[TD="width: 574, colspan: 4"] FOLLOW INSTRUCTIONS AS ABOVE, BUT ON SHEET NAMED "ERRORS"
[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 664, colspan: 6"] If Broker Reference Number does not = number above
[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"][/TD]
[TD="width: 48"]
[/TD]
[TD="width: 616, colspan: 5"] Use the new number to start a new loop[/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
[TR]
[TD="width: 54"] Next i[/TD]
[TD="width: 48"][/TD]
[TD="width: 42"][/TD]
[TD="width: 48"][/TD]
[TD="width: 36"][/TD]
[TD="width: 490, colspan: 2"][/TD]
[TD="width: 21, colspan: 2"][/TD]
[TD="width: 12"][/TD]
[/TR]
</tbody>[/TABLE]

**Of course r, d and e would then have to become the new Ref, Division and Entry data for the next new file.**

Please let me know what else I can provide to help clarify, as I am not good at explaining things, but am happy to try again, LOL!

Thank you for any help!

Jenny

Rijnsent · Jul 11, 2019

Hi Jenny,
okay, I gave your case a swing, got there 3/4 way. I'm not sure it's practical to have all errors on a different sheet, so I went for the easy way and put all results in the RESULTS sheet.
Hope this gives you a good start. For more on VBA: these are two free courses that I generally point people to: https://homeandlearn.org/ & https://www.excel-pratique.com/en/vba.php
Cheers,
Koen

Code:

Sub ProcessData()

Set DataSht = Worksheets("DATA")
Set ResSht = Worksheets("RESULTS")
Set ErrSht = Worksheets("ERRORS")

'Some crude cleaning
ResSht.Range("A2:Z10000").ClearContents
ErrSht.Range("A2:Z10000").ClearContents

StartRw = 2
EndRw = DataSht.Range("A2").End(xlDown).Row
ResultRw = 1

CurrentBroker = ""
CurrentDivision = ""
CurrentEntry = ""

'Loop through all rows of the DATA sheet
For Rw = StartRw To EndRw

    If DataSht.Range("A" & Rw).Value = CurrentBroker And DataSht.Range("B" & Rw).Value = CurrentDivision And DataSht.Range("C" & Rw).Value = CurrentEntry Then
        'Add to previous combination of broker, div & entry
        
        If DataSht.Range("D" & Rw).Value = "Broker's Invoice" And NrBrokerInvoices < 6 Then
            ResSht.Cells(ResultRw, 6 + NrBrokerInvoices).Value = DataSht.Range("E" & Rw).Value
            NrBrokerInvoices = NrBrokerInvoices + 1
        End If
    
        For Col = 1 To 14
            If DataSht.Range("D" & Rw).Value = ResSht.Range("K1").Offset(0, Col).Value Then
                ResSht.Range("K1").Offset(ResultRw - 1, Col).Value = "YES"
                Exit For
            End If
        Next Col
        
    Else
        'New combination of broker, div & entry
        NrBrokerInvoices = 0
        ResultRw = ResultRw + 1
        CurrentBroker = DataSht.Range("A" & Rw).Value
        CurrentDivision = DataSht.Range("B" & Rw).Value
        CurrentEntry = DataSht.Range("C" & Rw).Value
        
        ResSht.Cells(ResultRw, 3).Value = CurrentBroker
        ResSht.Cells(ResultRw, 4).Value = CurrentDivision
        ResSht.Cells(ResultRw, 5).Value = CurrentEntry
    
        If DataSht.Range("D" & Rw).Value = "Broker's Invoice" And NrBrokerInvoices < 6 Then
            ResSht.Cells(ResultRw, 6 + NrBrokerInvoices).Value = DataSht.Range("E" & Rw).Value
            NrBrokerInvoices = NrBrokerInvoices + 1
        End If
    
        For Col = 1 To 14
            If DataSht.Range("D" & Rw).Value = ResSht.Range("K1").Offset(0, Col).Value Then
                ResSht.Range("K1").Offset(ResultRw - 1, Col).Value = "YES"
                Exit For
            End If
        Next Col
    
    End If
Next Rw

End Sub

zookeepertx · Jul 11, 2019

Rijnsent said:
Hi Jenny,
okay, I gave your case a swing, got there 3/4 way. I'm not sure it's practical to have all errors on a different sheet, so I went for the easy way and put all results in the RESULTS sheet.
Hope this gives you a good start. For more on VBA: these are two free courses that I generally point people to: https://homeandlearn.org/ & https://www.excel-pratique.com/en/vba.php
Cheers,
Koen

Hi Koen!

Oooh, this is nice!! After I studied it a bit and figured out how it worked, I just had to make a couple of very minor adjustments. It works perfectly for the Broker Reference Number, DIVISION, Entry Number and Broker's Invoices. The next column on RESULTS is for the Bill of Lading and I think I had given the wrong column for that, but it works now - fills in "YES" if that document exists in the file.

The only problem is that the rest of the columns on RESULTS (M-Y) also need to get a "YES" if they have a document in each file. I'm thinking there's probably a way to increment the columns...
Oh, WAIT! As I was typing this, I had a moment of realization - one of my adjustments had messed with that part of your code, made another tiny adjustment, and now it seems to be working exactly right! In fact, I just tested it on data that's almost 9,800 rows long and it appears to have done the job perfectly! YAYY!!

This is amazing! You're awesome! Thank you so much; this made my whole week!

Oh, and I haven't been to the second link you provided but I have spent some time on homeandlearn and I LOVE that site! They give very understandable explanations in pretty straight-forward terms.

Jenny

Need to validate that all documents are appearing in a file

zookeepertx

Well-known Member

Excel Facts

Rijnsent

Well-known Member

zookeepertx

Well-known Member

Rijnsent

Well-known Member

zookeepertx

Well-known Member

Similar threads

Forum statistics

Share this page

Need to validate that all documents are appearing in a file

zookeepertx

Well-known Member

Excel Facts

Rijnsent

Well-known Member

zookeepertx

Well-known Member

Rijnsent

Well-known Member

zookeepertx

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock