Need help finding unique values and transforming an n x 1 Array
 

I need to transform a large array into a set of indicies that relate
back to actual values within each
column of the array.

For instance - suppose there was a list of 10,000 values in a column,
but there were only 20 unique values in that 10,000. I could transform
the 10,000 into the 20 if I had a way of finding them. (Or at least
that's the direction I'm headed).

This would require 1) Finding an array of unique values, 2) creating a
second array with similar dimensions (n x m) as an original array that
for each value of m goes through something like the
following:


Original array: n1 x m1 array
Unique values of n1 for each value of m1 in m1 arrays
Transformed array: n2 x m1 where n1 has been replaced by the index of
each unique n1 value within the column m1

All where n1 and m1 represent the size of the array

Need help finding unique values and transforming an n x 1 Array
 

Advanced Filter can find unique values in a range, and copy them to another
range. Would that help? I'm having a little difficulty following what you
need.

Jeff

"Marston" wrote in message
om...
I need to transform a large array into a set of indicies that relate
back to actual values within each
column of the array.

For instance - suppose there was a list of 10,000 values in a column,
but there were only 20 unique values in that 10,000. I could transform
the 10,000 into the 20 if I had a way of finding them. (Or at least
that's the direction I'm headed).

This would require 1) Finding an array of unique values, 2) creating a
second array with similar dimensions (n x m) as an original array that
for each value of m goes through something like the
following:


Original array: n1 x m1 array
Unique values of n1 for each value of m1 in m1 arrays
Transformed array: n2 x m1 where n1 has been replaced by the index of
each unique n1 value within the column m1

All where n1 and m1 represent the size of the array

Need help finding unique values and transforming an n x 1 Array
 

Besides advanced filter which could act on the source data for your array if
the source data is on a worksheet, you can see John Walkenbach's example of
using a collection to get a unique list.

http://www.j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

--
Regards,
Tom Ogilvy

"Marston" wrote in message
om...
I need to transform a large array into a set of indicies that relate
back to actual values within each
column of the array.

For instance - suppose there was a list of 10,000 values in a column,
but there were only 20 unique values in that 10,000. I could transform
the 10,000 into the 20 if I had a way of finding them. (Or at least
that's the direction I'm headed).

This would require 1) Finding an array of unique values, 2) creating a
second array with similar dimensions (n x m) as an original array that
for each value of m goes through something like the
following:


Original array: n1 x m1 array
Unique values of n1 for each value of m1 in m1 arrays
Transformed array: n2 x m1 where n1 has been replaced by the index of
each unique n1 value within the column m1

All where n1 and m1 represent the size of the array

Need help finding unique values and transforming an n x 1 Array
 

I can't do it on a worksheet - I have some 300,000 rows

"Jeff Standen" wrote in message ...
Advanced Filter can find unique values in a range, and copy them to another
range. Would that help? I'm having a little difficulty following what you
need.

Jeff

"Marston" wrote in message
om...
I need to transform a large array into a set of indicies that relate
back to actual values within each
column of the array.

For instance - suppose there was a list of 10,000 values in a column,
but there were only 20 unique values in that 10,000. I could transform
the 10,000 into the 20 if I had a way of finding them. (Or at least
that's the direction I'm headed).

This would require 1) Finding an array of unique values, 2) creating a
second array with similar dimensions (n x m) as an original array that
for each value of m goes through something like the
following:


Original array: n1 x m1 array
Unique values of n1 for each value of m1 in m1 arrays
Transformed array: n2 x m1 where n1 has been replaced by the index of
each unique n1 value within the column m1

All where n1 and m1 represent the size of the array