Lookup specific string and reformat data

[YasserKhalil]

Hello everyone
I have two sheets (The first sheet is Main and the second is Table)

I have put the expected output in columns (E:I) to illustrate the process :
--------------------------------------------------------------------------------------
* The headers are required E5:I5

* Column E & F are results of the search for SKU code (column C)
Example : SKU code "TVS067-01S" >> take the first part only which is "TVS067" and lookup this value in sheets("Table") in columns B & C & D >> If found take
both values in columns E & F (here this is in D113 so the value "Valar moghulis" & 1130) put in Main sheet in columns E & F

* column G : look in the cell in column B (Design description) and type "Couple" if Couple or Couples string are found (this is priority to male or female) -- type "Male" if the string Mens found -- type "Female" if the string Womens is found ... If none of these strings found, then alert message appears to determine where is the error and exit sub

* Column H : look in the cell in column B (Design description) and search for one of these colors (Black - Grey - White)
If none of these three colors exist then an alert message appears to determine where is the error and exit sub

* Column I : look in column B also.. If "Couple" or "Couples" then type "Get sizes" -- If "Mens" or "Womens" then type the size which is after color in the string
Abbreviation is required here (L for Large, S for Small, M for Medium)
The available sizes are S / M / L / XL / XXL / Get sizes only and if there is something wrong another alert message appears to determine where is the error and exit sub

* As for Couple gender ,the price should be >=799 or alert message appears and exit sub too

I know it may be complicated so I put every detail
Thanks advanced for help

[HansV]

Hi Yasser,

As you have found, I and others here are very willing to help. But now you're asking us to provide a complete solution. That is beyond the scope of Eileen's Lounge; we don't develop free software.

You should be able to do most of what you ask yourself. Some of it can be done using formulas. If you get stuck, you are very welcome to ask for help on specific points.

[YasserKhalil]

Thanks a lot for reply
In fact I don't know how to start .. this is point and another point I need to store results in VBA arrays and this is beyond my knowledge
It seems that this is very long as I explained in details
Will you provide me part of the solution?
Let's take one point only which is the search for SKU and bring related data in columns E & F
Thank you for great support

[HansV]

For example in E6:

=IFERROR(VLOOKUP(LEFT(C6,FIND("-",C6)-1),Table!$D$6:$F$119,2,FALSE),IFERROR(VLOOKUP(LEFT(C6,FIND("-",C6)-1),Table!$C$6:$F$119,3,FALSE),IFERROR(VLOOKUP(LEFT(C6,FIND("-",C6)-1),Table!$B$6:$F$119,4,FALSE),"-")))

In F6 the same, but with the column numbers increased from 2, 3 and 4 to 3, 4 and 5.

[YasserKhalil]

I have figured the part of Gender and color and siz using vba arrays ..Please give me a hand in achieving the task

Code: Select all

Sub Test()
    Dim arr, temp
    Dim I As Long, J As Long
    Dim fGender, fColor, fSize

    arr = Sheets("Main").Range("B5:D" & Sheets("Main").Cells(Rows.Count, 2).End(xlUp).Row).Value
    ReDim temp(1 To UBound(arr, 1), 1 To 5)

    temp(1, 1) = "Design name": temp(1, 2) = "Design number": temp(1, 3) = "Gender": temp(1, 4) = "Color": temp(1, 5) = "Size"

    For I = 2 To UBound(arr, 1)
        If InStr(arr(I, 1), "Couple") > 0 Then
            temp(I, 3) = "Couple"
            If arr(I, 3) < 799 Then MsgBox "Be Careful Couple Price Is Less Than 799", vbExclamation: Exit Sub
        ElseIf InStr(arr(I, 1), "Mens") > 0 Then
            temp(I, 3) = "Male"
        ElseIf InStr(arr(I, 1), "Womens") > 0 Then
            temp(I, 3) = "Female"
        Else
            Set fGender = Sheets("Main").Columns(2).Find(arr(I, 1))
            MsgBox "Error In Gender Field In Cell " & fGender.Address(0, 0), vbExclamation: Exit Sub
        End If

        If InStr(arr(I, 1), "Black") > 0 Then
            temp(I, 4) = "Black"
        ElseIf InStr(arr(I, 1), "Grey") > 0 Then
            temp(I, 4) = "Grey"
        ElseIf InStr(arr(I, 1), "White") > 0 Then
            temp(I, 4) = "White"
        Else
            Set fColor = Sheets("Main").Columns(2).Find(arr(I, 1))
            MsgBox "Error In Color Field In Cell " & fColor.Address(0, 0), vbExclamation: Exit Sub
        End If

        temp(I, 5) = MyUDF(CStr(arr(I, 1)), temp(I, 4) & "_", "")
        If temp(I, 3) = "Couple" Then temp(I, 5) = "Get sizes"
        temp(I, 5) = Replace(Replace(Replace(temp(I, 5), "Small", "S"), "Large", "L"), "Medium", "M")
        If temp(I, 5) <> "S" And temp(I, 5) <> "M" And temp(I, 5) <> "L" And temp(I, 5) <> "XL" And temp(I, 5) <> "XXL" And temp(I, 5) <> "Get sizes" Then
            Set fSize = Sheets("Main").Columns(2).Find(arr(I, 1))
            MsgBox "Error In Size Field In Cell " & fSize.Address(0, 0), vbExclamation: Exit Sub
        End If
    Next I

    Sheets("Main").Range("E20").Resize(UBound(temp, 1), UBound(temp, 2)).Value = temp
End Sub

Function MyUDF(s As String, b As String, a As String) As String
    Dim arr() As String
    Dim r As String

    arr = Split(s, b)

    If UBound(arr) > 0 Then
        r = arr(1)
        arr = Split(r, a)

        If UBound(arr) > 0 Then
            r = arr(0)
        End If
    End If

    MyUDF = Trim(r)
End Function

I welcome any correction. That was for gender column & color & size columns

Just need the lookup process
Thanks for help

[HansV]

For color, you could look at the color name enclosed in underscores. For example, for Black, check if "_Black_" is found in the design description.
For size, you could look at the size prefixed by an underscore. For example, for XL, check if the design description ends in "_XL".

[YasserKhalil]

Thanks a lor for reply Mr. Hans
Can you help me devising UDF for looking up the string through three columns? and retrieve two values in two different cells
Or any idea suitable to the first two columns

[HansV]

I already gave you a formula. Why do you need a UDF?

[YasserKhalil]

UDF can be easily used in other subs. That's the reason

[HansV]

That hardly seems an advantage, but here you go:

Code: Select all

Function GetData(SKU As String, LookupRange As Range, Col As Long) As Variant
    Dim s As String
    Dim a() As String
    Dim c As Range
    Dim i As Long
    a = Split(SKU, "-")
    s = a(0)
    For i = 1 To 3
        Set c = LookupRange.Columns(i).Find(What:=s, LookAt:=xlWhole, MatchCase:=False)
        If Not c Is Nothing Then Exit For
    Next i
    If c Is Nothing Then
        GetData = "-"
    Else
        GetData = c.Offset(0, Col - i)
    End If
End Function

In E6: =GetData($C6,Table!$B$6:$F$119,4)
In F6: =GetData($C6,Table!$B$6:$F$119,5)

You can fill this down.

[YasserKhalil]

That's perfect Mr. Hans
You have saved me with this wonderful and awesome solution
Now the code is complete
Thank you very very much for wonderful support