Get rid of repeating data

[danielalmaty]

Hi,

My issue is as follows:

I have a table of data in which I have several columns. In one of them there is an in formation about percentage price change from the beggining of the day until this moment in the cell. In the other Column there is an information if we shoul or shouldnt sell the item- 'Sell' or 0. The item should be sold just once a day but now it's sold every half an hour when it reaches particular price. This action shouln't Be repeated during the day, so after first time when the formula is active it should stop until the next day.


If the difference between Open of the day and High of the half an hour is more than half a percent it sells. But it shoul do it only once a day as i already told.

Sorry if I couldn't quite explain what a problem is, but I could upload the file so far as it weights about 25 mb.

[Pichingualas]

Could you upload just a sample instead of the whole file, with dummy data?

[danielalmaty]

Here.
For example cells K25 and K26. And I have the whole year 2010 of the half an hour data.

The sell order goes when rize in the open price of the day until high price of the half an hour is more thatn half a percent.

Tank you!!Example for forum.xls

[danielalmaty]

Guys, I urgently need help. If anyone of you will be able to help me, it would be perfect!!

[arlu1201]

Could you explain the issue you are facing? I understood how SELL n 0 are calculated.

[danielalmaty]

So, the issue is that "Sell" action should happen just once a day. But it happens every half an hour if the difference between open price of the day and highest price of this half an hour is more that half a percent. Sell action shold be activated just the very first time and then be inactive until the next day begins.

[arlu1201]

So what should be displayed if another "Sell" occurs?

[danielalmaty]

Another Sell just shouldn't occur this day. It should be 0 in all the following half hour periods until the next day starts and everything starts from the beggining.

[arlu1201]

Try running this code.

Please Login or Register  to view this content.

[danielalmaty]

Sorry, I didn't quite get where should I write in this code.

[arlu1201]

On your excel file, press Alt+F11, it will open up a code window. On the left hand side, right click on microsoft excel objects. Select Insert->Module. Copy the code over. Go back to your excel file. Go to Tools->Macro->Macros and run the macro given.

[danielalmaty]

Thank you it worked out in the demo file I've sent, but the file I need, which contains 16000 lines of information just stuck with this code. I tried to wait 15 minutes but nothing changes. I couldn't upload the original file here because it is 25 mb.

[arlu1201]

Is your original file and the demo file the same in layout and columns?

[danielalmaty]

No, it's much bigger and there are much more columns and other details.

[arlu1201]

In that case, your demo file should have reflected all the columns. Can you tell me which is your last column and which column has the "SELL" data in it.

It will be good if you post another sample with the same columns as your original file.

[danielalmaty]

Sell column is placed in K. And Last column is Y. But I also thre other worksheets in the same excel file, whhich are interconnected, if it makes difference.

I decided to upload the whole file. Here is the link: http://www.box.com/s/pu1orimly6xtap6bqac5

[arlu1201]

Made a few adjustments to the code - try this -

Please Login or Register  to view this content.

[danielalmaty]

Thank you for your help!! Still, it just stucks when I try to run the macros. Probably it's too much information for my poor PC.

[arlu1201]

Yeah, its taking time because your file is linked to another file. I have made a few adjustments in the above code. I ran it and it took a few seconds only. Try it and let me know.

[danielalmaty]

Nope. It's not starti to wok for 10 mnutes already.

[arlu1201]

Hmm....are you getting any security warning at the top of your screen? If yes, click on Options -> Enable this content -> Ok -> Continue.

Another question - there is a file linked to your workbook. Is it open along with this file that you are using? You need to ensure its open.

If you do not need this linked file for your current worksheet, then copy the data from this sheet - SELL AudNzd to another blank workbook and run the code on it. Then you can copy back the results.