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:

Broker Reference Number	Division	Entry Number	Imports - Document Type	Name
180128707	ABC	KE3 0128707-1	Broker's Invoice	805229
180128707	ABC	KE3 0128707-1	Bill of Lading	BL ABT29S036162
180128707	ABC	KE3 0128707-1	Packing List	Packing (71)
180128707	ABC	KE3 0128707-1	CF 7501	CF7501 (104)
180128707	ABC	KE3 0128707-1	Receiving	IFS REC (25)
180128707	ABC	KE3 0128707-1	Invoice, Commercial	1915/2018 D2000/10100281521
180128707	ABC	KE3 0128707-1	Payment (Debit) Advice	1915/2018
180128707	ABC	KE3 0128707-1	Spec Sheet	Spec (31)
180128730	DE	KE3 0128709-7	Invoice, Commercial	223/2018 D4401/10100289725
180128730	DE	KE3 0128709-7	Payment (Debit) Advice	223/2018
180128730	DE	KE3 0128730-3	Broker's Invoice	803932
180128730	DE	KE3 0128730-3	Broker's Invoice	10013524
180128900	ABC	KE3 0128900-2	Broker's Invoice	805119
180128900	ABC	KE3 0128900-2	Broker's Invoice	10014022
10100412480				Appr: grazia 12
10100412480				Grazia 12 desc page
10100412480				RFJA: Grazia Vozza 12E/9

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:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Broker Reference Number

Division

Entry Number

Broker's Invoice

Bill of Lading

Cargo Release

Invoice, Commercial

Packing List

CF 7501

Rated Invoice

Delivery Order

Receiving

Payment (Debit) Advice

FWS 3177

CITES

NMG Audit

Checklist

CF 7501-REVISED

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:

RESULTS SHEET

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Broker Reference Number

Division

Entry Number

Broker's Invoice

Bill of Lading

Cargo Release

Invoice, Commercial

Packing List

CF 7501

Rated Invoice

Delivery Order

Receiving

Payment (Debit) Advice

FWS 3177

CITES

NMG Audit

Checklist

CF 7501-REVISED

180128707

ABC

KE3 0128707-1

805229

YES

180128900

ABC

KE3 0128900-2

805119

10014022

ERRORS SHEET

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Broker Reference Number

Division

Entry Number

Broker's Invoice

Bill of Lading

Cargo Release

Invoice, Commercial

Packing List

CF 7501

Rated Invoice

Delivery Order

Receiving

Payment (Debit) Advice

FWS 3177

CITES

NMG Audit

Checklist

CF 7501-REVISED

180128730

DE

KE3 0128709-7

NO

YES

180128730

DE

KE3 0128730-3

803932

10013524

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.)

r = 1st Broker Ref Number (A2)
d = 1st Division in that file (B2)
e = 1st Entry Number in that file (C2)
i= 2

Put the Broker Reference Number from A2 in C2 on RESULTS sheet
Put the Division from B2 in D2 on RESULTS sheet
Put the Entry Number from C2 in E2 on RESULTS sheet
For i = 2 to LR
	IF (A & i) = r
		If (B & i) = d
			If (C & i) = e
				If (D & i)= Broker's Invoice
					Put invoice # from E in F2 of RESULTS sheet
					b = 1
					If there's another Broker's Invoice in that file
					b = b +1
					AND b < 7 (We only need the first 6 of these, if there are that many)
					Put invoice # from E in G2 of RESULTS sheet
				ElseIf (D & i)= Bill of Lading
					Put "YES" in L2 of RESULTS sheet
				ElseIf (D & i)= Cargo Release
					Put "YES" in M2 of RESULTS sheet
				ElseIf (D & i)= Packing List
					Put "YES" in N2 of RESULTS sheet
				ElseIf (D & i)= CF 7501
					Put "YES" in O2 of RESULTS sheet
				ElseIf (D & i)= Rated Invoice
					Put "YES" in P2 of RESULTS sheet
				ElseIf (D & i)= Delivery Order
					Put "YES" in Q2 of RESULTS sheet
				ElseIf (D & i)= Receiving
					Put "YES" in R2 of RESULTS sheet
				ElseIf (D & i)= Payment (Debit) Advice
					Put "YES" in S2 of RESULTS sheet
				ElseIf (D & i)= FWS 3177
					Put "YES" in T2 of RESULTS sheet
				ElseIf (D & i)= CITES
					Put "YES" in U2 of RESULTS sheet
				ElseIf (D & i)= NMG Audit
					Put "YES" in V2 of RESULTS sheet
				ElseIf (D & i)= Checklist
					Put "YES" in W2 of RESULTS sheet
				ElseIf (D & i)= CF 7501-Revised
					Put "YES" in X2 of RESULTS sheet
			If Entry Number does not = number above
				FOLLOW INSTRUCTIONS AS ABOVE, BUT ON SHEET NAMED "ERRORS"
		If Division does not = above
			FOLLOW INSTRUCTIONS AS ABOVE, BUT ON SHEET NAMED "ERRORS"
	If Broker Reference Number does not = number above
		Use the new number to start a new loop
Next i

**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