On Wed, 9 Nov 2005 04:09:15 -0600, chindo
wrote:


I have a column with number 1-9 in order (skus), and a second column
with either the letters a, b, or c as follows:

1 a
2 c
3 b
4 c
5 b
6 b
7 b
8 c
9 a

I need a formula or program that would analyze these two columns and
return 2 columns in this format: (first column is either a,b, or c and
the second column is a list of the skus associated with the letter as
follows)

a 1,9
b 3,5,6,7
c 2,4,8

Any help would be greatly appreciated, i am new to excel programming
and have been trying to figure this out for hours.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

BUT MAKE SURE IT HAS BEEN UPDATED TO A VERSION LATER THAN 4.0 (AND LATER THAN 3
NOVEMBER 2005) as there is a bug in 4.0 which will cause it to fail with these
formulas:

Then:

F1: a
F2: b
F3: c

G1: =REGEX.SUBSTITUTE(MCONCAT(((ltrs)=E1)*sku,", "),"0,\s|,\s0")

entered as an *array* formula. In other words, after copying/pasting the
formula, hold down <ctrl<shift while hitting <enter. Excel will place
braces {...} around the formula.

ltrs is the named range where your abc is located (e.g: B1:B9) and sku is the
range where your numbers are (e.g: A1:A9)

If you don't want to have named ranges, then merely substitute absolute
references for them in the above formula (sku: $A$1:$A$9) ltrs:$B$1:$B$9)

copy/drag down to G3.

-------------------------------


--ron