LinkBack URL
About LinkBacks
Some columns in my spreadsheet have duplicate data. The data in that row, however, isn't duplicate so I can't remove it by simply using the Remove Duplicates function. Is there a way to remove row data when it finds a duplicate column?
Remove Duplicates in Columns but also delete Row
Some columns in my spreadsheet have duplicate data. The data in that row, however, isn't duplicate so I can't remove it by simply using the Remove Duplicates function. Is there a way to remove row data when it finds a duplicate column?
You will need to explain with the help of a sample file.
To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I want to essentially combine all worksheets but remove all duplicates. Is it even possible?sampleFile.xlsx
All 3 sheets in your file are not consistent with regard to the columns. How do you want to consolidate them?
When you say you have duplicate data in the columns, can you explain with the help of your file
I might be asking for the impossible but I'd like to combine all the worksheets into one (so that I have one list) and remove the duplicates but keep the additional information that some of the link sources give.
So since the columns are not consistent in all the sheets, should the data for the respective columns be copied below each other and then duplicates removed in all columns?
I think that would suffice.
So how would i identify a duplicate in a column? By duplicates, do you mean repetitive words?
Yes by duplicate I mean the same URL.
So only the URL column needs to be checked for duplicates after consolidation, right?
Yes. I think that would work.
Oops i missed out one question - The first sheet has 5 columns, the 2nd has 7 and the 3rd has 9 so how many columns do you need in the final output?
I think that since all 9 could have pertinent info I need 9.
The header location will remain the same in the 1st & 2nd sheet, right?
For e.g in sheet2, the target URL is the 2nd column. So will it always remain as the 2nd column?
That isn't the same column as the other sheets but it's okay if you move it to be so.
I have manually consolidated the 3 sheets and sorted them by URL and the attached is the outcome. How do you want to proceed? Directly delete all duplicate rows?
Yes, that'd be great. Thank you.
If you check the sheet i attached, there are some rows with more information than the others. Do you want all the information to be consolidated into 1 row and then delete the duplicates or just delete the duplicate rows without consolidation?
See if this is what you are after.
Please Login or Register to view this content.
Last edited by jindon; 08-19-2012 at 07:23 AM.
Consolidating the info into one row then deleting the duplicates would be the best case scenario.
---------- Post added at 09:23 AM ---------- Previous post was at 09:21 AM ----------
Yes, I think that's perfect. When I run it against my actual data, though, I get a [Run-time error '9': Subscript out of range]. It's a rather large file. Would that have anything to do with it?Originally Posted by jindon
Try replace "test" sub with this
Please Login or Register to view this content.
Last edited by jindon; 08-20-2012 at 09:36 AM.
What I get now is Compile error: Sub or Function not defined.
Did you delete IsSheetExists function, too?
It shoud stay intact, so replace the "test" sub procedure only with the one I have last posted.
Should look like this.
Please Login or Register to view this content.
Last edited by jindon; 08-20-2012 at 10:09 AM.
Ok, I repasted and it does look like that. But now says Run-time error '13': Type mismatch. Sorry for the trouble, not used to working with macros.
When it stops, click on "Debug" and see which line was highlited.
By the way, do you have any Error value in the cell?
It has highlighted b = Application.Transpose(b)
OK
Try this one
Edited: CodePlease Login or Register to view this content.
Last edited by jindon; 08-20-2012 at 11:03 AM.
If it still raise the error then try this one. Might be much slower though...
Please Login or Register to view this content.
Neither worked. The last bit of code gave an error of Sub or Function not defined.
IsSheetExists function must not be deleted in any case.
The last code should work.
I am going to start over and try again.......
---------- Post added at 03:23 PM ---------- Previous post was at 03:20 PM ----------
What does Ambiguous name detected: test mean?
You can not have multiple procedures that have same procedure name within a module.
So, you need to delete one of them.
Last edited by jindon; 08-20-2012 at 04:37 PM.
SCORE!!!!! It worked. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks