Logging Data in same sheet when criteria is met

Topic

New Reply

Hi…

I get data into excel sheet from another application continuously.

I want to create a log with latest data on top when any of criteria is met. (in the same sheet).

Attaching herewith the excel file.

Reply | Quote

Replies

Dear Bhushanvshah:

Attached is a solution. It uses a new formula in Col L and then advanced Data Filter.

Each time you populate the sheet you will need to go to the “Data Ribbon” and select Filter “Advanced” make sure to select copy to a different location and press OK. Each time you will get the results.

The first worksheet works for Excel 10 and above, the second worksheet will work with lower versions.

Lastly, the criteria for returning information had a conflict. Your first Criteria was all stocks that equal High which by definition would pick up stocks both above and below 75 therefore the >75 AND High does not pick up new items. Also the information provided were outside the criteria so the log would result in no finds so I added more data that meet the criteria.

Hope this helps.

TomD

Reply | Quote

Thanks TomD for your reply.

I am looking for a VBA, as the input is continuously changing. I want to log the data when the criteria is met. There would be multiple instances when the criteria is met for the same row itself, as all the data changes continuously.

rgds
bhushan

Reply | Quote

Bhush,

I would take the approach of designating a column for a time stamp that gets placed for each record as it is imported from your application with the criteria met. The code would finish up with a sort on the time stamp column placing your most recent data at the top.

You would need to describe the format of how the data is exported from your application and into your spreadsheet. A sample would be beneficial. You also need to elaborate on the criteria that the data is weighed against.

Maud

Reply | Quote

Dear Bhushanvshah:

Does the attached help? It uses 3 pivot tables to show results. By Clicking the Text Box “Update Tables” it will refresh all three pivot tables and give the results in your request.

Currently the Pivot Table is set to pick up 5,000 lines of data. Of course if more lines are needed you will need to change the data source via the Pivot Table Ribbon. I also included a reference to the row number where the data is being pulled from.

Regards,

Tom D

Reply | Quote

Thank you Tom D.. It works absolutely well.. thnks again

Reply | Quote