Hi,
New to this, hoping somebody can advise.
I have a 37,000 row data table with stock and prices. I am trying to delete duplicate stock items (over 7,000) which show the higher price in a column. I can't see any place I can do that. Any advice or help would be greatly appreciated.
Thanks,
Craig.
depending on what the item is that indicates they are duplicates (say stock number in column A for example) you could put a formula like this somewhere to indicate which are duplicates... =COUNTIF($A$2:A2,A2) and drag down. That will give you 1s and 2s and higher for duplicates. 1s are first, 2s and higher are duplicates.
Then you could sort them based on those numbers and the duplicates would all be at the bottom (if you sort ascending) then you could highlight the whole bunch and delete them.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi Sambo,
Thanks for the reply and you're correct the duplicates are indicated in column A per stock number. However I need for example, if there is 5 rows with the same stock number, I need to keep the row with the lowest price (in separate column) and delete the 4 other rows which would have higher prices?
Thanks.
Hi, hoping for a solution,
I have now attached my file.
I need a formula or macro to identify duplicate rows in column A, then for all rows that contain the same data in column A (could be 4 or 5 lines), I want the whole row deleted based on criteria of what has the lowest value in column F.
Many thanks
This proposed solution employs several helper columns, which may be hidden/moved for aesthetic purposes. The first helper column (I) identifies duplicates using the formula:The second and third helpers will then get the 'Part' and 'GBP' for the duplicates using formulas similar to:Formula:
Please Login or Register to view this content.The fourth helper identifies 'singles' and lowest price duplicates using the formula:Formula:Please Login or Register to view this content.A table of singles and lowest cost duplicates is then created in columns M:T using the array entered formula*:Formula:Please Login or Register to view this content.*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.Formula:Please Login or Register to view this content.
Note: the calculations needed to create the table will, or at least on my laptop did, take quiet a long time.
Note: I had to greatly reduce the size of the attached file so that it would upload, hopefully there is enough left to see how the formulas work.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
In column I
=COUNTIF($A$2:$A$13000,A2)
Copy down
in column J
=IF(A2=A1,IF(MIN(OFFSET($F$2,MATCH(A2,$A$2:$A$13000,0)-1,0,COUNTIF($A$2:$A$13000,$A2)))=F2,F2,""),"")
Copy down
Filter on Column I > 1 and column J will have minimum value for the duplicates.
NOTE: this may take a few seconds to complete but it did the 12500 rows in your sample in about 5 seconds.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks