Duplicate listing
 

I have a that list part numbers accross a product line I have the formula

=SUMPRODUCT((B4:O33<"")/COUNTIF(B4:O33,B4:O33&""))

counting the number of unique part numbers on this spreadsheet BUT now I
would like it to create in a NEW column, say R starting at R1 the list of
these unique part numbers. Can this be done with a formula/function that
might help with this?

Duplicate listing
 

Hi!

You'd have to convert your table into a single column of data. Then, you
could do either: use a filter or use a formula to extract the uniques. (the
filter is easier) B4:O33 = 14 columns by 30 rows = 420 cells.

Assume the table is one Sheet1.

On Sheet2 enter some header in A1.

Enter this formula in A2:

=OFFSET(Sheet1!$B$4,INT((ROWS($B$4:B4)-1)/14),MOD(ROWS($B$4:B4)-1,14))

Copy down to A421.

With the range A2:A421 still selected:
Goto EditCopy
Then EditPaste Special<ValuesOK

Now, navigate back to Sheet1 cell R1
Goto DataFilterAdvanced filter
Select Copy to another location
List range: Sheet2!$A$1:$A$421
Copy to $R$1
Select Unique records only
OK

Biff

"huntin_Xcel_answers" wrote
in message ...
I have a that list part numbers accross a product line I have the formula

=SUMPRODUCT((B4:O33<"")/COUNTIF(B4:O33,B4:O33&""))

counting the number of unique part numbers on this spreadsheet BUT now I
would like it to create in a NEW column, say R starting at R1 the list of
these unique part numbers. Can this be done with a formula/function that
might help with this?

Duplicate listing
 

Having given this more thought I do thnk that a macro will be my best bet.
Copying this to a new sheet into a single column will work BUT I have about
30 wookbooks to do this to and an average of about 40 sheets per work book.

Good answer Biff. I just need to to do this a simply as possible.

"Biff" wrote:

Hi!

You'd have to convert your table into a single column of data. Then, you
could do either: use a filter or use a formula to extract the uniques. (the
filter is easier) B4:O33 = 14 columns by 30 rows = 420 cells.

Assume the table is one Sheet1.

On Sheet2 enter some header in A1.

Enter this formula in A2:

=OFFSET(Sheet1!$B$4,INT((ROWS($B$4:B4)-1)/14),MOD(ROWS($B$4:B4)-1,14))

Copy down to A421.

With the range A2:A421 still selected:
Goto EditCopy
Then EditPaste Special<ValuesOK

Now, navigate back to Sheet1 cell R1
Goto DataFilterAdvanced filter
Select Copy to another location
List range: Sheet2!$A$1:$A$421
Copy to $R$1
Select Unique records only
OK

Biff