Consolidating duplicate identifiers

[Ports_008]

Hello all,

I'm struggling with a scenario at present. I'm trying to reconcile our weekly carrier invoices into a flat spreadsheet to be imported into our system. I have compiled a macro in VBA to take our carriers CSV and format into a structure acceptable, the last thing I have to do now is identify when a consignment note is listed more than once, and consolidate their respective values to a single line.

An example is below of what my data currently looks like. Can anyone help with a solution in VBA that will identify the secondary charges for connote 1 and 3, update the value in the first row instance (A2, A4 below) to the sum of each line (there may be more than 2 records), and remove the 2nd or 3rd instance from the workbook? All detail represented by 'xxx' would remain consistent with what is present in the first row the consignment note is found (discard changes such as 'XXz' found in the secondary charges.


From This

AMOUNT	INVOICEDATE	INVOICEID	SHIPMENTID	TAXITEMGROUP	VENDACCOUNT	VENDCONSIGNMENTNUM	VENDORSHIPDATE	WORKER
19.23​	xxx	xxx	xxx	xxx	xxx	CONNOTE001	xxx	xxx
23.01​	xxx	xxx	xxx	xxx	xxx	CONNOTE002	xxx	xxx
27.84​	xxx	xxx	xxx	xxx	xxx	CONNOTE003	xxx	xxx
26.86​	xxx	xxx	xxx	xxx	xxx	CONNOTE004	xxx	xxx
12.65​	xxx	xxx	xxx	xxx	xxx	MANUAL HANDLING ROAD CONNOTE001	xxx	xxx
12.65​	xxx	xxx	XXz	xxx	xxx	2ND DELIV CHARGE CONNOTE001	xxx	xxx
12.65​	xxx	xxx	XXz	xxx	xxx	2ND DELIV CHARGE CONNOTE003	xxx	xxx

To This

AMOUNT	INVOICEDATE	INVOICEID	SHIPMENTID	TAXITEMGROUP	VENDACCOUNT	VENDCONSIGNMENTNUM	VENDORSHIPDATE	WORKER
44.53​	xxx	xxx	xxx	xxx	xxx	CONNOTE001	xxx	xxx
23.01​	xxx	xxx	xxx	xxx	xxx	CONNOTE002	xxx	xxx
40.49​	xxx	xxx	xxx	xxx	xxx	CONNOTE003	xxx	xxx
26.86​	xxx	xxx	xxx	xxx	xxx	CONNOTE004	xxx	xxx

I hope this makes sense, and thanks in advance.

 

[Herakles]

You will need a seperate column to calculate the Consignment Note total.

Put this formula in that column where column G is the VENDCONSIGNMENTNUM column and A is the AMOUNT column.

You will need change the scopes of the column A and G ranges.

=IF(IFERROR(FIND(" ",$G2,1),0)=0,SUMIF($G$2:$G$8,"*" & $G2,$A$2:$A$8),"")

 

[Sanjeev1976]

You can try :

Book13
	A	B	C	D	E	F	G	H	I
1	AMOUNT	INVOICEDATE	INVOICEID	SHIPMENTID	TAXITEMGROUP	VENDACCOUNT	VENDCONSIGNMENTNUM	VENDORSHIPDATE	WORKER
2	19.23	xxx	xxx	xxx	xxx	xxx	CONNOTE001	xxx	xxx
3	23.01	xxx	xxx	xxx	xxx	xxx	CONNOTE002	xxx	xxx
4	27.84	xxx	xxx	xxx	xxx	xxx	CONNOTE003	xxx	xxx
5	26.86	xxx	xxx	xxx	xxx	xxx	CONNOTE004	xxx	xxx
6	12.65	xxx	xxx	xxx	xxx	xxx	MANUAL HANDLING ROAD CONNOTE001	xxx	xxx
7	12.65	xxx	xxx	XXz	xxx	xxx	2ND DELIV CHARGE CONNOTE001	xxx	xxx
8	12.65	xxx	xxx	XXz	xxx	xxx	2ND DELIV CHARGE CONNOTE003	xxx	xxx
9
10
11	AMOUNT	INVOICEDATE	INVOICEID	SHIPMENTID	TAXITEMGROUP	VENDACCOUNT	VENDCONSIGNMENTNUM	VENDORSHIPDATE	WORKER
12	44.53	xxx	xxx	xxx	xxx	xxx	CONNOTE001	xxx	xxx
13	23.01	xxx	xxx	xxx	xxx	xxx	CONNOTE002	xxx	xxx
14	40.49	xxx	xxx	xxx	xxx	xxx	CONNOTE003	xxx	xxx
15	26.86	xxx	xxx	xxx	xxx	xxx	CONNOTE004	xxx	xxx
Data

Cell Formulas
Range	Formula
B12:I15	B12	=FILTER(B2:I8,LEN(G2:G8)=10)
A11:I11	A11	=A1:I1
A12:A15	A12	=IFERROR(SUMIF($G$2:$G$8,"*" & $G12,$A$2:$A$8),0)
Dynamic array formulas.

 

[Ports_008]

You will need a seperate column to calculate the Consignment Note total.

Put this formula in that column where column G is the VENDCONSIGNMENTNUM column and A is the AMOUNT column.

You will need change the scopes of the column A and G ranges.

=IF(IFERROR(FIND(" ",$G2,1),0)=0,SUMIF($G$2:$G$8,"*" & $G2,$A$2:$A$8),"")

Thanks for providing this. This works perfectly to pick up the secondary consignment charges. I do need to do more work as it is looking for a space and I know there are other charges that may have a space that are valid.

 

[Herakles]

Can you give a representative list of what would be in the VENDCONSIGNMENTNUM colomn that may also be valid?

 

[Ports_008]

Can you give a representative list of what would be in the VENDCONSIGNMENTNUM colomn that may also be valid?

LATE PAYMENT CHARGE _23039

ACCOUNT ADMIN FEE

I may not be able to proceed with this though. I've just been informed it's possible a secondary charge may not always be processed in the same billing cycle as the original charge which will make things problematic.

 

[Herakles]

If the data indicates in some way that it will not be charged then I am sure that it is possible to do.