Excel Help

gazzae · 22-04-2005, 09:25

Hi All,

A quick bit of Excel help please. I have a sheet that contains data shown below....

11/09/2000 A0052 0.0105
29/09/2000 A0052 0.0205
13/10/2000 A0052 0.0314
20/09/2002 A0347 1.0389
29/09/2002 A0347 1.0455
13/10/2002 A0347 2.0214

What I want to be able to do is fliter the sheet in someway so that it only keeps the latest date by each "A" number. So the sheet above would become...

13/10/2000 A0052 0.0314
13/10/2002 A0347 2.0214

Anyone any ideas, or will I need to write some code to do it? (I would prefer not too!)

Thanks

Gareth

Nemesis · 22-04-2005, 09:50

The problem is that all the A numbers will be in the same column ... so it looks like a coding situation to get the result you want.

gazzae · 22-04-2005, 10:15

I thought as much. Time to dig out "My Big Book of VBA".

Raistlin · 22-04-2005, 10:20

Depending on what you are actually trying to achieve with this, you might find you get better results with Access.....

Just a thought.....

Nemesis · 22-04-2005, 10:24

Quote:

Originally Posted by Raistlin

Depending on what you are actually trying to achieve with this, you might find you get better results with Access.....

Just a thought.....

Great minds ...

gazzae · 22-04-2005, 10:33

The data above has been pulled out of an MRP system. To give you a bit of background, some users weren't using the system correctly and we putting any old cost in the PO and only putting the correct cost in when the invoice from the supplier arrived. This meant that the cost on some parts is wrong as they were being updated with the wrong cost at GRN time. (Hope that makes sense).

What I am trying to do is a one off report with compares the last invoice price with the current price on the part and highlight any differences.

So the data above is from the invoice table, and I only want to have the cost off the most current invoice by"A" number and dump the rest. I can probably do this with code but was hoping for a quick and easy way.

MetaWraith · 22-04-2005, 11:03

There is a relatively quick and painless solution.

Assuming your data is sorted by Order number and then by Date
Put this formula in a the cell coresponding to your first row of data, but within a new column.

=IF(OrderNumberCell1=OrderNumberCell2,0,1)

Change OrderNumberCell1 and OrderNumberCell2 to appropriately to match your table of course.

Drag the cell to replicate it throughout the column, and you will have a column of ones and zeros, ones being rows you want to keep.

Turn on the Autofilter and use it select just those rows with ones in your new column and Roberts your Mother's Brother.

gazzae · 22-04-2005, 11:14

FANTASTIC!!!

Thank you MetaWraith.

MetaWraith · 22-04-2005, 11:22

Quote:

Originally Posted by gazzae

FANTASTIC!!!

Thank you MetaWraith.

You're welcome,

Thinks about making a comment about great minds and fools seldom differing but decided against it in case Nem's watching

Nemesis · 22-04-2005, 11:24

Quote:

Originally Posted by MetaWraith

You're welcome,

Thinks about making a comment about great minds and fools seldom differing but decided against it in case Nem's watching

*slap*