Ask Your Question
0

move rows that match condition to different sheet with macro

asked 2019-11-01 16:56:06 +0100

vanek gravatar image

Hi,

I've got a calc sheet of data with date columns. Each day the sheet grows with one row and I need to put the last 30 days data on a chart. Since named ranges and functions as data source for charts are unsupported, I'm not quite sure how to achieve this. I want to avoid manual deletion of rows, or modifying the graph data source field daily.

Is there a way to move rows that are older than 30 days (column A is ISO format date, like 2018-12-31) to a different sheet? I don't want to copy them, I want to move them completely - I guess that way I can keep 30 days of data presented.

I never tried macro in excel/calc so not sure how to do it. Or if named ranges can be achieved for charts, please do let me know.

Thanks!

edit retag flag offensive close merge delete

Comments

If you attach a file with exactly you need, I help you with this macro.

mauricio gravatar imagemauricio ( 2019-11-01 20:16:08 +0100 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2019-11-01 21:49:37 +0100

erAck gravatar image

This doesn't need a macro to move data. You can do this with a formula that displays the last 30 values of a column. Assuming your dates are in column A (not more than 999 rows in this example) and corresponding data in column B, enter in (cells are just examples, you can do that anywhere but need to adapt the $F1 reference)

  • F1: =LOOKUP(2;1/A2:A999;ROW(A2:A999)) => result is the maximum row occupied with dates
  • F3: =A1 => the dates' column header
  • G3: =B1 => the corresponding data's column header
  • F4: =OFFSET(A1;$F1-30;0;30;1) entered as array formula (Shift+Ctrl+Enter) => last 30 dates
  • select F4:F33 and copy to clipboard
  • move cell cursor to G4
  • paste => result is corresponding last 30 data, note that the formula's reference A1 was adjusted to B1
  • select F4:F33 and apply your date format
  • if needed select G4:G33 and apply your data format
  • place cell cursor somewhere on data and create chart
edit flag offensive delete link more
1

answered 2019-11-02 01:26:54 +0100

m.a.riosv gravatar image

I think a couple of ways to do it.

Using standard/advanced filter you can define a range where to send the filtered data, and then do the chart on it.
Standard Filter

Using a pivot table, last versions support chart on pivot table, so no need to redefine ranges for the chart.
Creating Pivot Chart

edit flag offensive delete link more
0

answered 2019-11-02 09:13:11 +0100

vanek gravatar image

Thanks for all the suggestions, I ended up with a different solution where in separate columns I just do vlookup on past dates and copy their values, and build the chart on these fix columns.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-01 16:56:06 +0100

Seen: 43 times

Last updated: Nov 02