keyword Vlookup Macro

[makinmomb]

I want the quantities of E to go into C

example 1

GODOOWN DATES VIP
VIP Dates 1084 ctn

I want that 1084 to go into c1 while the text are different a1 has the word godown , not necessarily the descriptions are exact without the word GODOWN , i want the macro to do a keyword search than plug the quantities

Look at these two that 1304 to go into from E to go into C2

GODOWN ABLE FARM CONDENSED 380 G X 48
ABle milk 380g 1304 ctn

It is a check of computer vs physical , it takes sometime to do manually do this by reading

See file attached

[AliGW]

Once again you have provided a workbook with NO mock-up showing what you are aiming for. How many times do we need to prompt you for a full and clear description and example workbook?

[makinmomb]

Quantity of E into C while i gave example

8545 the first one which reads Imperial soap Big 8545 ctn into into c 20 ( GODOWN IMPERIAL SOAP 200G )

[AliGW]

Put it into the workbook! SHOW us a few rows of example results! Please don't be lazy - you are expecting people to give of their time to help you, but can't be bothered to make it as easy for them as possible.

This is why there are many who no longer offer you help.

[makinmomb]

Find two results i put on c2 by manually reading

[AliGW]

And how, exactly, does that relate to column E?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
1	GODOOWN DATES VIP	1084.00 CRT	????
2	GODOWN ABLE FARM CONDENSED 380 G X 48	1304.00 CRT	????

Sheet: Sheet1

[makinmomb]

Column E has the quantity but text joined , i need the macro to do the needful

Column E is typed by a person reporting stock while Column A and B are computer generated
Column E is a non computer guy he does not wona listen that in order for VLOOKUP to perform description must be exact

[makinmomb]

Column E is a non computer guy again does not know excel so he manually types a report for which we want to match if computer stock is matching computer stock and he is not aware of office quantities of B of each stock

[AliGW]

You still haven't SHOWN us what you want. What is supposed to match with what?

Column E is a non computer guy he does not wona listen that in order for VLOOKUP to perform description must be exact

That's insubordination. Sack him and hire someone more competent and biddable.

Seriously, though - you still have not given us ANY examples in the workbook of any matches.

[makinmomb]

Computer E is a godown guy sitting at storage while , A and B are pulled from a system which allows export from another location

The need be macro need to find the quantity which are at the end of the description and plug them at c column after finding a maximum key search

HINT

Ask the macro to remove the word GODOWN of column A and the perform the needful Special Vlookup

[AliGW]

So you are not going to show us what you want?

I'm not fond of guessing games, so I'll leave it to someone with more patience. You are very frustrating in your unwillingness to SHOW us EXAMPLES of what you want.

after finding a maximum key search

This is what you are failing to demonstrate.

Good luck.

[makinmomb]

Some one down there has under stood

Africa Candles 190 ctn , take 190 from that put into C3 which reads GODOWN AFRICA CANDLES

[makinmomb]

ALIGW please wait there is someone who has understood and please wait for the needful results

[AliGW]

Ask the macro to remove the word GODOWN of column A and the perform the needful Special Vlookup

That won't work. If it would, it would be easy.

I'll leave you with this example: how do these items relate to each other and how would Excel know?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B
19	GODOWN IMPERIAL 115GMS	1250.00 CRT
20	GODOWN IMPERIAL SOAP 200G	8565.00 CRT

Sheet: Sheet1

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	E
1	Imperial soap Big 8545 ctn
2	Imperial soap Smoll 1250 ctn

Sheet: Sheet1

ALIGW please wait there is someone who has understood and please wait for the needful results

PROVIDE SOME EXAMPLES!!! How hard can it be? You know what results you want, so show us some.

[makinmomb]

It will work , the macro guys here can do that ,
they will ask the macro to pull the numeric from that text and
write a code to do the keyword search , wait and just watch

[makinmomb]

Give this thread a silence and people will tackle this situation the logic is not too sophisticated

[AliGW]

Give this thread a silence and people will tackle this situation the logic is not too sophisticated

Don't tell anyone here to stop responding. Provide the information they are asking for.

It is for YOU to explain the logic you want used, it is NOT for your helpers to have to work it out.

Again and again we have to prise the necessary information from you.

It would have been very easy for you to provide some example results in your workbook, but you refuse to do so.

It will work , the macro guys here can do that ,

Of course they can, and it could probably be done with a formula, but it cannot be done without knowing the LOGIC that you are wanting Excel to apply.

I see that you have chosen to ignore post #14 - is that because you can't work out the necessary logic?

[JohnTopley]

This is an issue of VERY "fuzzy" logic:

GODOWN EURO GOLD 2.5 KG X 6
GODOWN EURO GOLD 400 G X 24

vs

Euro milk 2500g 144 ctn
Euro milk 400g 1914 ctn


OR

GODOWN LITTLE STEPS NO 2
GODOWN LITTLE STEPS NO 3


vs

Little Sters Milk No .2. 30 ctn
Little Sters Milk No .3. 30 ctn

Mis-spellings adding to the problem!

No easy macro solution!

[makinmomb]

Where applicable , John , if it does not match some okay , at least if it can get me half the job done , i told ALIG someone understood she landing slapping me endlessly

[AliGW]

Where applicable , John , if it does not match some okay , at least if it can get me half the job done , i told ALIG someone understood she landing slapping me endlessly

It's AliGW, NOT AliG, and John is telling you the same thing as I was: the logic is too fuzzy.

[makinmomb]

It can easily do this kind maybe

GODOWN ARO PINK 377.00 CRT
Aro pink 277 ctn

[makinmomb]

It can handle this one JOHN

GODOWN EURO GOLD 400 G X 24 1914.00 CRT
Euro milk 400g 1914 ctn

Tell me why , you have EURO and 400 both common in that spelling variance , it should be able to do the needful

[TMS]

I can’t seriously see anyone jumping in. Ali and John have explained the issues. BUT, just for fun, I thought I’d have a go.

This formula replaces the first word (GODOWN, however it is spelt) in column A with nothing and then tries to MATCH what is left with column B. There are very few matches. As John said, there are mis-spellings, words in different order, things that may be meant to match but can't because different words are used and, I think, things that simply are not in the list.

=MATCH(REPLACE(A1,1,FIND(" ",A1,1),"")&"*",E:E,0)

DATES VIP    #N/A
ABLE FARM CONDENSED 380 G X 48    #N/A
AFRICA CANDLES    10
AL MUDISH 2.5 KG X 6    #N/A
ALDANAMAYA    17
AONE RIM    #N/A
ARO PINK    39
CITRON LEMON    #N/A
COVEX SOAP    40
DISPOSABAL GLASS    #N/A
DORCO BLADES CD    #N/A
EURO GOLD 2.5 KG X 6    #N/A
EURO GOLD 400 G X 24    #N/A
FALCON FOIL BIG    22
FALCON FOIL SMALL    #N/A
GAUTAM RICE 8 X 5 KG    #N/A
HASMAYA 450G X 20    #N/A
HIT SPRAY 400ML    #N/A
IMPERIAL 115GMS    #N/A
IMPERIAL SOAP 200G    #N/A
IVORY UKWAJU    4
KASUKU MATCHBOX    #N/A
LITTLE STEPS NO 2    #N/A
LITTLE STEPS NO 3    #N/A
MAGADI DATES    27
MILK ALMO 25KG    #N/A
MO EXCELLENCE SQUARE LINE    #N/A
MO SIMBA BATTERY BIG    #N/A
MO SIMBA BATTERY SMALL    #N/A
SAF INSTANT 500G X20    #N/A
SAF INSTANT SATCHET 30 X 12 X 11G    #N/A
SUGAR KILOMBERO - 1 X 20    #N/A
TIGER BATTERY BIG    #N/A
TIGER CANDLES    #N/A 

The first entry, DATES VIP, will not match VIP Dates. Note, words swapped over and two spaces.

If you want consistent Data Entry, you should use Data Validation, not try to clean the mess up afterwards.

[jindon]

Not 100%, but some of your results are really strange.

Sub test()
    Dim a(1), b, x, i As Long
    x = Range("a1", Range("a" & Rows.Count).End(xlUp)).Address
    a(0) = Evaluate("index(replace(trim(" & x & "),1,find("" ""," & x & "),""""),,)")
    ReDim b(1 To UBound(a(0), 1), 1 To 1)
    a(1) = Range("e1", Range("e" & Rows.Count).End(xlUp)).Resize(, 2)
    With CreateObject("VBScript.RegExp")
        For i = 1 To UBound(a(1), 1)
            a(1)(i, 2) = ""
            .Pattern = " *(\d+) *(ctn|bags?)$"
            If .test(a(1)(i, 1)) Then a(1)(i, 2) = .Execute(a(1)(i, 1))(0).submatches(0) & " CRT"
            a(1)(i, 1) = .Replace(a(1)(i, 1), "")
            .Pattern = "(\d)(\D)"
            a(1)(i, 1) = Application.Trim(.Replace(a(1)(i, 1), "$1 $2"))
        Next
    End With
    For i = 1 To UBound(a(0), 1)
        b(i, 1) = VLookLike(a(0)(i, 1), a(1))
    Next
    [c1].Resize(UBound(b, 1)) = b
End Sub

Function VLookLike(s, a) As String
    Dim i As Long, ii As Long, x(2), b(), n As Long, flg As Boolean
    x(0) = Split("^" & Replace(Application.Trim(s), " ", "^ ^") & "^")
    For i = 1 To UBound(a, 1)
        'If i = 28 Then Stop
        If UCase$(s) = UCase(Application.Trim(a(i, 1))) Then
            VLookLike = a(i, 2): Exit Function
        End If
        x(1) = Split("^" & Replace(Application.Trim(a(i, 1)), " ", "^ ^") & "^")
        For ii = 0 To UBound(x(0))
            x(1) = Filter(x(1), x(0)(ii), False, 1)
            If UBound(x(1)) = -1 Then VLookLike = a(i, 2): flg = True: Exit For
        Next
        If Not flg Then
            x(2) = UBound(Split("^" & Replace(Application.Trim(a(i, 1)), " ", "^ ^") & "^")) - UBound(x(1))
            If x(2) Then
                n = n + 1: ReDim Preserve b(1 To 2, 1 To n): b(2, n) = a(i, 2): b(1, n) = x(2)
            End If
        Else
            Exit For
        End If
        flg = False
    Next
    If Not flg Then
        If n Then
            x(0) = Application.Max(Application.Index(b, 1, 0))
            VLookLike = b(2, Application.Match(x(0), Application.Index(b, 1, 0), 0))
        End If
    End If
End Function

[makinmomb]

Thank you Jondon for doing the impossible possible ,

[makinmomb]

When running the macro i will rename Richoos Disposable to Disposable Glass so it runs the only missing , thank you again Jindon