function problem

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.

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iRow As Long
Dim iRow2 As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iRow = 2
Range("B1").Value = GetLetter(Range("A1").Value)
Range("C1").Value = GetNumber(Range("A1").Value)
For i = 2 To iLastRow
iRow2 = 0
On Error Resume Next
iRow2 = Application.Match(GetLetter(Cells(i, "A").Value), _
Range("B:B"), 0)
On Error GoTo 0
If iRow2 > 0 Then
Cells(iRow2, "C").Value = Cells(iRow2, "C").Value & "," & _
GetNumber(Cells(i, "A").Value)
Else
Cells(iRow, "B").Value = GetLetter(Cells(i, "A").Value)
Cells(iRow, "C").Value = GetNumber(Cells(i, "A").Value)
iRow = iRow + 1
End If
Next i
Columns("B:C").Sort key1:=Range("B1"), header:=xlNo

End Sub

Private Function GetLetter(cell As String)
Dim iPos As Long
iPos = InStr(cell, " ")
GetLetter = Right(cell, Len(cell) - iPos)
End Function


Private Function GetNumber(cell As String)
Dim iPos As Long
iPos = InStr(cell, " ")
GetNumber = Left(cell, iPos - 1)
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"chindo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Hello,

I suggest to put your numbers on the right side of your characters
(with a helper column, for example) and to use my UDF vlookupall() from
http://www.sulprobil.com/html/vlookupall.html

Then use something like
=vlookupall("a",B1:C9,2)
for example

HTH,
Bernd

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
>
>

On Wed, 9 Nov 2005 04:09:15 -0600, chindo
<[email protected]> 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

Ron Rosenfeld a écrit :
> 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:

It's fixed now (=> version 4.01). Many thanks for having detected and reported
this bug, Ron.

Laurent

On Thu, 10 Nov 2005 15:01:04 +0100, Laurent Longre <[email protected]>
wrote:

>
>Ron Rosenfeld a écrit :
>> 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:
>
>It's fixed now (=> version 4.01). Many thanks for having detected and reported
>this bug, Ron.
>
>Laurent

Great! Thank you for developing and supporting this most useful add-in.




--ron