| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract 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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract single occurence of order number from list
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 On Sep 17, 2:14 pm, JBW wrote: 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract single occurence of order number from list
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. "Pete_UK" wrote: 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 On Sep 17, 2:14 pm, JBW wrote: 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract single occurence of order number from list
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 On Sep 17, 4:36 pm, JBW wrote: 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. "Pete_UK" wrote: 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 On Sep 17, 2:14 pm, JBW wrote: 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?- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract single occurence of order number from list
wonderful, that's cracked it.
thankyou very much "Pete_UK" wrote: 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 On Sep 17, 4:36 pm, JBW wrote: 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. "Pete_UK" wrote: 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 On Sep 17, 2:14 pm, JBW wrote: 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?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract single occurence of order number from list
You're welcome - thanks for feeding back.
Pete On Sep 17, 5:10 pm, JBW wrote: wonderful, that's cracked it. thankyou very much "Pete_UK" wrote: 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 On Sep 17, 4:36 pm, JBW wrote: 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. "Pete_UK" wrote: 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 On Sep 17, 2:14 pm, JBW wrote: 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?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I create and extract a random order from list in Excel? | Excel Discussion (Misc queries) | |||
| Assign a number each letter on list compounding occurence @ each e | Excel Worksheet Functions | |||
| List all contracts that relate to a single part number | Excel Discussion (Misc queries) | |||
| Build a single order sheet from a multiple page material list. | Excel Discussion (Misc queries) | |||
| how to count occurence of numbers separated by , in a single cell | Excel Worksheet Functions | |||
Linear Mode
