If possible, I prefer to use worksheet functions rather than VBA code,
because there is virtually no other Excel user with VBA expertise in my
company, whereas most of them can understand and modify a formula, once they
see how it works. For your problem I use a matrix function (I entered the
empty spaces and the line breaks to make its components easier to understand)
{=INDEX(numbers,
SMALL( WENN(letters=E$6, ROW(INDIRECT("1:"&ROWS(numbers)))),
ROW(INDIRECT("1:"&ROWS(numbers)))))}
E$6 = cell that contains letter "a"
numbers = named cell range, contains your numbers 1 to 9
letters = named cell range, adjacent column with letters a, b or c
output:
1) write the letters a, b and c in a row, as column headings; cell E6 = "a",
F6 = "b", G6 = "c"
2) below the heading "a", enter the matrix formula, from E7 to E15
3) copy the formula from E7:E15 to the columns F and G
You'll see these results in E6:G15 :
a b c
1 3 2
9 5 4
#ZAHL! 6 8
#ZAHL! 7 #ZAHL!
#ZAHL! #ZAHL! #ZAHL!
#ZAHL! #ZAHL! #ZAHL!
#ZAHL! #ZAHL! #ZAHL!
#ZAHL! #ZAHL! #ZAHL!
#ZAHL! #ZAHL! #ZAHL!
#ZAHL! is an error in my German Excel version. In an English Excel you will
see something like #NUMBER!.
In a second step we skip those errors and convert the columns into rows:
1) Enter a,b and c as your row headings in cells D19:D21
2) Enter the following matrix formula into E19:M21
{=TRANSPOSE( IF(ISERROR(E7:G15), "", E7:G15))}
3) Then you'll see the final result in D19:M21
a 1 9
b 3 5 6 7
c 2 4 8
Looks more complicated than it is. Seeing the Excel sheet would be much less
cumbersome than this lengthy description. I believe that 2 formulas, even
complex ones like those shown above, are preferable to VBA code.
Nadia
"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.
--
chindo
------------------------------------------------------------------------
chindo's Profile: http://www.excelforum.com/member.php...o&userid=28669
View this thread: http://www.excelforum.com/showthread...hreadid=483433