Hide Duplicates but show all columns

Spa

New Member
Joined
Aug 7, 2010
Messages
5
Hi

Can anyone please help?

I have the following test data (the actual data set is immense):
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Test</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Date</TH><TH borderColor=#000000 bgColor=#c0c0c0>Order No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Item No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Status</TH><TH borderColor=#000000 bgColor=#c0c0c0>Expected Qty</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0>03.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0>05.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>7</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>8</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>700</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>9</TD><TD borderColor=#c0c0c0>08.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>70</TD><TD borderColor=#c0c0c0>300</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>11</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>12</TD><TD borderColor=#c0c0c0>04.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>80</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>13</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>14</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

The problem is that I have duplicate "item nos" per "order no". I would like to show only the "recent dated" rows where the duplicates occur and hide the duplicates. The returned data should look like this:

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Test</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Date</TH><TH borderColor=#000000 bgColor=#c0c0c0>Order No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Item No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Status</TH><TH borderColor=#000000 bgColor=#c0c0c0>Expected Qty</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0>03.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0>05.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>7</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>8</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>700</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>9</TD><TD borderColor=#c0c0c0>08.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>70</TD><TD borderColor=#c0c0c0>300</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>12</TD><TD borderColor=#c0c0c0>04.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>80</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>13</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>14</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Many Thanks
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
I couldn't get exactly what you need -- you will have to play with the appropriate columns to match -- but here is the approach;

NOTE -- ASSUMES THAT TABLE IS CALLED tblMain. You will need to adjust the name to suit.

1. Build a query with all fields of the table (all fields, not the *)
2. in the Criteria row for ID, put this (adjusted so you use the correct table name:
Code:
In (SELECT MAX(ID) FROM tblMain AS T WHERE T.[Order No]=tblMain.[Order No] AND T.[item no]=tblMain.[Item no] AND T.Status=tblmain.Status)

This assumes that you need to match up Order No, Item no and Status to get what you want.

Denis
 

Spa

New Member
Joined
Aug 7, 2010
Messages
5
Hi Denis

I have tried it and it works!

Thank you very much for taking the time to help me with this, your instruction were really clear. It really is appreciated.

Thanks again

Peter.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top