"order by" and removing newer records
 

Hello:

I have a spreadsheet that has the following columns:

Sales Document Number
Sales Customer PO Number
Item Number
Time of Day of Document Number Entry

There are other columns, actually, but these are the important ones.

I need to (a) sort this spreadsheet in order by Time of Day, Sales Document
Number, Sales Customer PO Number, and Item Number and (b) remove the records
with the more recent time of day.

The reason I want to do this is because there are a few records in this
spreadsheet with duplicate records. The only way to remove these duplicates
is to remove the records with the more recent time of day and in the column
order that I just specified.

When I say "more recent time of day", this would mean that 8AM needs to be
removed instead of 7AM.

Any ideas? I hope that it does not involve VBA programming, as I do not
know this.

Thanks!

childofthe1980s

"order by" and removing newer records
 

The built-in (advanced) sort- at least in Excel 2003 (you didn't mention
which version you use) only has 3 sort criteria. So, sort your table by the
least important criteria first, then go back and use the triple-sort to do
your main sort- the fourth column should retain as much of it's sorting as it
can through the sorting of the other three columns.

To avoid using VBA, use a cell formula or conditional formula to highlight
duplicate rows, making them easier to see and remove manually. For example,
assuming your sales document number is the key/unique field, and it is in
column A, I'd put the following formula in cell E2:
=if(A2=A1,"*****","")
and copy the formula down. As long as your data is sorted with the unique
field in the highest position (primary sort) then this put ***** in cells
where the item above is a duplicate. Depending on whether you are sorting
time of day ascending or descending, you either delete the row(s) with *****
or the row(s) above.

If you only have a few hundred records, this isn't a bad way to go. If you
have thousands of records, VBA may not be a bad idea, and if you are willing
to learn, this group is a great resource.

HTH,
Keith

"childofthe1980s" wrote:

Hello:

I have a spreadsheet that has the following columns:

Sales Document Number
Sales Customer PO Number
Item Number
Time of Day of Document Number Entry

There are other columns, actually, but these are the important ones.

I need to (a) sort this spreadsheet in order by Time of Day, Sales Document
Number, Sales Customer PO Number, and Item Number and (b) remove the records
with the more recent time of day.

The reason I want to do this is because there are a few records in this
spreadsheet with duplicate records. The only way to remove these duplicates
is to remove the records with the more recent time of day and in the column
order that I just specified.

When I say "more recent time of day", this would mean that 8AM needs to be
removed instead of 7AM.

Any ideas? I hope that it does not involve VBA programming, as I do not
know this.

Thanks!

childofthe1980s

"order by" and removing newer records
 

The built-in (advanced) sort- at least in Excel 2003 (you didn't mention
which version you use) only has 3 sort criteria. So, sort your table by the
least important criteria first, then go back and use the triple-sort to do
your main sort- the fourth column should retain as much of it's sorting as it
can through the sorting of the other three columns.

To avoid using VBA, use a cell formula or conditional formula to highlight
duplicate rows, making them easier to see and remove manually. For example,
assuming your sales document number is the key/unique field, and it is in
column A, I'd put the following formula in cell E2:
=if(A2=A1,"*****","")
and copy the formula down. As long as your data is sorted with the unique
field in the highest position (primary sort) then this put ***** in cells
where the item above is a duplicate. Depending on whether you are sorting
time of day ascending or descending, you either delete the row(s) with *****
or the row(s) above.

If you only have a few hundred records, this isn't a bad way to go. If you
have thousands of records, VBA may not be a bad idea, and if you are willing
to learn, this group is a great resource.

HTH,
Keith

"childofthe1980s" wrote:

Hello:

I have a spreadsheet that has the following columns:

Sales Document Number
Sales Customer PO Number
Item Number
Time of Day of Document Number Entry

There are other columns, actually, but these are the important ones.

I need to (a) sort this spreadsheet in order by Time of Day, Sales Document
Number, Sales Customer PO Number, and Item Number and (b) remove the records
with the more recent time of day.

The reason I want to do this is because there are a few records in this
spreadsheet with duplicate records. The only way to remove these duplicates
is to remove the records with the more recent time of day and in the column
order that I just specified.

When I say "more recent time of day", this would mean that 8AM needs to be
removed instead of 7AM.

Any ideas? I hope that it does not involve VBA programming, as I do not
know this.

Thanks!

childofthe1980s

"order by" and removing newer records
 

Thanks, ker_01. My apologies. I forgot to mention the version of Excel.
It's 2003.

Well, if you or anyone else has any ideas on how to program with VBA on how
to eliminate rows with the ***** in them, that would be great.

If I don't hear back on this thread, I'll start a new posting.

childofthe1980s

"ker_01" wrote:

The built-in (advanced) sort- at least in Excel 2003 (you didn't mention
which version you use) only has 3 sort criteria. So, sort your table by the
least important criteria first, then go back and use the triple-sort to do
your main sort- the fourth column should retain as much of it's sorting as it
can through the sorting of the other three columns.

To avoid using VBA, use a cell formula or conditional formula to highlight
duplicate rows, making them easier to see and remove manually. For example,
assuming your sales document number is the key/unique field, and it is in
column A, I'd put the following formula in cell E2:
=if(A2=A1,"*****","")
and copy the formula down. As long as your data is sorted with the unique
field in the highest position (primary sort) then this put ***** in cells
where the item above is a duplicate. Depending on whether you are sorting
time of day ascending or descending, you either delete the row(s) with *****
or the row(s) above.

If you only have a few hundred records, this isn't a bad way to go. If you
have thousands of records, VBA may not be a bad idea, and if you are willing
to learn, this group is a great resource.

HTH,
Keith

"childofthe1980s" wrote:

Hello:

I have a spreadsheet that has the following columns:

Sales Document Number
Sales Customer PO Number
Item Number
Time of Day of Document Number Entry

There are other columns, actually, but these are the important ones.

I need to (a) sort this spreadsheet in order by Time of Day, Sales Document
Number, Sales Customer PO Number, and Item Number and (b) remove the records
with the more recent time of day.

The reason I want to do this is because there are a few records in this
spreadsheet with duplicate records. The only way to remove these duplicates
is to remove the records with the more recent time of day and in the column
order that I just specified.

When I say "more recent time of day", this would mean that 8AM needs to be
removed instead of 7AM.

Any ideas? I hope that it does not involve VBA programming, as I do not
know this.

Thanks!

childofthe1980s

"order by" and removing newer records
 

Hi ker_01:

Disregard my most recent message.....the client has decided that VBA is not
necessary. Your steps worked like a charm!!! Thank you, so much!!!

childofthe1980s

"ker_01" wrote:

The built-in (advanced) sort- at least in Excel 2003 (you didn't mention
which version you use) only has 3 sort criteria. So, sort your table by the
least important criteria first, then go back and use the triple-sort to do
your main sort- the fourth column should retain as much of it's sorting as it
can through the sorting of the other three columns.

To avoid using VBA, use a cell formula or conditional formula to highlight
duplicate rows, making them easier to see and remove manually. For example,
assuming your sales document number is the key/unique field, and it is in
column A, I'd put the following formula in cell E2:
=if(A2=A1,"*****","")
and copy the formula down. As long as your data is sorted with the unique
field in the highest position (primary sort) then this put ***** in cells
where the item above is a duplicate. Depending on whether you are sorting
time of day ascending or descending, you either delete the row(s) with *****
or the row(s) above.

If you only have a few hundred records, this isn't a bad way to go. If you
have thousands of records, VBA may not be a bad idea, and if you are willing
to learn, this group is a great resource.

HTH,
Keith

"childofthe1980s" wrote:

Hello:

I have a spreadsheet that has the following columns:

Sales Document Number
Sales Customer PO Number
Item Number
Time of Day of Document Number Entry

There are other columns, actually, but these are the important ones.

I need to (a) sort this spreadsheet in order by Time of Day, Sales Document
Number, Sales Customer PO Number, and Item Number and (b) remove the records
with the more recent time of day.

The reason I want to do this is because there are a few records in this
spreadsheet with duplicate records. The only way to remove these duplicates
is to remove the records with the more recent time of day and in the column
order that I just specified.

When I say "more recent time of day", this would mean that 8AM needs to be
removed instead of 7AM.

Any ideas? I hope that it does not involve VBA programming, as I do not
know this.

Thanks!

childofthe1980s