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