Dear all,
I need your help in order to identify an easy and efficient way to remove duplicates no. and substitute part number, but in the same time I must keep a row with “No” and all Substitute No.” – you can see in attached file "Substitute pn", sheet "FINAL" how the final result must be.
The final result must be a unique count no. and substitute no. till 8 were is the case. In this moment the only solution I have is VLOOKUP and erase manually thousands of lines.
Thank you in advance,
Costin
Have you looked into Excel's "Remove Duplicates" functionality?
Spread the love, add to the Rep
"None of us are as smart as all of us."
Hello mcmahobt,
Thanks for your reply. Yes, but doesn’t work in my case …I need a formula or a VBA.
Regards,
Costin
Hello mcmahobt,
Thanks for your reply. Yes, but doesn’t work in my case …I need a formula or a VBA.
Regards,
Costin
Hello,
Can anybody offer me a solution - formula or VBA for this issue ?
Thank you in advance,
Costin
This looks to be totally illogical but it works with the example as far as my tired eyes can tell.
1./ Select the data including the column headers. Click on the Data Tab, click on the Filter button.
2./ From right to left (yes, you read it correctly) sort the columns in order A-Z
3./ Enter this formula in column J and fill down:
Formula:
Please Login or Register to view this content.
4./ Enter this ARRAY formula in column K and fill down (Enter with Ctrl + Shift + Enter):
Formula:Please Login or Register to view this content.
5./ Enter this formula in column L and fill down:
Formula:Please Login or Register to view this content.
6./ Where you want the data, copy the headers then fill in the following formulae (I made a new sheet and put the headers in row 1
7./ In A2 of the new worksheet enter this ARRAY formula and fill down:
Formula:Please Login or Register to view this content.
8./ In B2 enter this ARRAY formula and fill across and down:
Formula:Please Login or Register to view this content.
Here is the resultant file.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hello newdoverman ,
This is amazing, thank you very much for your help (I know this look to be totally illogical, but this job with substitute p/n make me crazy and I’m losing a lot of time to do it manually…).
Now I don’t know how to erase and keep unique information and most complete in my file, sometimes I have Substitute No. 1, sometimes I have Substitute No. 1 to Substitute No. 4, sometimes I have Substitute No. 1 to Substitute No. 8 . You can see that the p/n can be in every column (from A to I) My file has 1000-3000 rows.
Regards,
Costin
Save your file before making changes then go through the procedure above. The enclosed file has changes to the formula on the Source and Sheet1 worksheets to accommodate a lot more data (up to 5000 rows ... fill the formulae down and across as needed) on Sheet1 and unlimited on Source. You will have to fill the formulae in columns J K and L down to the last row of data manually.
Thanks for your help and support !
Wish you all the best,
Costin
You're welcome. I just hope that it stands up with a lot of data.
If that solves your problem, please go to the TOOLS just above your first post and choose SOLVED.
I have come to the conclusion that the sorting of the data only brings like data together and makes it more obvious if all the substitute parts are being included or not in the rows with the most columns of data.
Hope this helps.
Hello,
After a few tests with my file - I don’t have duplicates on row A:I, but I still have duplicates on columns B:D.
It is very hard to eliminate automatically all duplicates when you are working with substitute p/n (in my case I have 8 p/n with substitute).
Anyway, thanks again for your help. I really appreciate your work !
Costin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks