 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007Extract single occurence of order number from list
I have a 5000+ number list within a spreadsheet made up of 6 digit order numbers, if an order numbers 'order' contained multiple items then the number appears x times in the list, once per item. I want to pivot the data, but need to extract a list of order numbers with their relevant data from the other columns, at the moment the only way (to me) seems to be to sort by order number and manually delete the duplicate occurences. example of data below, actual is 20+ columns wide 246335 14/06/2007 F170656 246341 14/06/2007 F170925 246526 15/06/2007 F170770 246551 15/06/2007 F170785 246620 18/06/2007 F170993 246627 18/06/2007 F171022 246628 18/06/2007 F171023 246637 18/06/2007 F170699 246689 18/06/2007 F170785 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 246690 18/06/2007 F171105 ideas? You can copy your order numbers (with heading) to another sheet. Then highlight the data and heading and click on Data | Filter | Advanced filter and in the pop-up click on Unique records only and also Filter to another location - specify C1. Click OK and you will have your unique list in column C - you can delete columns A and B if you want to. Hope this helps. Pete > I have a 5000+ number list within a spreadsheet made up of 6 digit order > numbers, if an order numbers 'order' contained multiple items then the number [quoted text clipped - 30 lines] > > ideas? Ok, If I copy that one column that works beautifully, however. If I highlight the column within the 5000row 21 column sheet and try filter in place it changes nothing, I'm guessing this is because its looking across at the other columns (which will all be different) and as they're all therefore unique it's not removing any of them. I need it to get rid of all duplicate entries of the order number in column A regardless of the data in the other columns, and yes I need the data in the other columns on the row that's left to remain intact. > You can copy your order numbers (with heading) to another sheet. Then > highlight the data and heading and click on Data | Filter | Advanced [quoted text clipped - 41 lines] > > > > ideas? But if the data in your other columns is all different, thus making each row unique, which records do you want to get rid of in order to have the unique values of order number? If it doesn't matter what is in the 20-odd other columns, then you could use VLOOKUP in the second sheet (i.e. the one with the unique order numbers in), and this will bring the first matching record from the first sheet. Something like this in B2: =VLOOKUP(A2,Sheet1!$A$1:$U$5000,COLUMN(B1),0) Copy across to U2, then copy B2:U2 down to the bottom of your unique list of order numbers. This assumes that your order numbers are in column A of the first sheet - if not then you will have to use an INDEX/MATCH combination. You can then fix the values in the second sheet (Edit|Paste Special) and then delete the first sheet. Hope this helps. Pete > Ok, If I copy that one column that works beautifully, however. If I highlight > the column within the 5000row 21 column sheet and try filter in place it [quoted text clipped - 53 lines] > > - Show quoted text - wonderful, that's cracked it. thankyou very much > But if the data in your other columns is all different, thus making > each row unique, which records do you want to get rid of in order to [quoted text clipped - 75 lines] > > > > - Show quoted text - You're welcome - thanks for feeding back. Pete > wonderful, that's cracked it. > [quoted text clipped - 81 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.