Map data based on effective date

R

richlyn9

New Member
Joined
Jun 13, 2009
Messages
11
Hi All,
I am unable to figure out how do i map data based on effective date
i have a record with transaction date, owner of transaction and another table which maps owner with department and effective date which tell me if an owner was transferred to a different dept and its effective date.
Hence when i am doing a simple lookup the dept mapping on the first table will change the values when the dept changes.

i am trying to build this on excel or access if possible to save time as data is large (25 MB file).
 
Sort by date Sort by votes
I must be missing something here - your second table is present dept and effective date - so why do you need first table at all ?
 
Upvote 0
oldbrewer said:
I must be missing something here - your second table is present dept and effective date - so why do you need first table at all ?
Click to expand...

Second table will have older dept mapping as well,
so for the records in the first table prior to dept change i want to retain the older dept for any such instances.

Table 1

Transaction Date | Owner
10/31/16 ABC
10/16/16 ABC

Table 2
Owner | Dept | Effective date
ABC 123 10/20/16
ABC 456 09/20/16


so when i map dept in Table 1, i should get 456 for the 1st record and 123 for the second.
 
Upvote 0
[TABLE="width: 981"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]owner[/TD]
[TD]curr dept[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]owner[/TD]
[TD]dept[/TD]
[TD]eff date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/10/2016[/TD]
[TD]abc[/TD]
[TD="align: right"]777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]abc[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]20/10/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/10/2016[/TD]
[TD]def[/TD]
[TD="align: right"]456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]def[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]15/10/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]abc[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]28/10/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]this macro has populated the date and curr dept columns[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]by working through the table on the right[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]it assumes dates are in ascending order[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]if they are not extra code will be needed to sort into ascending order[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]For j = 2 To 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For k = 2 To 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] If Cells(k, 9) = Cells(j, 2) Then temp = Cells(k, 11): dept = Cells(k, 10)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Cells(j, 1) = temp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Cells(j, 3) = dept[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
the below code works in excel, where both the tables are in to different sheet of the same woorbook
now to write this in access.


Public Sub test()
Sheets("Sheet1").Activate
lrow1 = Cells(Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("B2:B" & lrow1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("A2:A" & lrow1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Casesview").Activate
lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lrow
cnt = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("B:B"), Cells(i, "I").Value)
CreatedDate = Cells(i, "f").Value

FindString = Cells(i, "I").Value

For j = 1 To cnt
Sheets("Sheet1").Activate
With Sheets("Sheet1").Range("B:B")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
If cnt = 1 Then
Sheets("CasesView").Range("AD" & i) = ActiveCell.Offset(0, 1).Value
Sheets("CasesView").Range("AE" & i) = ActiveCell.Offset(0, 2).Value
Sheets("CasesView").Range("AF" & i) = ActiveCell.Offset(0, 3).Value
Sheets("CasesView").Range("AG" & i) = ActiveCell.Offset(0, 4).Value
Sheets("CasesView").Range("AH" & i) = ActiveCell.Offset(0, 5).Value
Else
If CreatedDate >= ActiveCell.Offset(j - 1, -1).Value Then
Sheets("CasesView").Range("AD" & i) = ActiveCell.Offset(0, 1).Value
Sheets("CasesView").Range("AE" & i) = ActiveCell.Offset(0, 2).Value
Sheets("CasesView").Range("AF" & i) = ActiveCell.Offset(0, 3).Value
Sheets("CasesView").Range("AG" & i) = ActiveCell.Offset(0, 4).Value
Sheets("CasesView").Range("AH" & i) = ActiveCell.Offset(0, 5).Value
End If
End If
Sheets("CasesView").Activate
Next
Next
End Sub
 
Upvote 0
My data runs in to over 60, 000 rows and hence this is dosent seem like a very good solution to me.
Can such a thing be done in Power BI or access?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Calculated Field based on Data in Column
Replies
0
Views
115
sea_captain
S
J
  • Question Question
Law Firm Needing Some Excel Help
Replies
1
Views
144
Candyman8019
C
H
  • Solved
Array transactions not mapped
Replies
3
Views
66
Peter_SSs
Peter_SSs
kristell
  • Solved
Pivot table date format
2
Replies
10
Views
307
RoryA
RoryA
R
  • Question Question
Checkbox problems
2
Replies
12
Views
542
randomdrums
R

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