Autosort a list removing spaces

St Jimmy

New Member
Joined
Oct 29, 2015
Messages
37
I am trying to create a dynamic column in excel that will serve as an index for a graph.
I have a list of 4000 items that have been registered over the past 10 years.
I'd like to keep the list intact, but create a new list in a column that meets criteria.
Easy so far, but I'd like there to be no gaps between criteria meeting cells and I'd like to use formulas to get it done. Not VBA.

[TABLE="width: 100"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Use[/TD]
[TD]Age[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD]A100[/TD]
[TD]Valid[/TD]
[TD]60[/TD]
[TD]A103[/TD]
[/TR]
[TR]
[TD]A101[/TD]
[TD]Valid[/TD]
[TD]45[/TD]
[TD]A104[/TD]
[/TR]
[TR]
[TD]A102[/TD]
[TD]Invalid[/TD]
[TD]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A103[/TD]
[TD]Valid[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A104[/TD]
[TD]Valid[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Based on the example above, if the Use column = Valid AND if the Age column is less than 30 the List column will populate the ID associated with the item meeting criteria. A103 and A104 in the example above.

Any thoughts?

Jimmy
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Upvote 0
Thank you Fluff!
I still can't wrap my head around how the denominator (($B$2:$B$6="Valid")*(C$2:C$6<30)) works.
Is it making an array? A list? An explanation would be greatly appreciated.

Thanks again!
 
Upvote 0
It returns an array with the data shown above

this ($B$2:$B$6="Valid") returns
TRUE;TRUE;FALSE;TRUE;TRUE

this (C$2:C$6<30) gives
FALSE;FALSE;TRUE;TRUE;TRUE

when multiplied together true gives 1 & false give 0 so you get
0;0;0;1;1

As that's the divisor you end up with
#DIV/0!;#DIV/0!;#DIV/0!,4;5

As the aggregate function is set to ignore errors you end up with
4;5

I find the easiest way of trying to understand this sort of formulae s to use "Evaluate Formula" on the formula tab

HTH
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top