Conditional Quantity sum based on duplicate values in various other columns

[whassan]

Hi All,
I have an Excel file name "Security macro.xlsm". There are 11 columns on sheet1 of this excel file.
User enters a new record manually, in column A, C, D, E, F, G, H, I, J and K.
Column B, values in this column "Security ID number" are already populated.
I am looking for a VBA solution to achieve that when user enters a new record, run that VBA code and if the new values in column E, H, I, J and K match existing data in the same columns, it will automatically add a new line under the existing record, cut values in column A, C, D, E, F, G, H, I, J and K and paste them in the new line and then sum the "Quantity" and show the sum on additional new line.
I have provided examples of both the original data and how the data should appear after running VBA code.
Could someone kindly help?

Example of ORIGINAL DATA
System ref	Security ID Number	CLIENT/CPTY	Quantity	CCY	Deal date	Sett. Date	Maturity Date	Rate	Security Type	Instrument
12345678	GB6341659900	Client 1	100,000,000.00	GBP	28-Apr-23​	03-May-23​	05-Jul-23	4.47	Non-Discounted	A
12345679	GB6341659901	Client 2	100,000,000.00	EUR	28-Apr-23​	03-May-23​	10-Jul-23	3.21	Discounted	A
12345680	GB6341659902	Client 1	20,000,000.00	EUR	28-Apr-23​	03-May-23​	01-Jun-23	3.18	Discounted	B
12345681	GB6341659903	Client 4	50,000,000.00	EUR	28-Apr-23​	03-May-23​	03-Aug-23	3.415	Discounted	B
12345682	GB6341659904	Client 5	130,000,000.00	GBP	28-Apr-23​	03-May-23​	03-Aug-23	4.705	Discounted	B
12345683	GB6341659905	Client 6	50,000,000.00	EUR	02-May-23​	04-May-23​	02-Aug-23	3.405	Discounted	B
12345684	GB6341659906	Client 7	30,000,000.00	GBP	02-May-23​	04-May-23​	05-Jul-23	4.47	Non-Discounted	A
12345685	GB6341659907	Client 8	73,610,000.00	USD	02-May-23​	03-May-23​	15-Jun-23	5.16	Discounted	A
12345686	GB6341659908	Client 9	100,000,000.00	EUR	02-May-23​	03-May-23​	05-Jun-23	3.205	Discounted	B
12345687	GB6341659909	Client 10	65,000,000.00	EUR	02-May-23​	04-May-23​	05-Jun-23	3.225	Discounted	B
12345688	GB6341659910	Client 11	50,000,000.00	EUR	02-May-23​	04-May-23​	04-Aug-23	3.425	Discounted	B

Example of data below, should appearing after running VBA code.
System ref	Security ID Number	CLIENT/CPTY	Quantity	CCY	Deal date	Sett. Date	Maturity Date	Rate	Security Type	Instrument
12345678	GB6341659900	Client 1	100,000,000.00	GBP	28-Apr-23​	03-May-23​	05-Jul-23	4.47	Non-Discounted	A
12345684		Client 7	30,000,000.00	GBP	02-May-23​	04-May-23​	05-Jul-23	4.47	Non-Discounted	A
			130,000,000.00
12345679	GB6341659901	Client 2	100,000,000.00	EUR	28-Apr-23​	03-May-23​	10-Jul-23	3.21	Discounted	A
12345680	GB6341659902	Client 1	20,000,000.00	EUR	28-Apr-23​	03-May-23​	01-Jun-23	3.18	Discounted	B
12345681	GB6341659903	Client 4	50,000,000.00	EUR	28-Apr-23​	03-May-23​	03-Aug-23	3.415	Discounted	B
12345682	GB6341659904	Client 5	130,000,000.00	GBP	28-Apr-23​	03-May-23​	03-Aug-23	4.705	Discounted	B
12345683	GB6341659905	Client 6	50,000,000.00	EUR	02-May-23​	04-May-23​	02-Aug-23	3.405	Discounted	B
12345684	GB6341659906	Client 7	30,000,000.00	GBP	02-May-23​	04-May-23​	05-Jul-23	4.47	Non-Discounted	A
12345685	GB6341659907	Client 8	73,610,000.00	USD	02-May-23​	03-May-23​	15-Jun-23	5.16	Discounted	A
12345686	GB6341659908	Client 9	100,000,000.00	EUR	02-May-23​	03-May-23​	05-Jun-23	3.205	Discounted	B
12345687	GB6341659909	Client 10	65,000,000.00	EUR	02-May-23​	04-May-23​	05-Jun-23	3.225	Discounted	B
12345688	GB6341659910	Client 11	50,000,000.00	EUR	02-May-23​	04-May-23​	04-Aug-23	3.425	Discounted	B

 

[whassan]

I have the following Works sheet

ISIN macro (working bulk ISIN).xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	OBS REF	ISIN	CLIENT/CPTY	FMT	CCY	TD	SD	MD	Interest Rate	Disc. / Non Disc.	Product
2	12345678	SP6341659900	Customer 1	10,000,000.00	GBP	28-Apr-23	03-May-23	05-Jun-23	4.47	Non-Discounted	AB
3	12345679	SP6341660916	Customer 2	12,000,000.00	EUR	28-Apr-23	03-May-23	05-Jun-23	3.21	Discounted	AB
4	12345680	SP6341661922	Customer 3	14,000,000.00	EUR	28-Apr-23	03-May-23	01-Jun-23	3.18	Discounted	CD
5	12345681	SP6341662938	Customer 4	16,000,000.00	EUR	28-Apr-23	03-May-23	03-Aug-23	3.415	Discounted	CD
6	12345682	SP6341663944	Customer 1	18,000,000.00	GBP	28-Apr-23	03-May-23	03-Aug-23	4.705	Discounted	CD
7	12345683	SP6341664959	Customer 6	20,000,000.00	EUR	02-May-23	04-May-23	02-Aug-23	3.405	Discounted	CD
8	12345684	SP6341665964	Customer 7	22,000,000.00	EUR	02-May-23	04-May-23	04-Aug-23	3.425	Discounted	CD
9	12345685	SP6341666970	Customer 8	24,000,000.00	USD	02-May-23	03-May-23	15-Jun-23	5.16	Discounted	AB
10	12345686	SP6341667010	Customer 3	26,000,000.00	EUR	02-May-23	03-May-23	05-Jun-23	3.205	Discounted	CD
11	12345687	SP6341668026	Customer 10	28,000,000.00	EUR	02-May-23	04-May-23	05-Jun-23	3.225	Discounted	CD
12	12345688	SP6341669032	Customer 11	30,000,000.00	EUR	02-May-23	04-May-23	04-Aug-23	3.425	Discounted	CD
13	12345689	SP6341670048	Customer 12	32,000,000.00	EUR	02-May-23	04-May-23	05-Jun-23	3.22	Discounted	AB
14	12345690	SP6341671053	Customer 7	34,000,000.00	EUR	02-May-23	04-May-23	04-Aug-23	3.435	Discounted	AB
15	12345691	SP6341672069	Customer 14	36,000,000.00	EUR	03-May-23	05-May-23	07-Aug-23	3.435	Discounted	CD
16	12345692	SP6342968417	Customer 15	38,000,000.00	GBP	03-May-23	04-May-23	04-Aug-23	4.71	Discounted	AB
17	12345693	SP6342969423	Customer 16	40,000,000.00	GBP	03-May-23	05-May-23	05-Jul-23	4.615	Discounted	AB
18	12345694	SP6342970439	Customer 2	42,000,000.00	GBP	03-May-23	05-May-23	05-Jul-23	4.62	Non-Discounted	AB
19	12345695	SP6342971445	Customer 18	44,000,000.00	EUR	03-May-23	05-May-23	05-Jun-23	3.23	Discounted	AB
20	12345696	SP6342972450	Customer 8	46,000,000.00	USD	03-May-23	08-May-23	08-Aug-23	5.33	Discounted	CD
21	12345697	SP6342973466	Customer 20	48,000,000.00	GBP	03-May-23	05-May-23	05-Jul-23	4.61	Discounted	AB
22	12345698	SP6342974472	Customer 21	50,000,000.00	GBP	03-May-23	05-May-23	05-Sep-23	4.78	Non-Discounted	AB
23	12345699	SP6342975487	Customer 14	52,000,000.00	GBP	04-May-23	09-May-23	09-Jun-23	4.515	Discounted	AB
24	12345700	SP6342976493	Customer 18	54,000,000.00	EUR	04-May-23	09-May-23	02-Aug-23	3.385	Discounted	AB
25	12345701	SP6342977509	Customer 24	56,000,000.00	EUR	04-May-23	09-May-23	09-Jun-23	3.24	Discounted	AB
26	12345702	SP6342978515	Customer 25	58,000,000.00	EUR	04-May-23	09-May-23	09-Jun-23	3.235	Discounted	CD
27	12345703	SP6342979521	Customer 26	60,000,000.00	EUR	04-May-23	09-May-23	08-Jun-23	3.24	Discounted	AB
28		SP6342980537
29		SP6342981543
30		SP6342982558
31		SP6342983564
32		SP6342984570
33		SP6342985585
34		SP6342986591
35		SP6342987607
36		SP6342988613
37		SP6342989629
38		SP6342990635
39		SP6342991641
40		SP6342992656
41		SP6342993662
42		SP6342994678
43		SP6342995683
44		SP6342996699
45		SP6342997705
46		SP6342998711
47		SP6342999727
48		SP6343000731
49		SP6343001747
50		SP6343002752
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B16:B215	Cell Value	duplicates	text	NO
B16:B215	Cell Value	duplicates	text	NO
B2:B15	Cell Value	duplicates	text	NO
B2:B15	Cell Value	duplicates	text	NO
B251:B1048576,B1	Cell Value	duplicates	text	NO
B251:B1048576,B1	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=$AG$15:$AG$54
C2:C1048576	List	=$S$2:$S$214
A2:A50	Custom	=COUNTIF($A$2:$A$400,A2)=1
J2:J50	List	=$R$2:$R$3
K2	List	=$Q$2:$Q$3

 

[whassan]

Hi,
I have tried to search for a solution to my problem online but have not been successful so far. I kindly request someone to assist me. If I am not clear in asking what I need or have not presented my data in the desired format, please guide me accordingly.Thank you for your help.

 

[whassan]

Hi,
I am wondering if there is anyone who might be able to assist me, or should I assume that my issue will go unresolved and refrain from seeking further help?