Drawing information from one table to another

MYMunshi

New Member
Joined
Apr 16, 2016
Messages
16
Hi

I have two tables in separate tabs within a spreadsheet.


Table 1: 'EmpInfo'

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]REC[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]WT/PT[/TD]
[TD]Last Updated[/TD]
[TD]Reason for Update[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]01/01/2010[/TD]
[TD]02/02/2012[/TD]
[TD]PT[/TD]
[TD]02/02/2012[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]02/02/2012[/TD]
[TD]17/06/2015[/TD]
[TD]WT[/TD]
[TD]17/06/2015[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]18/06/2015[/TD]
[TD]24/01/2016[/TD]
[TD]WT[/TD]
[TD]24/01/2016[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]25/01/2016[/TD]
[TD][/TD]
[TD]PT[/TD]
[TD]01/01/2017[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]12/04/2017[/TD]
[TD][/TD]
[TD]PT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2: 'UpdtInfo'

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]REC[/TD]
[TD]Last Updated[/TD]
[TD]Reason for Update[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]01/01/2011[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]01/01/2012[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]02/02/2012[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]01/01/2013[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]01/01/2014[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]01/01/2015[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]17/06/2015[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]01/01/2016[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]24/01/2016[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]01/01/2017[/TD]
[TD]201[/TD]
[/TR]
</tbody>[/TABLE]


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 :)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
Code:
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.
 
Last edited:
Upvote 0
Eeeek :eeek:

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 :)
 
Upvote 0
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.
Code:
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 :)
 
Upvote 0
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
 
Upvote 0
Unfortunately, using formulas is not my strong suit. Have you tried the macro I suggested?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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
Back
Top