Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate records in Excel 2003 | Excel Discussion (Misc queries) | |||
Is there a macro to identify and remove duplicate data in Excel? | Excel Worksheet Functions | |||
Function to find duplicate values, then delete | Excel Worksheet Functions | |||
identify duplicate data in excel spreadsheet | Excel Discussion (Misc queries) | |||
Showing Duplicate Rows | Excel Discussion (Misc queries) |