VBA Copying data from sheet 1 to sheet 2 not including duplicates

[Bobsta_666]

I have a new problem....
I have a data sheet ("Sheet 1") with raw data, I want to filter/find based on Criteria ("billy") in column ("I"), how do I copy the data within range J2:W1000 based on that (criteria ("billy")) whilst removing blanks and only copying over rows to a new sheet ("Sheet 2") that do not find a match (are not duplicates).

The idea is - I have a data sheet ("Sheet 1") with exported jobs in, I want to bring over all the new jobs to the planning sheet ("Sheet 2") that do not already exist in the planning sheet ("Sheet 2"). The new jobs from ("Sheet 1") only need to add to the bottom of last inputted data of planning sheet ("Sheet 2") it does not need to remove anything from ("Sheet 2")

Consistent Data: in ("Sheet 1") column ("I"), I can use this column to filter/find the Criteria ("billy") for example as this contains common names that do not change
Then ("Sheet 1") Column ("S2:S1000") has a unique number that can be VLookup or Match to ("Sheet 2") column ("K4:K1000"), this can be used to determine whether the job already exists

copy Range ("Sheet 1") is J2:W1000 or (J2:W2 to last row)
paste Range ("Sheet 2") is B:O added to bottom of last row

Hope you find this an easy task because i have been pulling my hair out trying to partially write code and macro record to find a solution to no avail
thanks

 

[skillilea]

Can you share the workbook?

 

[Bobsta_666]

Track Quality Planning -2024-2025 v2.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Attribute Description	Severity Description	Attribute Description	Peak	Location	Position From Value	ELR	TID	Total Start	Total end	Standard Job No	Actioned Work Order	Fault ID	Linespeed	Suspected Repeat	Action Due Date
2	Gauge	Intervention Limit - Track	Gauge < 12 Weeks	-20.39		51.48914:-0.14817	VIR	2100	0m824y	0m824y	009709	7465993	2617537	20	N	05/07/2023
3	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	4.27			VTB1	1200	7m660y	7m880y	009048	75146525	2663856	60	N
4	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	5.00			BBJ	2100	6m440y	6m660y	009048	75305641	2712057	50	N
5	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	4.92			VTB1	1200	7m0y	7m220y	009048	75328607	2713628	60	N
6	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	6.81			BBJ	2100	6m440y	6m660y	009048	76098942	2806566	50	N
7	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	6.51			VTB1	1200	1m1540y	2m0y	009048	77812890	2877601	45	N
8	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	6.40			VTB1	1200	2m1100y	2m1320y	009048	77812891	2877602	60	N
9	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	5.03			VTB1	1200	2m1540y	3m0y	009048	77812892	2877603	60	N
10	Track Quality SD Mean Alignment 35m	Super Red - Track	Track Quality SD Mean Alignment 35m	9.77			BSP2	2100	2m220y	2m375y	009046	77812973	2883133	25	N
11	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	5.50			VTB1	1100	6m220y	6m440y	009048	77812893	2883137	70	N
12	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	4.51			VIR	2100	1m880y	1m1100y	009048	77812894	2913335	45	N
13	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	7.19			BBJ	2100	6m440y	6m660y	009048	77812895	2920668	50	N
14	Track Quality SD Mean Alignment 35m	Super Red - Track	Track Quality SD Mean Alignment 35m	10.03			SLJ	2200	6m143y	6m220y	009046	77812974	2920745	20	N
15	Track Quality SD Mean Alignment 35m	Very Poor Eighth - Track	Track Quality SD Mean Alignment 35m	5.75			SLJ	2200	6m220y	6m440y	009048	77812896	2920746	20	N
16	Track Quality SD Worst Top 35m	Very Poor Eighth - Track	Track Quality SD Worst Top 35m	6.03			WLL	1100	5m220y	5m440y	009048	77812937	2924843	60	N
17	Track Quality SD Worst Top 35m	Super Red - Track	Track Quality SD Worst Top 35m	6.37			WLL	1100	5m660y	5m880y	009046	77812984	2924844	60	N
18	Track Quality SD Worst Top 35m	Super Red - Track	Track Quality SD Worst Top 35m	6.96			WLL	1100	5m880y	5m1100y	009046	77812985	2924845	60	N
19	Track Quality SD Worst Top 35m	Super Red - Track	Track Quality SD Worst Top 35m	6.64			WLL	1100	5m1100y	5m1320y	009046	77812986	2924846	60	N
Calculate

Cell Formulas
Range	Formula
H2:H19	H2	=HLOOKUP(Code!$A$10,Data!$A$1:$FK$1000,ROW(A2),FALSE)& ""
I2:I19	I2	=HLOOKUP(Code!$A$16,Data!$A$1:$FK$1000,ROW(A2),FALSE)
J2:J19	J2	=H2&" " &G2
K2:K19	K2	=HLOOKUP(Code!$A$12,Data!$A$1:$FK$1000,ROW(A2),FALSE)
L2:L19	L2	=""
M2:M19	M2	=IFERROR(HLOOKUP(Code!$A$15,Data!$A$1:$FK$1000,ROW(A2),FALSE),"")
N2:N19	N2	=HLOOKUP(Code!$A$8,Data!$A$1:$FK$1000,ROW(A2),FALSE)
O2:O19	O2	=HLOOKUP(Code!$A$9,Data!$A$1:$FK$1000,ROW(A2),FALSE)
P2:P19	P2	=B2&"m"&C2&"y"
Q2:Q19	Q2	=E2&"m"&F2&"y"
R2:R19	R2	=HLOOKUP(Code!$A$11,Data!$A$1:$FK$1000,ROW(A2),FALSE)
S2:S19	S2	=HLOOKUP(Code!$A$5,Data!$A$1:$FK$1000,ROW(A2),FALSE)
T2:T19	T2	=HLOOKUP(Code!$A$6,Data!$A$1:$FK$1000,ROW(A2),FALSE)
U2:U19	U2	=IFERROR(HLOOKUP(Code!$A$14,Data!$A$1:$FK$1000,ROW(A2),FALSE),"")
V2:V19	V2	=IFERROR(HLOOKUP(Code!$A$13,Data!$A$1:$FK$1000,ROW(A2),FALSE),"")
W2:W19	W2	=HLOOKUP(Code!$A$7,Data!$A$1:$FK$1000,ROW(A2),FALSE)

 

[Bobsta_666]

this is ("Sheet 1") this sheet is named "Calculate"

Range from this to copy to "L2 Planning Sheet" is J2:W bottom of column

**Ignore the formula in cells**

 

[Bobsta_666]

	Track Quality Planning
0	Description Of Work/Fault	Value	Location	GPS	ELR	TID	Fault From	Fault To	STD Number	Work Order	TIGER ID	Speed	Repeat	Due Date	TV Date	Comments	Week Planned	Status
	Gauge < 12 Weeks	-20.39		51.48914:-0.14817	VIR	2100	0m824y	0m824y	009709	74659900	2617537	20	N	05/07/2023
	Gauge < 14 Days	25.17		51.46965:-0.15793	CKL	2100	2m468y	2m468y	009117	76681331	2930618	40	N	06/10/2023
	Mean Alignment 35m < 4 Weeks	-28.41		51.47820:-0.14778	VTB1	3200	1m446y	1m446y	009115	77566599	3048270	20	N	09/01/2024
	Mean Alignment 35m < 4 Weeks	29.71		51.47833:-0.14779	VTB1	3200	1m430y	1m430y	009115		3048271	20	N	09/01/2024
	Mean Alignment 35m < 4 Weeks	43.01		51.47877:-0.14778	VTB1	3200	1m377y	1m377y	009115	77566609	3048273	20	N	09/01/2024
	Mean Alignment 35m < 14 Days	-21.71		51.47828:-0.14776	VTB1	1100	1m436y	1m436y	009115		3048329	45	N	26/12/2023
	Mean Alignment 35m < 4 Weeks	-30.35		51.42007:-0.13925	SNS	3400	6m872y	6m872y	009115	77813078	3056904	20	N	16/01/2024
	Mean Alignment 35m < 4 Weeks	41.32		51.47033:-0.15696	BSP2	2100	2m327y	2m327y	009115	77813079	3056926	25	N	16/01/2024
	Cyclic Top 18m Right < 1 Month	26.97		51.47086:-0.15553	BSP2	2100	2m201y	2m219y	009308	77813112	3056955	25	N	18/01/2024
	Cyclic Top 18m Left < 1 Month	26.29		51.47086:-0.15553	BSP2	2100	2m201y	2m219y	009308	77813111	3056962	25	N	18/01/2024
	Cyclic Top 18m Both < 14 Days	53.25		51.47086:-0.15553	BSP2	2100	2m201y	2m219y	009308	77813110	3056968	25	N	02/01/2024
	Twist 3m < 14 Days	-15.85		51.47028:-0.16334	SCC	2100	3m883y	3m883y	009308	77813103	3075544	30	N	23/01/2024
	Mean Alignment 35m < 4 Weeks	23.73		51.47030:-0.16356	SCC	2100	3m899y	3m899y	009115	77813080	3075545	30	N	06/02/2024
	Mean Alignment 35m 7 Days	-37.74		51.49642:-0.20823	WLL	2100	3m713y	3m713y	009115	77813081	3075546	60	N	16/01/2024
	Mean Alignment 35m < 14 Days	41.36		51.49651:-0.20837	WLL	3500	3m728y	3m728y	009115	77813082	3075547	25	N	23/01/2024
	Mean Alignment 35m < 14 Days	-29.40		51.50033:-0.21308	WLL	3500	3m1314y	3m1314y	009115	77813083	3075548	25	N	23/01/2024
	Mean Alignment 35m < 4 Weeks	-22.41		51.47096:-0.16844	CJL	1100	0m1486y	0m1486y	009115	77813084	3075762	20	N	06/02/2024
	Mean Alignment 35m < 4 Weeks	21.17		51.47088:-0.16801	CJL	1100	0m1452y	0m1452y	009115	77813085	3075763	20	N	06/02/2024
	Mean Alignment 35m 7 Days	-37.74		51.49642:-0.20823	WLL	2100	3m713y	3m713y	9115	77813081	3075546	60	N	16/01/2024
	Mean Alignment 35m 7 Days	-37.74		51.49642:-0.20823	WLL	2100	3m713y	3m713y	9115	77813081	3075546	60	N	16/01/2024
	Gauge < 12 Weeks	-20.39		51.48914:-0.14817	VIR	2100	0m824y	0m824y	009709	7465993	2617537	20	N	05/07/2023

 

[Bobsta_666]

this is ("Sheet 2)
Sheet name "L2 Planning Sheet"

 

[Bobsta_666]

Range of "L2 Planning sheet" B:S, to be copied to the bottom of row as if adding new faults to list.
before copying faults from "calculate" to "L2 Planning Sheet" i want to filter out variables such as ("Super Red - Track") based on Sheet("calculate")Column (I)

And no duplicates to be copied over based on match of Sheet("calculate").Range(S2nward) & Sheet("L2 Planning Sheet").Range(K4nwards)

Sorry if i am confusing you

 

[skillilea]

OK

Let's back up a bit.

A few questions:
1. You want to copy data from the "Raw" sheet that doesn't exist in the production sheet based on some criteria.

Where are you searching for the criteria?

2. You need the raw data to not bring over blank rows, I didn't see any in the example above.

3. Is there any uniqueness to the row in Raw data, ie. a unique number or code?

What am I missing? I'm a bit confused but, would love to help.

 

[Bobsta_666]

1. You want to copy data from the "Raw" sheet that doesn't exist in the production sheet based on some criteria.
The raw sheet I meant ("Calculate") sheet, there is a range in this sheet that is in the exact same order as the planning sheet ("L2 Planning Sheet") these are
("Calculate").Range("J2:W")
("L2 Planning Sheet").Range(B4:S)

These can be used to copy/paste

Where are you searching for the criteria?
The filter column in the ("Calculate") sheet (I) has the generic titles that can be used to filter what I require out of the data for example ("Super Red - Track")

2. You need the raw data to not bring over blank rows, I didn't see any in the example above.
The way I was doing it, was using filters to remove unwanted rows and then copying the range over to the ("L2 planning Sheet") but this was leaving all the blank cells still present.
So there is definitely a better way of transferring the information without copy and paste

3. Is there any uniqueness to the row in Raw data, ie. a unique number or code?

The Unique number that is in both sheets is located -
("Calculate").Range("S2:Bottom")
("L2 Planning Sheet").Range("K4:Bottom)

The work order number in these cells can determine whether it already exists in

What am I missing? I'm a bit confused but, would love to help.
Sorry i was jumping the Gun, thanks for the help

 

[skillilea]

OK give me a couple hours. I'll put something together.