Drawing information from one table to another

M

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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
mumps said:
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.
Click to expand...

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
MYMunshi said:
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 :)
Click to expand...

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
You must log in or register to reply here.

Similar threads

M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
288
shift-del
shift-del
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
339
excelnewbie999
E
D
  • Question Question
How to Copy rows from one sheet to another based on the criteria in the second sheet
Replies
9
Views
178
dsubash
D
A
  • Question Question
Find entries in a sheet available or not
2
Replies
10
Views
494
AFZAL SOHAIL
A
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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