macro to insert lines when comparing 2 lists or form to update 2 lists

[glennmckenna]

Hello,

i've currently got 2 lists : 

• list 1 is a catalog of tools
• list 2 is a the tracking of orders from the catalog

my aim is to declare that there has been an order in the the catalog (list 1) by putting a "Y" in a column and then running a macro that will add the new lines into the list 2 without deleting the old ones and the sorting by "internal ref"

both lists are in the same work book just different pages 

My main question is how can i update list 2 with out deleting the lines currently present, nor creating duplicates ?

i was thinking about going ahead this way :

1 - fetch last line of list 2

2 - copy/paste or advanced filter to fetch all the orders

3 - sort by internal ref

but that doesn't solve the duplicate error ... maybe it could be done with an "if"  or ticking "unique records only" in the advanced filter?

if this option was to complicated i was thinking of a "place order" form that would update list 1 and then add the affected lines to list 2 ... the form would need to be able to place multiple orders at once

list 1 looks like this :

Tool name	tool version	Internal ref	Tool price	Odered by X	Ordered by YZ
Tool 1	A	Tool 1_ind A	99
Tool 1	B	Tool 1_ind B	56		Y
Tool 1	C	Tool 1_ind C	7	Y
Tool 2	A	Tool 2_ind A	456	Y	Y
tool 3	A	tool 3_ind A	456	Y	Y
Tool 4	A	Tool 4_ind A	46	Y

List 2 looks like (once filtered by internal ref):

Internal ref	Tool name	tool version	Client	internal price	internal order date	internal delivery date	comments	client ship date
Tool 1_ind B	Tool 1	B	Y
Tool 1_ind C	Tool 1	C	X
Tool 2_ind A	Tool 2	A	X
Tool 2_ind A	Tool 2	A	Y
tool 3_ind A	tool 3	A	X
tool 3_ind A	tool 3	A	Y
Tool 4_ind A	Tool 4	A	X