Excel VBA Script
Hi,
Could anyone help me please with a piece of Excel Vba. I have attached a sample xlsx which has two sheets.
Sheet one (Before VBA) is my source data
Sheet two (After VBA) is my required result.
In a nutshell I have a spreadsheet of many thousands of rows. It is sorted by document id. Where the document id is not duplicated in the dataset, the only action required is to copy the entire row (as is) to the target sheet. Where a document id is repeated through several rows, these need to be reduced into one single row, with the unique cell value (pool) being moved from each of the duplicated rows to the first row and the duplicate rows deleted. Hopefully that will make sense when you open the sample sheet.
sample.xlsx
Could anyone help me please with a piece of Excel Vba. I have attached a sample xlsx which has two sheets.
Sheet one (Before VBA) is my source data
Sheet two (After VBA) is my required result.
In a nutshell I have a spreadsheet of many thousands of rows. It is sorted by document id. Where the document id is not duplicated in the dataset, the only action required is to copy the entire row (as is) to the target sheet. Where a document id is repeated through several rows, these need to be reduced into one single row, with the unique cell value (pool) being moved from each of the duplicated rows to the first row and the duplicate rows deleted. Hopefully that will make sense when you open the sample sheet.
sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Macroshadow is there a reason why to change the code? I am not a VBA expert so I was just curious.
Sheet1 is the sheet code name and Sheets(1) will mean whatever sheet's index is 1.
I used Sheet1 code name because if user changes it's name, it will still refer to the same sheet.
Of course that can be tweaked as per the requirement.
Like
I used Sheet1 code name because if user changes it's name, it will still refer to the same sheet.
Of course that can be tweaked as per the requirement.
Like
Set Sws = Sheets("Sheet1")
@Subodh thanks for the explanation. :)
You're welcome Edward! :)
ASKER
Thanks Subodh, I will give this a go.
ASKER
Great solution Subodh, worked perfectly
Thanks
Thanks
You're welcome Diane! Glad it worked.
Just change
Open in new window
toOpen in new window