How to create a parts number list

Hi,

I'm sure I'm at the correct place... I have 2 worksheets, on w/s 1 I enter part numbers in the same column different rows and in any given period, the same part number might be entered 1-50 times. On w/s 2 I want a vertical list that searches the part number column in w/s 1 and lists the part numbers from w/s 1 in any order BUT only includes 1 copy. So if for the month parts number 123 is entered 20 times in w/s 1, on w/s 2 the list includes it only once. If part number 123 is not entered for that month then it does not appear in the list on w/s 2.

Any ideas?

Thanks in advance.

Ranger.RSA

Hi,

a pivot table is the most convenient solution.

If you'd need a formula,

=IF(ISERROR(INDEX(Sheet1!$A$2:$A$1000,SMALL(IF(ISNUMBER(MATCH(ROW($A$2:$A$1000)-1,MATCH(Sheet1!$A$2:$A$1000,Sheet1!$A$2:$A$1000,0),0)),MATCH(Sheet1!$A$2:$A$1000,Sheet1!$A$2:$A$1000,0),""), ROW(A1)))),"",INDEX(Sheet1!$A$2:$A$1000,SMALL(IF(ISNUMBER(MATCH(ROW($A$2:$A$1000)-1,MATCH(Sheet1!$A$2:$A$1000,Sheet1!$A$2:$A$1000,0),0)),MATCH(Sheet1!$A$2:$A$1000,Sheet1!$A$2:$A$1000,0),""), ROW(A1))))

This formula must be confirmed with control+shift+enter: it extracts not in alphabetical order a unique list from sheet1-A1:A1000.

Please see the attached file.

Regards

Hoop dit help