Ordering data

  • Thread starter Thread starter Schizoid Man
  • Start date Start date
S

Schizoid Man

I have the following data:
4150 4321 4525 <blank cell> 5050 <blank cell> 5575 <blank cell> 5650

The second cell - in this case the value 4321 - is linked to a live market
feed. I need to sort this data so that the live feed cells always falls into
the correct 'band', ie. the blank cell between 4150 and 4525, between 5575
and 5650, etc.

So for example if the market feed value suddenly becomes 5321, the cells
will be displayed as:
4150 <blank cell> 4525 <blank cell> 5050 5321 5575 <blank cell> 5650

Thanks,
Schiz
 
Hi,

This problem lies in the blank cells. These will never sort correctly
onless you put something in them. That's easy enough. For example between
4123 and 5102 you can add a new entry in place of the blank 4999.999. Now
when you sort the bands will say in their proper location.

Now select the cell where the 4999.999 is located and create the following
custom format: Choose Format, Cells, Number tab, Custom and on the Type line
enter ;;;
This is the code to hide you entry from view. But the sort will still work.
 
Hi,

This problem lies in the blank cells. These will never sort correctly
onless you put something in them. That's easy enough. For example between
4123 and 5102 you can add a new entry in place of the blank 4999.999. Now
when you sort the bands will say in their proper location.

Now select the cell where the 4999.999 is located and create the following
custom format: Choose Format, Cells, Number tab, Custom and on the Type
line
enter ;;;
This is the code to hide you entry from view. But the sort will still
work.
Click to expand...

Hi,

Thanks for the answer, but I don't quite understand your solution. The
market feed ticks approximately every 30 seconds. So the way I imagine this
would work would be for the market feed cell to automatically get inserted
into the correct band.

I initially attempted solving this problem by duplication. In other words,
the market feed cell was fixed always in the same cell, and I tried to order
the market feed and the bands using the SMALL() function.

However the problem with this solution is that the blank cells need to be
eliminated.

I could embed a nested IF statement, but that solution strikes me as a bit
inelegant.

Thanks for your help.
 
Are you willing to use VBA code?


Hi,

Thanks for the answer, but I don't quite understand your solution. The
market feed ticks approximately every 30 seconds. So the way I imagine this
would work would be for the market feed cell to automatically get inserted
into the correct band.

I initially attempted solving this problem by duplication. In other words,
the market feed cell was fixed always in the same cell, and I tried to order
the market feed and the bands using the SMALL() function.

However the problem with this solution is that the blank cells need to be
eliminated.

I could embed a nested IF statement, but that solution strikes me as a bit
inelegant.

Thanks for your help.
Click to expand...
 
Hi,

I'm slowly getting a clearer picture. Well the 30sec feed is not going to
automatically go into the correct position unless you use VBA, however, let's
revisit the solution you once tried - using the SMALL function.

Suppose the range where the data comes in is A1:A100 with spaces to band the
data. The feed is probably give data to 2 decimal places? If so we go back
to my original suggestion - put a number such as 4999.999 as the demarkation
of the band. You data would look something like this:

4150
4321
4525
4999.999
5050
5499.999
5575
5599.999
5650

Now to your idea, duplicat the data somewhere else -
In another area, say E1 in this example, enter the formula

=IF(ROUND(MOD(SMALL(A$1:A$100,ROW(A1)),1),3)=0.999,"",SMALL(A$1:A$100,ROW(A1)))

And fill this down. It will put things in the correct order and leave the
bands blank. You should be able to modify this to suit your needs.

If this helps, please click the Yes button.
 
ShaneDevenshire said:
Hi,

I'm slowly getting a clearer picture. Well the 30sec feed is not going to
automatically go into the correct position unless you use VBA, however,
let's
revisit the solution you once tried - using the SMALL function.

Suppose the range where the data comes in is A1:A100 with spaces to band
the
data. The feed is probably give data to 2 decimal places? If so we go
back
to my original suggestion - put a number such as 4999.999 as the
demarkation
of the band. You data would look something like this:

4150
4321
4525
4999.999
5050
5499.999
5575
5599.999
5650

Now to your idea, duplicat the data somewhere else -
In another area, say E1 in this example, enter the formula

=IF(ROUND(MOD(SMALL(A$1:A$100,ROW(A1)),1),3)=0.999,"",SMALL(A$1:A$100,ROW(A1)))

And fill this down. It will put things in the correct order and leave the
bands blank. You should be able to modify this to suit your needs.

If this helps, please click the Yes button.
Click to expand...

Thank you very much for the suggestion, Shane (and Dave). I would definitely
be open to trying a VBA solution if it will work as I originally imagined.
In the meantime I'll give this a shot.

Thanks again.
 
ShaneDevenshire said:
Hi,

I'm slowly getting a clearer picture. Well the 30sec feed is not going to
automatically go into the correct position unless you use VBA, however,
let's
revisit the solution you once tried - using the SMALL function.

Suppose the range where the data comes in is A1:A100 with spaces to band
the
data. The feed is probably give data to 2 decimal places? If so we go
back
to my original suggestion - put a number such as 4999.999 as the
demarkation
of the band. You data would look something like this:

4150
4321
4525
4999.999
5050
5499.999
5575
5599.999
5650

Now to your idea, duplicat the data somewhere else -
In another area, say E1 in this example, enter the formula

=IF(ROUND(MOD(SMALL(A$1:A$100,ROW(A1)),1),3)=0.999,"",SMALL(A$1:A$100,ROW(A1)))

And fill this down. It will put things in the correct order and leave the
bands blank. You should be able to modify this to suit your needs.

If this helps, please click the Yes button.
Click to expand...

Hi,

I think this solution works quite well, thought I was wondering if you could
point me towards a VBA based solution.

Thanks.
 
First place the feed updating a single cell and create a named range for the
cell feed, call it say "Feed" and have the feed update this cell. It can be
anywhere on the worksheet and can be hidden if required. Naming it makes it
easier to refer to in code, especially if its location changes when you insert a
row of column.

Next Names the range of cells that contain the price bands. Select all the cells
(I assume it is in a single column with no gaps between the rows, e.g. cells A3
to A8)

Make sure the range PriceBands is sorted into as sending order.

Paste the following code into the "Sheet1" code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect As Range
Dim MyCell As Range

'Action for within the range "Feed"

'When the price value changes delet the old Range "InsertedFeed"
'The locate the now location in the range "PriceBand" and insert the updated
feed. Name it "insertedFeed"
Set isect = Application.Intersect(Target, Range("Feed"))
If Not (isect Is Nothing) Then
'MsgBox "feed changed"
On Error Resume Next
Range("InsertedFeed").Delete
On Error GoTo 0

For Each MyCell In Range("PriceBands")
'MsgBox MyCell.Value
'Find first value which is greater than Feed and insert the Feed value
before it
If Range("Feed").Value < MyCell.Value Then
'MsgBox "Insert before" & MyCell.Value
MyCell.EntireRow.Select
Selection.Insert Shift:=xlDown 'Insert a blank row
before the target/current cell
ActiveWorkbook.Names.Add Name:="InsertedFeed", RefersToR1C1:="=Sheet1!R"
& Selection.Row
With Cells(Range("InsertedFeed").Row, Range("PriceBands").Column)
.Value = Range("Feed").Value
.Interior.ColorIndex = 3
.Select
End With
Exit Sub
End If
Next

End If

End Sub
 
Hi,

I'm glad I could help. Regarding the VBA solution, Dave is giving you some
ideas. I won't be much help

Cheers,
Shane Devenshire
 
Back
Top