I have a table of data like this:
A , 1 , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3 , 3
I need to eliminate duplicate entries WITHIN a row. So, I need the table to become:
A , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3
Is there an easy way to do this for a large sample? Also, there are actually more like 8 columns of this data, if thats important. Any help you all could give me would be immensely appreciated. Thank you so much
Michael
The only way to actually erase duplicates is with a macro of some sort. Are you OK with VBA?
If so, upload a sample workbook demonstrating the exact data to deal with. Don't oversimplify the sample workbook.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Something alond the lines what JB was saying.
A macro would be the easiest way.
Maybe something like this:
All it requires you to do is to highlight what what you want to look for duplicates in and run the macro
Please Login or Register to view this content.
Hey thanks so much.
This looks really good. Except, it only works when I do one row at a time. When I select multiple rows, it doesn't keep the row structure when it puts in the new values. They end up in the wrong row.. (Value from row 3 end up in row 2 or 1, etc.) Is there a quick fix to this?
Thanks so much for all your help.
Michael
I attached a small sample. All of your help is so appreciated!
Thank you,
Michael
So, you want to evaluate each row D:M and remove duplicates within the row, that's it? Squeeze to the left as they are removed?
Thats exactly right. Is there an easy way to edit this code?
Thanks, Michael
Not really easy, but not overly hard either. Try this:
Please Login or Register to view this content.
This is really awesome.
Just a few really quick questions. Is there any way to make the range selectable? Also, what is the counter from 1 to 27? I just want to make sure I understand this code, can edit it, so I can continue to learn and get better.
Thanks so much for all of your help so far. I can't tell you how grateful I am.
Sincerely,
Michael
I hardcoded column D:M as you indicated those were the columns to evaluate.
Selectable? It's already self-adjusting. It will go down column D to the bottom of the data set, how ever far that may be, then remove the duplicates in columns D:M down to that depth.
To keep from having to evaluate each cell one at a time, I decided to use Excel's Advanced Filter. But Adv Filter only works on columns, not rows, so I copied the dataset to column AA (that's column 27) and transposed it into columns. Then I use the Adv Filter for unique values on each column (from 27 to the Last Column (LC) with transposed data in it). When I'm done I transpose the data back into the original position at D2, replacing the data that was there originally.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks