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

So you are looking for someone to write the code for you?

Right now there's not enough information in your request to determine
exactly how to help you. Which column on the sheets has the information that
uniquely identifies an entry is just one thing needed to be known. Which
columns contain the information? You mentioned sorting in 'desc' order -
what column does that stuff show up in? It could be done without knowing
that right now, but things like that are nice to know.

"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

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

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

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

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

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

Hi.... Thanks for the help... I am still stuck...

the code gives an error when executed... the error msg is : compile error.
Cannot define a public user-defined type within an object module

Not very familiar with vb coding in excel. Plz help.

Nikhil

"JLatham" wrote:

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

I think maybe you've copied the code into the wrong place in your workbook.
Go to where ever it is now and delete it all and follow these instructions:

Get the code ready to copy and paste.

Open up the Excel workbook. use [Alt]+[F11] to get to the VB Editor. Once
there use
Insert | Module
just plain 'Module' not Class Module or UserForm or anything. A clean white
'sheet' should show up for you. Paste the code directly into it. Use 'Debug
| Compile...' from the menu bar to verify that it compiles. If the sheet
already had the phrase "Option Explicit" at the top of it, you'll get an
error about duplicate Option statements. Just delete one of those two lines.
Check with Debug | Compile... again to make sure. Should run.

If this still doesn't get you past the problem. Send your workbook to me
and I'll put the code into it and send it back to you. Email as attachment
to 2kmaro at DSLR.net (replace " at " with @ symbol, naturally).



"Nikhil" wrote:

Hi.... Thanks for the help... I am still stuck...

the code gives an error when executed... the error msg is : compile error.
Cannot define a public user-defined type within an object module

Not very familiar with vb coding in excel. Plz help.

Nikhil

"JLatham" wrote:

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