Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
want to list only rows with highest value of duplicates from a column
hi all,
Forgive me if this has been posted here. I'm new and am having a huge problem searching for an answer on the net. I have a column with item numbers, and another column with letters listed where each letter is a version of that item number. For example I might have item number 1234 and versions A, B, C and D. Each version has its own row in Excel with the same item number. What I want to do is view the highest version letter for each item number. In the same sheet I would have several item numbers listed. For example I would have something like this. Item number Version 1234 A 1234 B 1234 C 6789 A 6789 B 6789 C 6789 D What I want to view is the highest version letter of each so that it would be listed in Excel as follows. Item number Version 1234 C 6789 D Can I do this using some sort of filter? If I need some sort of code to do this then can someone tell me how I can enter this? Any help would be very appreciated. Thanks, Shane |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
want to list only rows with highest value of duplicates from a column
Try a formulas set up ..
Data in cols A and B, from row2 down Put in C2: =IF(A2="","",COUNTIF($A$2:A2,A2)) Copy down Then if you have the item#s listed in say, E2 down viz in E2: 1234, in E3: 6789, etc Put in F2, array-enter (press CTRL+SHIFT+ENTER): =INDEX($B$2:$B$100,MATCH(MAX(IF($A$2:$A$100=E2,$C$ 2:$C$100)),IF($A$2:$A $100=E2,$C$2:$C$100),0)) Copy F2 down Adapt the ranges to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Feb 27, 1:46 pm, wrote: hi all, Forgive me if this has been posted here. I'm new and am having a huge problem searching for an answer on the net. I have a column with item numbers, and another column with letters listed where each letter is a version of that item number. For example I might have item number 1234 and versions A, B, C and D. Each version has its own row in Excel with the same item number. What I want to do is view the highest version letter for each item number. In the same sheet I would have several item numbers listed. For example I would have something like this. Item number Version 1234 A 1234 B 1234 C 6789 A 6789 B 6789 C 6789 D What I want to view is the highest version letter of each so that it would be listed in Excel as follows. Item number Version 1234 C 6789 D Can I do this using some sort of filter? If I need some sort of code to do this then can someone tell me how I can enter this? Any help would be very appreciated. Thanks, Shane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove all duplicates and return highest value in new row | Excel Discussion (Misc queries) | |||
list of entries from a column without duplicates | Excel Worksheet Functions | |||
Remove duplicates from list but leaving one row with highest date | Excel Programming | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
How to delete rows with repeating values and leaving only one with highest value on the next column? | Excel Programming |