Automatic resorting list with out VBA?

[St Jimmy]

Hi guys,

I am trying to automatically resort a list of items based on some data result elsewhere in the book and I would like to avoid using VBA or a macro.
For instance:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Well[/TD]
[TD]Data[/TD]
[TD]Result[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD]A01
[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]A02[/TD]
[/TR]
[TR]
[TD]A02[/TD]
[TD]1[/TD]
[TD]A02[/TD]
[TD]B01[/TD]
[/TR]
[TR]
[TD]A03[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]B04[/TD]
[/TR]
[TR]
[TD]A04[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B01[/TD]
[TD]1[/TD]
[TD]B01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B02[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B03[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B04[/TD]
[TD]1[/TD]
[TD]B04[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Well is a string identifier
Data is determined elsewhere in the book, its a boolean type
Result is simply detecting whether or not the Data is true and referencing the well
List is the part Im stuck on, I have 2400 identifiers but only 20 or 40 hits (data with 1 value). I want the list to ignore the results that are "-" and line up the results if they have a value into a compact list in the result column. I am making a template that will be shared by a group of people so I would like to avoid macros and VBA if possible (else this would be a piece of cake).

Thank you for your time and help! All thoughts are greatly appreciated

Kyle

ps I have an attachment in excel but the board wont let me post it?

 

[St Jimmy]

Any ideas? Is this even possible?

 

[FormR]

Hi, this assumes you have XL2010 or higher. It uses the "data" column versus the "result" column to determine which rows should be returned. The formula in F1 is required to aid performance. The formula in H2 can be copied down as far as might be required.

Excel 2012
	A	B	C	D	E	F	G	H
1	Well	Data	Result		Count:	3		List
2	A01	0	-					A02
3	A02	1	A02					B01
4	A03	0	-					B04
5	A04	0	-
6	B01	1	B01
7	B02	0	-
8	B03	0	-
9	B04	1	B04
Sheet1

Cell Formulas
Range	Formula
F1	=COUNTIF(B:B,1)
H2	=IF(ROWS($H$2:H2)>$F$1,"",INDEX($A$2:$A$2400,AGGREGATE(15,6,((ROW($A$2:$A$2400)-ROW($A$2)+1)/($B$2:$B$2400=1)),ROWS($H$2:$H2))))

 

[St Jimmy]

WOW! Thank you so much! Works beautifully, the only thing more impressive than the formula is that it took you less than 15 minutes to generate!
Very much obliged

 

[FormR]

Glad it helped, welcome to the forum