Selective copying based on criteria

default_name

Board Regular
Joined
May 16, 2018
Messages
147
Office Version
  1. 2016
Platform
  1. Windows
  2. 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.
ABC....ARAS...GBGCGD
2134675448-898-5641Y
....​
2/21/2005QGC
....​
UPLA5451388Blue
4041384357-8243-863Y
....​
5/15/2019QRP
....​
USLS1545348Blue
5135454319-999-5621X
....​
8/8/2023QGC
....​
UPLC1534688Green
45135451355-43354-4Y
....​
5/14/2020QPA
....​
URST6435412Red
45134545466-4548-87X
....​
5/18/2018QHU
....​
UGJR1513334Red
45156737848-000-264C
....​
6/20/2024JGKS
....​
UJGL1324858Green

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)
BlueQRP5/15/2019404138435
GreenQGC8/8/2023513545431
RedQPA5/14/2020451354513
GreenJGKS6/20/2024451567378

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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Again with pleasure. Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,113,824
Messages
5,544,539
Members
410,619
Latest member
gregor222
Top