Home |
Search |
Today's Posts |
#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. -- chindo ------------------------------------------------------------------------ chindo's Profile: http://www.excelforum.com/member.php...o&userid=28669 View this thread: http://www.excelforum.com/showthread...hreadid=483433 |
#2
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
function problem
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 |
#6
|
|||
|
|||
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
|
|||
|
|||
function problem
On Thu, 10 Nov 2005 15:01:04 +0100, Laurent Longre
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count function problem | Excel Worksheet Functions | |||
Lookup function problem (kg) | Excel Worksheet Functions | |||
Problem with function "Worksheet_Change" | Excel Worksheet Functions | |||
Problem adding a range using Sumif function. | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions |