I've modified that chunk of code to examine the contents of all the data in
each row and look for duplicates. So everything in each cell with data on a
row must be an exact match for it NOT to copy it to new sheet. In this one
you tell it which column is the very first one that could have data in it and
then tell it which is the last one that could have data in it. In either
case, if there are some cells empty in either column, it still needs the IDs
for any that may have data sometimes.

It is very literal minded and entries that may look like they should be
duplicates may get identified as distinct because of any number of minor
variations in the content. Only a review of the final, sorted data by human
eye will catch those.

Replace the previous code with what you'll find he
http://www.jlathamsite.com/uploads/G...tRowData.tx t


"Nikhil" wrote:

One way to go i feel...and i tried that...it works...

i manually carried out this .....select all reqd columns in sheet 1 and used
advanced filter to copy distinct rows to another range in the same
worksheet...

simillary do the same in other worksheets...

I recorded the macro for doing this... now the problem that i get stuck is...

advanced filter copies data in the same worksheet....while i want it to get
copied in another worksheet..
the other problem is say...the macro has copied 100 distinct rows from sheet
1 to sheet 3..then it should copy the distinct rows from the second worksheet
in row 101 onwards on sheet 3...

should you be able to help me... the macro works well to filter unique
records and copy them to another set of cells in the same worksheet and then
copy the unique set to anotehr work sheet using copy-paste.

Regards

Nikhil

"JLatham" wrote:

The code I provided earlier depends on the information in one column being a
'discriminator' - something like a serial number or name of something. It
works on a situation like this, for example:
Sheet1
John 5 7 9 3
John 5 7 9 3
Bill 6 2 1 4

Sheet2
John 5 7 9 12
Amy 3 7 8 14
Bill 6 8 1 12

Gives:
John 5 7 9 3
Bill 6 2 1 4
Amy 3 7 8 14

But what you are saying is that every cell on every row has to be compared
with every cell on every other row on every sheet to determine if it is
repeated data. The code could be modified to do that - I'll work on that.
You just have to realize that if there is a lot of data, then it is going to
take a lot of time to perform all of the work.

"Nikhil" wrote:

i want to copy distinct rows of data.....

e.g. i have three (or more) sheets

Sheet1 -

A B C D
1 2 3 4
5 6 7 8
5 6 7 8

and sheet2 -

A B C D
8 9 10 11
5 6 7 8
12 13 14 15

the result i want to compile in say sheet3 should be

A B C D
1 2 3 4
5 6 7 8
8 9 10 11
12 13 14 15

thereafter using vlookup i can get the sales figure in col E....
now i would like the data in sheet3 to automatically get sorted on the
sales figures in col E.

Plz note that i would be adding data to new sheets...all of which should
be
compiled in sheet3

any of the column in sheet 1 or 2 might contain a blank cell

Hope you could help me yet again...

Regards

Nikhil

"JLatham" wrote:

I worked up some very generic code that will collate all uniquely
identifiable information in a workbook onto a single sheet. It will just ask
you for the column in which the "unique" information can be found in. Copy
the code from this file:

http://www.jlathamsite.com/uploads/G...ForCopying.txt

and paste it into a code module in your workbook. Use [Alt]+[F11] to open
the VB Editor, use Insert | Module if you need to create a place to copy the
code into. Once that's done run it like you would a regular Macro, since it
is just a regular macro.

It also sorts by the unique column that you provide to it. It's
non-destructive, meaning it does not alter any existing information in your
workbook at all. You should be all set up to start composing your VLOOKUP()
formulas.




"Nikhil" wrote:

I have data in some sheets (the same data can be repeated in different
sheets)..I want to compile distinct records from these worksheets into
another sheet...prefereably using vba code.

I do not want to use a formula...since once i get the distinct data...i want
to use vlookup to get sales figures and then sort the data on sales...

I also want that the data automatically gets sorted in the compiled sheet in
desc order.

Plz help.

Nikhil