Drawing information from one table to another

[MYMunshi]

Hi

I have two tables in separate tabs within a spreadsheet.


Table 1: 'EmpInfo'

	A	B	C	D	E	F
1	REC	Start	End	WT/PT	Last Updated	Reason for Update
2	1	01/01/2010	02/02/2012	PT	02/02/2012	333
3	2	02/02/2012	17/06/2015	WT	17/06/2015	333
4	3	18/06/2015	24/01/2016	WT	24/01/2016	333
5	4	25/01/2016		PT	01/01/2017	201
6	5	12/04/2017		PT

<tbody>
</tbody>

Table 2: 'UpdtInfo'

	A	B	C
1	REC	Last Updated	Reason for Update
2	1	01/01/2011	201
3	1	01/01/2012	201
4	1	02/02/2012	333
5	2	01/01/2013	201
6	2	01/01/2014	201
7	2	01/01/2015	201
8	2	17/06/2015	333
9	3	01/01/2016	201
10	3	24/01/2016	333
11	4	01/01/2017	201

<tbody>
</tbody>

I need a formula that will drag the most recent date from column B in 'UpdtInfo' of the appropriate REC to 'EmpInfo'.
The same for the 'Reason for Update' code associated to the appropriate REC to be mirrored into 'EmpInfo'.

REC5 is an employment that commenced after the yearly update period (01/01/XXXX) so the cells in 'EmpInfo' for E6 and F6 need to be blank (I'd presume they'd remain blank anyway because there is no entry for REC5 in Table 2).

Could I ask for help on two formulas on these two problems pleeeease?

Thank you so very much kindly

 

[mumps]

Rather than a formula, I am suggesting a macro. If you are willing to try it, start by making sure that all columns in both worksheets that have dates are formatted as 'Date'. Then do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub UpdateInfo()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("updtInfo").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim REC As Range
    Dim foundREC As Range
    Dim foundDate As Object
    Dim dDate As Date
    Dim DateRow As Long
    For Each REC In Sheets("updtInfo").Range("A2:A" & LastRow)
        Sheets("updtInfo").Range("A1:C" & LastRow).AutoFilter Field:=1, Criteria1:=REC
        dDate = Application.WorksheetFunction.Max(Sheets("updtInfo").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible))
        Set foundDate = Sheets("updtInfo").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Find(dDate, LookIn:=xlValues)
        Set foundREC = Sheets("EmpInfo").Range("A:A").Find(REC, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("EmpInfo").Range("E" & foundREC.Row) = dDate
        Sheets("EmpInfo").Range("F" & foundREC.Row) = Sheets("updtInfo").Range("C" & foundDate.Row)
    Next REC
    If Sheets("updtInfo").FilterMode Then Sheets("updtInfo").ShowAllData
    Application.ScreenUpdating = True
End Sub

If this doesn't work for you, perhaps you could upload a copy of your file to a site a such as www.dropbox.com. Mark it for sharing and you will be given a link to the file that you can post here.

 

[MYMunshi]

Eeeek

I've never done anything like this before!! I can definitely give it a try, you've given all that information, a try is the least I can do compared to the graft you've done in your reply

I will try and let you know how I get along

Thank you once again for your kind reply

 

[MYMunshi]

Rather than a formula, I am suggesting a macro. If you are willing to try it, start by making sure that all columns in both worksheets that have dates are formatted as 'Date'. Then do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub UpdateInfo()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("updtInfo").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim REC As Range
    Dim foundREC As Range
    Dim foundDate As Object
    Dim dDate As Date
    Dim DateRow As Long
    For Each REC In Sheets("updtInfo").Range("A2:A" & LastRow)
        Sheets("updtInfo").Range("A1:C" & LastRow).AutoFilter Field:=1, Criteria1:=REC
        dDate = Application.WorksheetFunction.Max(Sheets("updtInfo").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible))
        Set foundDate = Sheets("updtInfo").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Find(dDate, LookIn:=xlValues)
        Set foundREC = Sheets("EmpInfo").Range("A:A").Find(REC, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("EmpInfo").Range("E" & foundREC.Row) = dDate
        Sheets("EmpInfo").Range("F" & foundREC.Row) = Sheets("updtInfo").Range("C" & foundDate.Row)
    Next REC
    If Sheets("updtInfo").FilterMode Then Sheets("updtInfo").ShowAllData
    Application.ScreenUpdating = True
End Sub

If this doesn't work for you, perhaps you could upload a copy of your file to a site a such as www.dropbox.com. Mark it for sharing and you will be given a link to the file that you can post here.

Hi

I've only had a limited amount of time today just to work on it, but I've tried to do it the formula way (I will definitely try it the Macro way today after work), but I was wondering if there was any way a formula solution could be possible for this problem.

I came up with one (and another formula for the other column). I think the formulas are half-way successful, but I've added comments in the Dropbox link below to explain what's happened and what I'm trying to do. I'd be most grateful if you'd be able to have a look for me please. For the sake of convenience, I've put both tables onto the same worksheet and not separate tabs.

https://www.dropbox.com/s/4p83zh99fn7xrql/Emp Info.xlsx?dl=0

Thanks again for your kind help

 

[MYMunshi]

Hi

I've only had a limited amount of time today just to work on it, but I've tried to do it the formula way (I will definitely try it the Macro way today after work), but I was wondering if there was any way a formula solution could be possible for this problem.

I came up with one (and another formula for the other column). I think the formulas are half-way successful, but I've added comments in the Dropbox link below to explain what's happened and what I'm trying to do. I'd be most grateful if you'd be able to have a look for me please. For the sake of convenience, I've put both tables onto the same worksheet and not separate tabs.

https://www.dropbox.com/s/4p83zh99fn7xrql/Emp Info.xlsx?dl=0

Thanks again for your kind help

Apologies, I forgot to include the table with the formulas:

https://www.dropbox.com/s/x8xcmswlb2zagym/Emp Info (formulas).xlsx?dl=0

 

[mumps]

Unfortunately, using formulas is not my strong suit. Have you tried the macro I suggested?

 

[MYMunshi]

Unfortunately, using formulas is not my strong suit. Have you tried the macro I suggested?

Hi mumps

Wanted to say thank you, I tried the solution and it worked like a charm

Problem solved!

 

[mumps]

Glad it worked out.