Duplicate listing

  • Thread starter huntin_Xcel_answers
  • Start date
H

huntin_Xcel_answers

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?
 
B

Biff

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 Edit>Copy
Then Edit>Paste Special<Values>OK

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

Biff
 
H

huntin_Xcel_answers

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.
 
Top