LinkBack URL
About LinkBacks
Hi All,
New to the site as I'm in need of help for a spread sheet I am compiling for work.
Basically it's an overstocking sheet, I've used a SQL query to get some of the information required from our system, but I need to split some of it out and do some separate functions to get some other data (plus I'm not that good to do it all in SQL yet).
Basically I have 2 columns that I am working from. The first column contains part numbers which have been sold since the beginning of this Financial year (so April 1st) The second column is the number of months since a part was sold. e.g. PARTA1 was last sold in April, So column A contains "PARTA1" and Column B contains "7" as April was 7 months ago.
Hopefully I'm making sense so far.
The problem is duplication. PARTA1 was sold 3 times this year, in April, June and September, giving me 3 rows of information for just one part as below:
Column1 Column2
PARTA1 7
PARTA1 5
PARTA1 2
Obviously then we have other part numbers which could have been sold multiple times or only the once, giving a table which looks something like this but on a much much larger scale (thousands of lines):
Column1 Column2
PARTA1 7
PARTA1 5
PARTA1 2
PARTB2 0
PARTH4 1
PARTZ8 4
PARTZ8 2
PARTX7 6
What I need is a formula / function, which compares column 1 with itself and so picks up the duplicates, then references Column 2 and shows the largest number for each row, even the duplicates, so the above table would look like the below:
Column1 Column2 Result
PARTA1 7 7
PARTA1 5 7
PARTA1 2 7
PARTB2 0 0
PARTH4 1 1
PARTZ8 4 4
PARTZ8 2 4
PARTX7 6 6
With any luck I've explained my problem clearly, if not let me know and I'll try again. I've tried various functions but cannot get anywhere. Any help would be greatly appreciated!
Many thanks,
Martin
EDIT: Forgot to say I cannot add a file as we have restrictions on data handling.
Register To Reply
Bookmarks