If formula finds corresponding data, copies into table- how?

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I am attempting to create an automated data grab type formula which I will then use to set up an automatic email macro.

What I'm looking to do is have a formula which looks through the table (table 2), finds any dates that are more than 2.5 years old (some sort of a if/find function using today()-912 I would presume) then locates the corresponding training course title in row two, and name in column C&D (it may be easier to concatenate these into one? Not sure.). Then copy this data into a table in another sheet (outstanding training) with said mined data.

So it would look at this:

1597653740594.png


and regurgitate this:

1597654183583.png


Has anyone done anything like this before? I'm afraid this is beyond the realms of my understanding...so any help appreciated!

Thanks all :)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
First of all, we need to know if your dates are valid or not. Referring to the table in the top image, does =ISNUMBER(G7) return TRUE or FALSE?
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Hi Jason and thanks for the assistance. It returns TRUE.

I just realised that a macro or something in VBA may be more applicable than just asking for a formula- sorry for being basic!

BTW just seen your signature line- I am using Excel 2013.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
I just realised that a macro or something in VBA may be more applicable
I did think that after taking a second look at the image and your original post. Personally, I would do it with PQ, but that is something that I'm still getting to grips with on basics so not sure how to automate it.

A few more things to clarify before I start off on the wrong route.

Are there more columns with dates to the right of the image that should also be checked?
If yes, are there also columns to the right that should be excluded?

Are the dates manually entered or produced by existing formulas?

Should we assume that only rows with 'Yes' in column A should be included?
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No idea what PQ even is..!

Yes lots more columns, up to AQ at present but this may/will increase as more courses are identified as being required. All of these columns should be included however it may be useful to know how to add a functionality that excludes courses that do not require refresher training.

Dates are manually entered.

Column A is actually redundant, I have moved all 'no' employees (ex employees) to another sheet for simplicitys' sake.

I may actually delete that now...
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
You might not even have PQ, I'm not entirely sure when it was added as a standard feature, although I believe that it can still be installed and used with some older versions.
If you do have it, it will be on the Data tab of the ribbon, look for 'Get Data' on the left side of the ribbon.
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nope, no signs of PQ here.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
Keeping it as simple as possible for a first test, input Sheet1, output Sheet2, top left course date cell G3. These points will need to be edited in the code before you try to run it.

Assumed output sheet starts empty, nothing included for checking existing data there.

As always, use test data in order to prevent data loss in the event of problems.
VBA Code:
Option Explicit
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim c As Range, rng As Range, rw As Long
Set ws1 = Worksheets("sheet1"): Set ws2 = Worksheets("sheet2")
Set rng = ws1.Range("G2", ws1.Range("G2").SpecialCells(xlLastCell))
rw = 2
For Each c In rng.SpecialCells(xlConstants)
    If IsDate(c.Value) Then
        If Date > (c.Value + 912) Then
            ws2.Cells(rw, 1) = ws1.Cells(c.Row, 3) & " " & ws1.Cells(c.Row, 4)
            ws2.Cells(rw, 2) = ws1.Cells(2, c.Column)
            ws2.Cells(rw, 3) = c.Value
            rw = rw + 1
        End If
    End If
Next
End Sub
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Thanks for this, you're going to have excuse my lack of knowledge here though. I don't understand what points need editing, it looks like you have the right info there?

I have popped this into VBA as follows:

1597755389258.png


Not got anywhere with it just yet though- does it need a button to make it fire? I have selected follow hyperlink for this reason and put a rectangle shape in to use as a button for the time being, assuming this is yes.

Thanks for your help!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
I don't understand what points need editing,
The sheet names and the top left cell of the date range need editing, the bits in bold below. I've done the 2 in the top line based on the image above, if you deleted the 'Currently Employed' column and moved everything else to the right then G2 will probably need changing to F2.
Rich (BB code):
Set ws1 = Worksheets("Summary"): Set ws2 = Worksheets("Outstanding Training")
Set rng = ws1.Range("G2", ws1.Range("G2").SpecialCells(xlLastCell))

To assign it to a button / shape, you just right click the shape and 'assign macro'. This must be a normally named macro in a standard module. Follow hyperlink is for hyperlinks in cells.
If you want it to run automatically it would be best done either when the file is opened or when it is saved.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,001
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top