VBA to Find Duplicate Row & Clear Contents of Cell in Column

[LA_256]

It's been 10 years since I was in VBA and I'm very rusty so any assistance on my current project is greatly appreciated. We are trying to do an invoice upload to our system instead of manually enter every invoice.
I've written the vba to take data from one sheet to a new sheet with no issue including duplicating rows that have values in column M. Where I'm stuck is I need to locate those duplicate rows and clear the contents in contents that duplicate row so that it is no longer a duplicate.
Right now, it copies the data that has "Ready to Invoice", has not been sent (No "Yes" in Column W), and is not waiting for PO (Column S). Once it pulls that in, it goes back through and duplicates any rows that are not blank in Per Diem (Column M). The reason we duplicate is because the Per Diem will have to be a separate line on the invoice and our import process requires it to be a separate row in excel in order to do that.
If clearing the contents in Price of the duplicate row is not an option, then I need to have the duplicate row highlighted so I can manual update Price column for the duplicates.
The data can be sorted by Date, Billing, Site, and customer if it helps as that will have to happen before we upload.
Just an fyi.. the image only shows a few rows, but by December our billing tracker will be close to 20,000 rows. It grows by almost 5,000 rows a year.

 

[OaklandJim]

It is challenging, for me at least, to provide assistance without having something to work with. Is it possible to post a workbook with real or at least realistic data. Use the link icon above the message area.

 

[LA_256]

It is challenging, for me at least, to provide assistance without having something to work with. Is it possible to post a workbook with real or at least realistic data. Use the link icon above the message area.

Sorry for the delay. Attached is the sample sheet.

Invoice Import Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Date	State	Freq.	Billing	Site	Customer	LOCATION	TECH	PDP Uploaded by Tech	Report	Piles	Check In		PRICE	MM	Billing	Product Service	Notes	Just	PO#	Status	Invoice #	Invoice
2	5/11/2023	OK		116	116-008	Delta Division	Elkhart	JHill	5/14/2023 22:00	15-May	60			$1,650.00		Sampling	Stock		Waiting
3	5/11/2023	OK		116	116-008	Delta Division	Elkhart	JHill	5/14/2023 22:00	19-May	N/A					Flight	Services			6300442983	Tim Review
4	5/11/2023	WA		190	190-001	Company ABC	Seattle	Jordan	5/12/2023 6:00	15-May	33		$150.00	$2,000.00		Sampling	Stock			4501709543	Ready to Invoice
5	5/11/2023	OH		116	116-020	Delta Division	Lexington	Daniel	5/12/2023 8:00	23-May	N/A					Flight	Services
6	5/11/2023	FL		116	116-018	Delta Division	Orlando	Daniel	5/12/2023 8:00	18-May	N/A			$1,800.00		Flight	Services			6300442902	Ready to Invoice
7	5/11/2023	IL		116-000-000	116-000-019	Prairie Materials	McDowell	Daniel	5/12/2023 8:00	17-May	26		$75.00	$1,650.00		Sampling	Stock		Waiting
8	5/11/2023	IL		116	116-019	Prairie Materials	McDowell	Daniel	5/12/2023 8:00	22-May	N/A			$1,800.00		Flight	Services			6300442935	Ready to Invoice	23244	Yes
9	5/11/2023	TX	Q	424	424-001	WC Industries	Dallas	Chase	5/11/2023 20:00	24-May	N/A			$2,400.00		Flight	Services			N/A	Ready to Invoice
10	5/11/2023	VA	Q	424	424-002	WC Industries	HRRC	Paul	5/11/2023 12:00	23-May	N/A		$200	$2,400.00		Flight	Services			N/A	Ready to Invoice	23246	Yes
11
12	5/12/2023	TX	Q	106	106-027	ABC LLC	Amarillo	Jackson	5/12/2023 16:00	24-May	N/A			$2,800.00		Flight	Services			30629917	Ready to Invoice	23247	Yes
13	5/12/2023	AR		125	125-008	Xotic System	Forman	Darrell	5/12/2023 20:00	22-May	N/A			$4,400.00		Flight	Services
14	5/12/2023	IL		208	208-002	TMS	Manteno	Daniel	5/12/2023 22:00	16-May	2			$1,950.00		Sampling	Stock
Billing Tracker

 

[OaklandJim]

I've written the vba to take data from one sheet to a new sheet with no issue including duplicating rows that have values in column M.

So your code duplicates rows with Per Diem value in Column M. And then the first row has just price and the second row has the Per Diem?

Where I'm stuck is I need to locate those duplicate rows and clear the contents in contents that duplicate row so that it is no longer a duplicate.

This is confusing to me. You need to locate the duplicate rows and clear some data in the duplicate (second) row so "it is no longer a duplicate"?

Right now, it copies the data that has "Ready to Invoice", has not been sent (No "Yes" in Column W), and is not waiting for PO (Column S).

So your code copies data whose 1. value in the "Status" column (column U) is "Ready to Invoice", 2. value in "Invoice" column (column W) in not "Yes" and 3. value in "Just" column (column S) in not "Waiting"?

Once it pulls that in, it goes back through and duplicates any rows that are not blank in Per Diem (Column M). The reason we duplicate is because the Per Diem will have to be a separate line on the invoice and our import process requires it to be a separate row in excel in order to do that.

I cannot tell, is "it" your code or code that you need? Whichever, you need a "duplicate row" where the above qualifications are met and for which there is a value for Per Diem (column M)?

If clearing the contents in Price of the duplicate row is not an option, then I need to have the duplicate row highlighted so I can manual update Price column for the duplicates.

Why can contents of "Price" column (column N) not be clearable?

The data can be sorted by Date, Billing, Site, and customer if it helps as that will have to happen before we upload.

Of course code can do this.

Just an fyi.. the image only shows a few rows, but by December our billing tracker will be close to 20,000 rows. It grows by almost 5,000 rows a year.

Is the data that you posted enough to develop and test code? If there is not more than one or two instances of the circumstances that need processing then you'll need to provide more.