Log In
Register
Home
Active Member
June 7, 2019
Offline
Hello,
I may be using the wrong function but I believe VLOOKUP is what I need. Here is my problem. I have a large amount of data with different attributes in each of the two sets of data. For instance
one set has (document#, date, time, road name) the other has (date, time, road name, + many more)
The goal is to match the document# to the data that includes all the extra attributes
The problem is that the time may be off by a couple minutes and not an exact match and the road name may be (Arthur DR.) in one cell and in the matching cell (Arthur DRIVE). I have been able to easily match the documents manually but as there are hundreds of these documents I am trying to find a quicker way to match them up. The cells are also not perfectly lined up as there are only 100 documents to match and maybe 1000 that I am searching for the match in.
I do not know if this is all the information needed to make the problem clear but I need help please!
Let me know if more information is needed
VIP
Trusted Members
December 7, 2016
Offline
Hello,
It is always easier if we can see the data, a small sample file is good enough.
Active Member
June 7, 2019
Offline
Hi,
Here is a sample. I have highlighted the matching data. I am trying to connect the data on the left to the data on the right.
VIP
April 21, 2015
Offline
Well the number is the matching point for the Vlookup, but I can't follow you in what comes from one source and what from the other and what you want to combine. I think the columns C:I in your example are the ones descriped as 'document#' and the columns M:S the ones with the more data.
What do you want as output from these two? And what to do with the rows that belong to a certain number, but where in the column M the number is not repeated?
Frans
VIP
Trusted Members
December 7, 2016
Offline
Hello,
As you probably already know, by having lookups without using exact match you will end up with incorrect results, sooner or later. In order to trust the data you need to know it is showing correct values, else a message or error of some kind.
With this said, I find it difficult to find a good solution based on your data. If we could have a unique value on which we could combine the two data sets this would be a whole different issue.
November 8, 2013
Offline
Hi Charles,
Only a visual basic based solution can help, or Power Query.
Attached is a power query solution, the query I used is:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"DateTime", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Match", (x)=> Table.FirstN(Table.SelectRows(Table2, each [#"Date/time"] >= x[DateTime]),1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Match", {"Date", "Date/time", "Street Address", "City Name", "State Name", "Postal Code"}, {"Match.Date", "Match.Date/time", "Match.Street Address", "Match.City Name", "Match.State Name", "Match.Postal Code"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Match.Date", type date}, {"Match.Date/time", type datetime}})
in
#"Changed Type1"
The logic behind the query is:
Select from Table2 all rows with DateTime greater or equal to Table1 -DateTime, and keep just the first row.
1 Guest(s)
