Formula help needed

[gnrboyd]

Hello,

I am looking for some help with a formula. In one column, I have a set of data that could be as long as 200 rows. In the next column, a user will key in an "X" in the corresponding cells for those items they wish to select. Most of the time, the users will be selecting less than 10 items but it could be as many as 25. I need a cell with an output of those selections sorted numerically first and then alphanumerically at the end. The data options will not be sorted in any particular way so the sort needs to happen within the formula. (It isn't feasible to sort the data list by the way.)

I think the SMALL formula could be used for the numeric values but I am not certain how to get the text entries added into the list at the end.

Although my project is more complex, here is a small example to illustrate what I am looking for. By the way, I prefer to do this without using an array formula or VBA if possible.

Can this be done? I would appreciate any help you can provide. (By the way, I am using Excel 2000)


Thank you.

PS: I posted this a couple of weeks ago but didn't get any response so I am trying again.

Excel example.xls
	A	B	C	D	E
1	Option	Select?		Output:	1,4,6,8,10,C,D
2	1	X
3	2
4	3
5	5
6	6	X
7	4	X
8	7
9	D	X
10	8	X
11	C	X
12	9
13	10	X
14	M
15	11
Sheet1

 

[gnrboyd]

Disregard.... I found something that will work using a combination of forumulas. (Sumproduct, Index, Match, Row and Small)

Thanks

 

[iliace]

You've pretty much eliminated all possible solutions in your inquiry (I'm guessing an add-in such as Morefunc is also out of the question), so, barring formula length limitations (32,767 characters in Excel 2000)...

=IF(B1="X", A1 & ",", "") & IF(B2="X", A2 & ",", "") & ... & IF(B200="X", B200, "")

...except arrange them in the desired sort order. You could use a macro to generate this formula, in a separate workbook, so that there's no macro in this master workbook.

Sub MakeLongFormula()
  Dim myFormula As String
  Dim i As Long
  
  myFormula = "="
  
  For i = 1 To 200
    myFormula = myFormula & "IF(B" & i & "=""X"", A" & i & "&"","", """") & "
  Next i

  myFormula = Left$(myFormula, Len(myFormula) - 3)
  
  Debug.Print myFormula
End Sub

You would copy the result from VBA's Debug window, and then you'll also have to go through and eliminate extra line breaks that will be added in the process. I did it for the first 10 rows, for sake of shortness:

Excel Workbook
	A	B	C	D
1	1	X		1,3,4,D,9,
2	2
3	3	X
4	4	X
5	5
6	6
7	D	X
8	8
9	9	X
10	L
Sheet

 

[gnrboyd]

iliace... Thanks for your help on this but your solution doesn't put the numbers first and the alpha characters last. Besides, that formula would be pretty long.

I did find a solution using a combination of the following: Sumproduct, Index, Match, Row and Small. It seems to be working.

Thanks again.