# function problem

1. ## 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.

2. ## Re: function problem

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

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" <chindo.1y85ao_1131531026.6089@excelforum-nospam.com> wrote in
message news:chindo.1y85ao_1131531026.6089@excelforum-nospam.com...
>
> 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
>

3. ## Re: function problem

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

4. ## RE: function problem

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.

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

5. ## Re: function problem

On Wed, 9 Nov 2005 04:09:15 -0600, chindo
<chindo.1y85ao_1131531026.6089@excelforum-nospam.com> 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.

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

6. ## Re: function problem

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

7. ## Re: function problem

On Thu, 10 Nov 2005 15:01:04 +0100, Laurent Longre <laurent.longre@nospam.fr>
wrote:

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1