Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create List of Unique Values from several columns
I need to create a list of unique values from a range of columns. For
example, say Columns E - G are as follows: E F G Bob Dave Bill Rick Steve Dave Steve Rick Fred Bill Joe Steve I would end up with one column with the unique names: Bob Rick Steve Bill Dave Joe Fred If it is possible, I would like to have the Unique list in a separate sheet, update automatically and not use a Macro (VBA). Is this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create List of Unique Values from several columns
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create List of Unique Values from several columns
Here's an alternative, a formulas play which can deliver the automatic goods
as specified .. Assume your source data in Sheet1, cols E to G, data in row1 down In another sheet, In A1: =OFFSET(Sheet1!$E$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) Copy A1 down to strip the 3-col source data in Sheet1 into a vertical col. Copy down as far as required to cater for the max expected extent of source data in Sheet1. If you expect source data to extend down to row 100 in Sheet1, copy down by 3 x 100 = 300 rows to A300 to cover the extent. Then In B1: =IF(A1=0,"",IF(COUNTIF(A$1:A1,A1)1,"",ROW())) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to the same extent, ie to C300. Hide/minimize cols A and B. Col C will return the required list of uniques which is dynamic to the source data in Sheet1. Success? Celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Matt" wrote: I need to create a list of unique values from a range of columns. For example, say Columns E - G are as follows: E F G Bob Dave Bill Rick Steve Dave Steve Rick Fred Bill Joe Steve I would end up with one column with the unique names: Bob Rick Steve Bill Dave Joe Fred If it is possible, I would like to have the Unique list in a separate sheet, update automatically and not use a Macro (VBA). Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a list of unique values from 2 columns of data | Excel Worksheet Functions | |||
create a list of unique values | Excel Worksheet Functions | |||
List unique Values from different columns: How to... | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |