P
peter_cawley
I was hoping that somebody out there would be able to help me out with
a problem that I have:
I have an Excel 2000 spreadsheet with data in it from columns A to O
and approximately 50,000 rows, which holds information on rates per
unit. Unfortunately, the problem lies in that the data contains
duplicate data as the file holds historic rate changes and what I'm
looking to do is drag out the newest rate change for each rate section.
The key data contains:
Column A contains a contract number
Column B contains a registration number
Column D contains the rate section
Column F contains the date of last rate change
Column H contains the rate
An example of the data in the rows would be:
A B D F H
1 1000 W123ABC FIXED 01/01/06 1000.00
2 1000 W123ABC FIXED 01/10/05 900.00
3 1000 W123ABC DIST 01/01/06 1.00
4 1000 W123ABC DIST 01/10/05 0.95
5 999 Y789XYZ FIXED 01/11/05 500.00
6 999 Y789XYZ FIXED 31/03/05 475.00
7 999 Y789XYZ DIST 01/11/05 0.56
8 999 Y789XYZ DIST 31/03/05 0.50
What I would like to be able to do is:
For each contract (col A), Unit (col B) and Rate (col D) I would like
to be able to extract the latest rate change (col F) row and transfer
it to another spreadsheet. In the example above I would be hoping that
rows 1, 3, 5 & 7 would be extracted into another spreadsheet, ideally
in one tab.
Thanks,
Pete.
a problem that I have:
I have an Excel 2000 spreadsheet with data in it from columns A to O
and approximately 50,000 rows, which holds information on rates per
unit. Unfortunately, the problem lies in that the data contains
duplicate data as the file holds historic rate changes and what I'm
looking to do is drag out the newest rate change for each rate section.
The key data contains:
Column A contains a contract number
Column B contains a registration number
Column D contains the rate section
Column F contains the date of last rate change
Column H contains the rate
An example of the data in the rows would be:
A B D F H
1 1000 W123ABC FIXED 01/01/06 1000.00
2 1000 W123ABC FIXED 01/10/05 900.00
3 1000 W123ABC DIST 01/01/06 1.00
4 1000 W123ABC DIST 01/10/05 0.95
5 999 Y789XYZ FIXED 01/11/05 500.00
6 999 Y789XYZ FIXED 31/03/05 475.00
7 999 Y789XYZ DIST 01/11/05 0.56
8 999 Y789XYZ DIST 31/03/05 0.50
What I would like to be able to do is:
For each contract (col A), Unit (col B) and Rate (col D) I would like
to be able to extract the latest rate change (col F) row and transfer
it to another spreadsheet. In the example above I would be hoping that
rows 1, 3, 5 & 7 would be extracted into another spreadsheet, ideally
in one tab.
Thanks,
Pete.