Attempting to combine two lists

Hi,

Slightly different kind of query for me - it's actually to do with something else that I do - so I've made two example lists (as both contained over 2000 lines of data etc. and other things) to illustrate what I am trying to do - e.g. on a list as short as the attached - I could adjust it manually - on a 2000+ line list this could take hours.

In the attached; Worked_Example.xlsx

On tab 1 - is the "accurate list" - a colleague pulled this from a data source that I know is accurate, where as the current list I know is inaccurate. It lists all of the roads, and splits them where they fall into multiple areas
On tab 2 - is my "filter list" - where I have filtered by column A from tab 1 - which I copied and pasted, to try and break it down into a list, just listing all of the roads in the area. I advanced filtered by column A and the problem this causes is where roads F and I fall in multiple areas, it only lists one road in both cases (so I lose 3 roads in total). Is there a way round this - e.g. can I filter by 2 columns?
On tab 3 - is just my "filtered list" - just a paste/special/values of tab 2 - so I have a clean list going forward.
On tab 4 - is the "original list" - which I know is out of date and inaccurate. I have made a vlookup table of it though as I want the "control" data in column C - which my accurate list doesn't have.
On tab 5 - is a copy of my "filtered list" from tab 3, with a 3rd column column added to get the "control" data by vlookup from tab 4.

As well as the problem on tab 2 - about some roads being filtered out - I have multiple problem on tab 5 that I have tried to list

Road D - doesn't pick up as a VLOOKUP, as Road D had a typo on the original list. Is there any way of dealing with this? - I have filtered my tab 5, on my 2000 line list and have over 300 N/A's which I am trying to break down.
Road G - was not on the original list - this is fine as this is what I am trying to pick up.
Road F - one of the "Road F's" was missed off the tab 5 list as it wasn't picked up on the filter list on tab 2 (if this makes sense)
Road I - was the same as road F - 2 were missing on tab 2 - and it was also spelt differently on tab 4 - pulling up the N/A where road F still registered the one road F it picked on the VLOOKUP.

As you can see I have multiple problems with the original list and was just wondering if there were other functions I could use to try and do this more efficiently?

Thanks in advance for any suggestions.

I didn't think of looking at the problem this way - I think this will work. I have copies of the other document at home, and will try this out. When I looked at the problem, I was trying to fit the road names to the control data - there are a couple of columns (but if you can do it for one you can do it for all of them in principle), when I really should have been looking to fit the control data to the accurate list.

The real problem is the original list is over 3 years old, and wasn't put together with any kind of quality checking - as a result it is patchy (e.g. some roads are listed odds and evens for no reason e.g. Road A - odd numbers, Road A - even numbers - in reality I just need Road A).

I'm in all honesty just trying to get a complete and accurate list - which I have - but then to filter out what we don't have the control data for from the original list (if this makes sense). Am going to go away and have a go with this.

Thanks for the idea.

Originally Posted by newdoverman

Don't use the first solution! There is an error other than the wild card that allows a false result for Road G.

Something in Row 9 of the Original List onward is not right and I can't find it right now. The corrections in YELLOW do work.

I suggest that you get rid of the word "Road" leaving just the letter. The use of helper columns like I have in this solution makes it much easier to work with and the formulae are somewhat less complicated as a result. The word "Area" could also be taken out for the same reason.

The areas in YELLOW, are areas that I changed. The formula has taken out the wildcard.

Thanks. Just coming back on this, I can see that the idea would work and would probably get me closer to where I want to be in a quicker way. Just so you know the Road A, Road B, and Road C - were just names that I came up with as the actual list has data on it that I don't want out there if that makes sense. In reality it would be something like (am just making up street names here, using fruits and animals)

Apple Avenue
Bear Road
Cardinal Way
Elephant Street

Which made me think going back to your formula in the first one:

=IFERROR(INDEX('Original List'!$C$2:$C$12,MATCH("Ro"&"*"&B2,'Original List'!$A$2:$A$12&$B$2:$B$12,0)),"")

I am figuring the "ro" is to key on Road, as I'd made an example typo of Rod instead of Road? In reality using my example above though, I couldn't figure how I would work that (e.g. for example - "Cadinal Way" instead of "Cardinal Way")?

Also areas were made up for the same reason - I can see that you are from Canada, it would be the equivalent of provinces (in the actual list).

I will definately have a look over v2 - totally agree with the use of helper columns - I use them on other sheets.

I'm happy to bounce something around and thanks for taking the time.

Sorry just looking at this further - this would work in principle. Your formula doesn't return anything for Road G - as Road G isn't on the original list. As the original list is old and inaccurate - that's what I was expecting. If that returned a blank or an N/A I could filter those out and pull a list of roads missing from the original list. I think this may work fine. Will go away and have a look at this and will probably come back tomorrow - as it's getting late UK time.

Originally Posted by newdoverman

Stupid mistake.....found the error. It is in the formula. Using this formula, missing entries in Original List will result in a blank cell.

Enter this with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

The bold part was missing.

Using wildcards can be "dangerous" in that there can be unexpected and unwanted results. You can see the rather convoluted use of wildcards in the above formula. It does give results for everything except the missing value. Your real data shouldn't require this....I hope.

You might want to use the wildcards and if you do, don't forget to make the correction that is in bold. t would have been easier, I think to have been using more "realistic" data....hind-sight!

Thanks. Am going to definately take a look at this and run it against some of the real data (and will come back and mark this closed if it works out). I can see the need for wildcards I think - but where I have limited understanding of that side, it's why I tried to go in a different direction when I first looked at this. I'll definately come back on this, have a bit of a busy week at work but think that this will work. Apologies on not using the "realistic" data - given the size of the table at over 2000 lines, and the fact that only aout 10-15% of it was wrong, I found it really difficult to split it down into a managable sized example without posting the full thing online which I would not have been comfortable with. I think I understand enough of the formula that I should be able to re-work it to the actual sheet.

Originally Posted by newdoverman

You might want to use the wildcards and if you do, don't forget to make the correction that is in bold. t would have been easier, I think to have been using more "realistic" data....hind-sight!

Totally this. I had a chance to play around with the realistic data during my lunch-break today. The formula behaved inconsistently when I tried to adapt it to the realistic data (e.g. I put a right in on the A2 and B2 on the realistic data and took the first couple of letters with it as well as the way it was laid out for the example gave me some freak results). I think the concept works (I didn't think of the quickest way of doing this straight away - and then I just pasted the first 12 and 13 lines of the realistic data into the example workbook to spot why I was getting odd results) but I think I just need to apply the match constraints better. Will definitely come back on this. Thanks.

Originally Posted by newdoverman

In my opinion it is much easier to correct identified errors than it is to code for every possible error that could occur in this case.

Thanks. Apologies for the really late comeback on this - this is almost as much help as the formula (which is helpful as it gives me a different approach). I almost wanted to get a second opinion, on if it would be easier to try and pick this off with a formula or just take the time to pick through the data manually (which I did not fancy as it is going to take days - ironically it may not be my job any more ). I've marked this solved, as the problem I posted was solved - even though it would not work for my actual data (which was completely my fault).