Copy paste usedrange data and delete duplicate rows

[Ravi321]

Hi friends,

I need your help, below are the things which i am trying for my code to perform.

• Copy data from range B5:F13 (range will not be fixed)
• Go to sheet “Archive” paste copied range as value from cell A3
• Delete duplicate rows from sheet “Archive” (Like in the sample file last two rows are duplicate so they should get deleted) may be concatenation formula be used for it
• After a month again some new data can be manually added in sheet “Master” below the existing data. So next time when the same VBA code is executed then the whole copied data from “Master” sheet should be copied and pasted in sheet “Archive” below the data which is present earlier. After that again the VBA code should search for the duplicate rows from the entire data and delete it

Link for sample data file
https://www.dropbox.com/s/xfp2aw5wxan6scf/Sample data.xlsm

 

[venkat1926]

See data and formula in G5andH5 formulas copied down. sort according to col H and delete rows manually

Master

*	B	C	D	E	F	G	H
*	*
abc	India	Male	abcIndia23Male46000	*
def	Sri Lanka	Male	defSri Lanka24Male48000	*
ghf	UK	Female	gh***26Female52000	*
klm	UK	Male	klmUK20Male40000	*
opq	USA	Female	opqUSA28Female56000	*
xyz	Africa	Female	xyzAfrica31Female62000	*
shf	Singapore	Male	shfSingapore24Male48000	*
ghf	UK	Female	gh***26Female52000	delete
klm	UK	Male	klmUK20Male40000	delete

<tbody>
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #808080, align: center"]Name[/TD]
[TD="bgcolor: #808080, align: center"]Country[/TD]
[TD="bgcolor: #808080, align: center"]Age[/TD]
[TD="bgcolor: #808080, align: center"]Sex[/TD]
[TD="bgcolor: #808080, align: center"]Salary[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]23[/TD]

[TD="align: right"]46000[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]24[/TD]

[TD="align: right"]48000[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]26[/TD]

[TD="align: right"]52000[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]40000[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]28[/TD]

[TD="align: right"]56000[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]31[/TD]

[TD="align: right"]62000[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]24[/TD]

[TD="align: right"]48000[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]26[/TD]

[TD="align: right"]52000[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]40000[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G5 =CONCATENATE(B5,C5,D5,E5,F5) H6 =IF((COUNTIF($G$5:G6,G6))>1,"delete","") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4