Lookup partial match and enter that in another column same row

Has this posted

Here, try this:

- You need to enter chad in the table (Not only chad Limited)
- You need to sort them alphabetically
- Won't find Central African Republic two since there is Central African Republic

OK Will do The bank is what I am mimicking. But your function is great Thanks. I will go away now. Study it and try my best to understand it.
Mark
Wish I was in Croatia. Told it a great place to windsurf.

You still have time to come English say it's cheap

Hi Zorbo
Ahh I'm Irish and I have plans to convince a friend of mine who has an old sailing boat currently moored in Malta to move it to Croatia. Hopefully some time this summer. So if you fancy a day or so - great.

Now my issues:-
Firstly thanks for the great help you have been. I started to study your work. I inserted in Orange, Chad Limited and Central African Republic Two as separate companies and your system caught them. Originally I had them in because I wanted to give myself difficulties.

I then studied your function as best I could. There are parts I don’t understand but will work more on.

Next I copied the file and added my categories using ctrl X and insert in the middle into the category list in My Attempts as this was the one you used. This forced your list to expand. I checked the numbers in the code. This drastically changed the company names in the company list generated by your code in What I have.

I Have copied a list of companies with matching categories into a new sheet called sheet 1.

I then copied the same list as separate columns IE Description into description on What I want.
This is what I had intended to do in the first instance but as you can see it has gone haywire.

Have you any suggestions?

I have uploaded my files test (1) created by you and added to as above.
I have also uploaded my file test (2) which is as discussed above.

This may be a bit of a headache and I will appreciate if you say so.

All the best.

Does this help:

First: You MUST sort Company names in my Attempts sheet alphabetically
For that just click arrow in H1 and sort them every time you add new value

- If there is no match it will return #N/A
- After inserting value it will become red if it's not found in string in A column (only such case is in B184) or if't is #N/A


I've add also category but it's different than yours. I don't know where you get values. Mine return correctly.

I also add in D column comparation between mine result in B column and yours in F column.
Difference is marked as False.

Second:
Excel don't read mind.
You can not enter for example '002018 and expect to return result (for example Anne Hopital) if you didn't defined what's criteria for such result.

Third: it look only for exact match.
So if you have WDFE , GATWICK and you have define Wdfe Gatwick it will return error since it can not find it.

or for example in H column you have defined three Starbucks's but third is with space at the end so formula return that since it has also space after Starbucks (and that make him different than your expected result).

Thanks I will go through it. Dragged away from it right now. Blast. The point you mention re '002018. This is precisely how the bank provide the data. It will never be possible to match the cheque numbers. They will always be entered by hand. I could tell you my full implementation plans for this project but never know how much to put here for fear of overload.
Sadly i ma not an expert at this an even though you give me a perfectly working solution I may **** it up. A bit line why does the cable for your head phone tangle. Because there is only one shape they can be in not to be tangled yet there are an infinite number of ways they can tangle.
Looking forward to working with your solution.
Thanks again and all the best.
have a great day as the Americans say.

If you define those numbers within same table then you can get result out of them.

Also, if you need help with plans for summer just let me know

Here are some tips for windsurfing (if you ask me I prefer southern Adriatic, but here are also some locations from the North):

Channel between peninsula Peljesac and island of Korcula

Most popular surf spot here is at place Viganj.
Korcula is an old town, and Dubrovnik is also close.

Bol, island of Brac

One of the most popular and one of the most beautifull beaches.
I know that there is a windsurfing school so it must be good

Island Krk

Best surf spots on Krk are Punat and Baska.
Could be more crowded than southern Adriatic during season.


Preluk

Surfing early in the morning, after that there is no much wind.
But because of nearby town Rijeka sea might not be so clear as other locations.

Ravni, peninsula Istra

Very popular for surfing. Quite strong western wind during afternoon.
great sea and benefit is closer upper location: There in the morning, and here afternoon and evening.

Premantura, peninsula Istra

Also one of top destinations close to upper two.

And last tip: Autumn might be better for windsurfing, less rush and cheeper accommodation. Weather is still warm during all September with more wind.

Hi Zorbo

Have to go and see planning officer today so will not be on this till later. Thanks for the info on windsurfing. Yes September is the best month her as well.

Great thing about having a boat is that there are almost no crowds. And living on the boat reduces the cost though my friend has the cost of mooring her all the year which is a bit costly. Don't know if you know much about this stuff but we want to totally re-fit the boat. It is an old boat one of the first fiber glass ever built. more of an ocean boat than a Med boat. Has crossed the Atlantic. I was thinking of Croatia as a place to get it refitted. The interior is wooden. Some say it should be left as it is to retain it's character. its 42' but being early is not a tear drop shape so a bit narrow.

Will get on to the Excel later today.

All the Best
mark

I know few shipwrights, both on wood and plastic. I could ask

P.S. Who is Zorbo you talking to :D

Apologies i had read your name as Norbo. Sorry.
re the boat
let me talk to my friend. But sounds a good idea.
here is a link to a photo of the boat.

http://www.theyachtmarket.com/boats_for_sale/338624/

Mark

Apologies i had read your name as Norbo. Sorry.
re the boat
let me talk to my friend. But sounds a good idea.
here is a link to a photo of the boat.

http://www.theyachtmarket.com/boats_for_sale/338624/

Mark

Looking great on open sea. I'll volunteer as a crew member
Just kidding. But if you need any help contact me over private message.

Hi Zbor

I have been trying to use the function but can't seem to. The moment I click on it the {} disappear. I have tried typing it out without success. Must be as thick as the wall but have you any advice.
It is a complex function which I have not gotten my head round as yet.

Hope the weather is good where you are. Cold and a little miserable here.
Mark

Hi Again
Just to say i worked out the percentages on the return.
Cheques 24 10%
Matched 131 62% 56%
Un Matched 77 36%

I have matched against what is possible not including the Cheque numbers. Looking at the matches and not fully understanding how the function works I am mystified as to why it does not match more.

All the best

Mark

Love to talk more about the boat. Perhaps there is something here as you seemed to be very knowledgeable on the best places to Windsurf. Do you windsurf or sail.

Hi Zbor

Here is the file that I am working on. For love nor money can I get it to work. I think I have replaced the sheet names and cell references correctly. please have a look if you have a moment. Have had to delete some sheets to get it up.

Thanks.
Mark

Originally Posted by markDuffy

I have been trying to use the function but can't seem to. The moment I click on it the {} disappear. I have tried typing it out without success.

it's an array function and must be entered with ctrl+shift+enter (not just enter).
A curely brackets will appear around your formula (but you can not enter them manualy).

Love to talk more about the boat. Perhaps there is something here as you seemed to be very knowledgeable on the best places to Windsurf. Do you windsurf or sail.

Neither. But I do fishing and spending all my vacation on the sea (where I originally come from, so I stil have lot of friends and all family there).

Going to see your example now. Hope I'll come back with more info.

Few things:

1. List of Company names (Column G at ControlSheet) MUST be sorted Alphabeticaly. I tolud you this few times already

2. You didn't had your formula on this column but on H column.

3. as I said above, formula must be entered with ctrl+shift+enter. Keep in mind that it might be slow with lot of data.

Hi Zbor

I have altered some of the names in Control Sheet to match those the bank uses (If need be which is unlikely and these have to be used in some other way and the full names are required then I can have a function to do that) and we have achieved 81%. This is not counting the cheques or the petty cash which are not matchable.

Thanks again
All the best
If we get to Croatia or plan to go there I will be in tough again.
Mark