default_name
Board Regular
- Joined
- May 16, 2018
- Messages
- 170
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
Hi guys!
I have kind of a complex question.
It all starts with a very large spreadsheet (let's call it 'raw data'). It has many columns of information (A:GD).
Much of this data is pretty useless to me, to be honest.
But there are a few pieces of information that I want to extract (if the row meets a certain criteria) onto a new sheet (let's call it 'reduced data').
The criteria is date (found in column AR).
If the date in column AR comes after 'today's date minus two years' [example: today is May 21, 2020. if the date is after May 21, 2018] then I want to grab relevant data from certain columns in that row and paste it into the new sheet.
Fictional Data Example:
The following represents the 'raw data' table (including some unwanted data). I skipped a few columns (shown with the '....') just to show the scale of the data.
The raw data table also changes/varies in number of rows from time to time.
If I have this data, then I would want to look at the cell in column AR. If the date comes after 'today's date minus two years' then I want to copy over desired data from that row.
If it comes before that date, then the data is ignored and the VBA moves on to check the next row.
The following represents the new 'reduced data' sheet where the important data is pasted.
Notice how data from only a few rows were copied over.
Also notice that the data was copied over in a particular column order.
I am hoping to achieve this routine via VBA code. I am having issues, though, because the table length (number of rows) can vary/change too.
I hope this makes sense.
I have kind of a complex question.
It all starts with a very large spreadsheet (let's call it 'raw data'). It has many columns of information (A:GD).
Much of this data is pretty useless to me, to be honest.
But there are a few pieces of information that I want to extract (if the row meets a certain criteria) onto a new sheet (let's call it 'reduced data').
The criteria is date (found in column AR).
If the date in column AR comes after 'today's date minus two years' [example: today is May 21, 2020. if the date is after May 21, 2018] then I want to grab relevant data from certain columns in that row and paste it into the new sheet.
Fictional Data Example:
The following represents the 'raw data' table (including some unwanted data). I skipped a few columns (shown with the '....') just to show the scale of the data.
The raw data table also changes/varies in number of rows from time to time.
If I have this data, then I would want to look at the cell in column AR. If the date comes after 'today's date minus two years' then I want to copy over desired data from that row.
If it comes before that date, then the data is ignored and the VBA moves on to check the next row.
| A | B | C | .... | AR | AS | ... | GB | GC | GD |
|---|---|---|---|---|---|---|---|---|---|
| 213467544 | 8-898-5641 | Y | .... | 2/21/2005 | QGC | .... | UPLA | 5451388 | Blue |
| 404138435 | 7-8243-863 | Y | .... | 5/15/2019 | QRP | .... | USLS | 1545348 | Blue |
| 513545431 | 9-999-5621 | X | .... | 8/8/2023 | QGC | .... | UPLC | 1534688 | Green |
| 451354513 | 55-43354-4 | Y | .... | 5/14/2020 | QPA | .... | URST | 6435412 | Red |
| 451345454 | 66-4548-87 | X | .... | 5/18/2018 | QHU | .... | UGJR | 1513334 | Red |
| 451567378 | 48-000-264 | C | .... | 6/20/2024 | JGKS | .... | UJGL | 1324858 | Green |
The following represents the new 'reduced data' sheet where the important data is pasted.
Notice how data from only a few rows were copied over.
Also notice that the data was copied over in a particular column order.
| A (copied from GD) | B (copied from AS) | C (copied from AR) | D (copied from A) |
|---|---|---|---|
| Blue | QRP | 5/15/2019 | 404138435 |
| Green | QGC | 8/8/2023 | 513545431 |
| Red | QPA | 5/14/2020 | 451354513 |
| Green | JGKS | 6/20/2024 | 451567378 |
I am hoping to achieve this routine via VBA code. I am having issues, though, because the table length (number of rows) can vary/change too.
I hope this makes sense.
