Hi Guys,
I have a database with a ton of duplicate entries where each column is the exact same data in multiple rows except for the price column. I would like to figure out a quick way to delete duplicates keeping the row with the lowest price. I can sort to make it simpler. i have attached a spreadsheet with a sample of data.
Thanks
Also, does anyone know a good advanced excel book to learn macros and VBA?
Last edited by timmy1254; 11-04-2011 at 03:40 PM.
timmy1254,
Give the below code a try. As for books to learn, afraid I can't help you there
Sub tgr() Const BookCol As String = "C" Const PricCol As String = "H" Dim rngChk As Range With ActiveSheet.UsedRange Set rngChk = Cells(.Row, .Column + .Columns.Count).Resize(.Rows.Count) End With With rngChk Cells(.Row, .Column).FormulaArray = "=IF(" & PricCol & "1=MIN(IF(" & Range(BookCol & rngChk.Row).Resize(.Rows.Count).Address & "=" & BookCol & "1," & Range(PricCol & rngChk.Row).Resize(.Rows.Count).Address & ",9^9)),1,0)" Cells(.Row, .Column).AutoFill rngChk .AutoFilter 1, 0 .Offset(1).EntireRow.Delete .EntireColumn.Delete End With End Sub
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Very nice solution tigeravatar.
Thank you
That worked perfect! Thanks alot! Could you tell me what part of the code I would alter if I add or subtract columns from the sheet? Not the price column, of course, but I may need to delete a column or add a column in the future and it'd be nice if I could alter the code myself.
timmy1254,
The code already self-adjusts. All you need to do is make sure the BookCol and PricCol are set to the correct column letters
Actually I noticed a problem. You wrote it so that it would delete duplicates based off the title column. I need it to delete duplicates based off the first Item ID column. There are some titles that have different Item ID's.
Nevermind I figured it out. Very easy code thanks alot!
Found this thread and is exactly what I am needing, but I need to see how to modify this in order to include one additional column.
I have attached my example on this and are not really sure what I need to modify in the code to get this to work. I added a new Const called FeeTab which is my Column A, but not sure how to include it in the FormulaArray.
Breakdown:
There are four columns
A - FeeSched: This is the insurance company name
B - Code: This is a list of CPT/HCPC codes for each insurance company listed in Column A. There can be/probably will be duplicates each time.
C - APC: This column may or may not have values; however, I do not believe this should be factored in.
D - Charge: This is the charge.
Goal:
The goal I am trying to accomplish is the code should check Columns B and D, and remove the "lowest" value if a duplicate "Code" (Column B) is found; however, the catch is it should take into consideration Column A.
In the attached, There is a duplicate code for Aetna (10022) that has a charge of 100 and 105. The expected result would be to remove the code for Aetna that has the charge of 100 and leave all of the others.
Hopefully my case makes sense, and appreciate any help
If any clarifications are needed, let me know.
Thank you,Excel Example.xlsm
How can you adjust the code to keep only the highest value?
matrix, welcome to the forum
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks