+ Reply to Thread
Results 1 to 7 of 7

function problem

  1. #1
    Registered User
    Join Date
    11-09-2005
    Posts
    3

    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. #2
    Bob Phillips
    Guest

    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
    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" <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
    > View this thread: http://www.excelforum.com/showthread...hreadid=483433
    >




  3. #3
    bplumhoff@gmail.com
    Guest

    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. #4
    Nadia
    Guest

    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.

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


  5. #5
    Ron Rosenfeld
    Guest

    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.


    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

  6. #6
    Laurent Longre
    Guest

    Re: function problem


    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

  7. #7
    Ron Rosenfeld
    Guest

    Re: function problem

    On Thu, 10 Nov 2005 15:01:04 +0100, Laurent Longre <laurent.longre@nospam.fr>
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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