Struggling with VLOOKUP OR IF statement

[MikeJHHT]

I apologize if this sounds confusing in advance,

Column K contains a concatenate for the warehouse software i use for entering sales orders based off the first truck number.
Column R adds additional sales orders with the same truck number so it wont repeat step 1 which is K.

When i copy the sales order information into excel, it will fill in column B and D.

For every same customer, the truck numbers will be the same (unfortunately my example shows D11 should be with truck # 755 but customer service entered the name differently...

Is it possible if there is only truck number 750, column U will show (k2), but if there are more 750's it will add each additional cell from R

for example U2, should be =K2&R2&R3&R4&R5&R6&R7&R8&R9 (which is the start of the concatenate(K2) followed by the additions in (R)

also, im entering the truck numbers manually based off the similar customer names, is there a statement that would automate this?

Ive been struggling to search using the right terminology to get exactly what im looking for.
Thankyou as always for your time.

 

[MikeJHHT]

mass.shiplist MAGIC.xlsm

Shared with Dropbox

www.dropbox.com

correction, use this one
and click on the WILL CALL TAB

 

[mumps]

A12(753) has the same data as A13 to A15

Can you explain what "has the same data" means?

K2 and P2 cannot be used together, There is an SO(sales order) in Column B, which is placed in both K and P, Column S decides if its the header or a secondary.

You say that K and P cannot be used together but then you say:

the order would be K12&P13&P14&P15

and this puts K and P together. I don't understand what you mean by "Column S decides if its the header or a secondary". I'm afraid that I'm very confused.

 

[MikeJHHT]

A12 is truck number 753 which goes with D12 (ALBERS FIREPLACE) All of Albers Fireplaces will be truck number 753.

Correct K12 and P12 cannot be used together
but
K12 and P13 can be used together.
If the sales order from B12 begins in K12, it cannot be added additionally in P12.
Column P is only used When column K Has been started for the initial truck number

Column S =IF(ISNUMBER(SEARCH(D2,D1)),P2,K2).
looks at Column D and says, If D2 is the same as D1 then use P, if different use K
meaning if the customer above D2 is the same, use the "additional" concatenate,(P2), if the customer is new or the start of the same customers in D, use K.
I was proud i figured this one out.

 

[mumps]

Start by deleting all the truck numbers in column A. Assign this macro to the "Magic" button.

Sub MAGIC()
    Application.ScreenUpdating = False
    Dim LastRow As Long, arr As Variant, arr2 As Variant, RngList As Object, Val As String, fVisRow As Long, lVisRow As Long, x As Long: x = 750
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    arr2 = Range("D2:D" & LastRow).Value
    Set RngList = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1)
        If Not RngList.Exists(Val) Then
            RngList.Add Val, Nothing
            With Cells(1, 1).CurrentRegion
                .AutoFilter 4, Val
                Range("A" & i + 1).Resize([subtotal(103,B:B)] - 1) = x
                x = x + 1
            End With
        End If
    Next i
    Range("A1").AutoFilter
    arr = Range("A2:A" & LastRow).Resize(, 12).Value
    For i = 1 To UBound(arr, 1)
        Val = arr(i, 1)
        If WorksheetFunction.CountIf(Range("A:A"), Val) = 1 Then
            Range("U" & i + 1) = Val
            Range("V" & i + 1) = arr(i, 11)
        Else
            If Not RngList.Exists(Val) Then
                RngList.Add Val, Nothing
                Range("U" & i + 1) = Val
                With Cells(1, 1).CurrentRegion
                    .AutoFilter 1, Val
                    fVisRow = Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
                    lVisRow = Cells(Rows.Count, "A").End(xlUp).Row
                    Range("V" & i + 1) = arr(i, 11) & ";" & Join(Application.Transpose(Range("P" & fVisRow + 1 & ":P" & lVisRow).Value), ";")
                End With
            End If
        End If
        strVal = ""
    Next i
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[MikeJHHT]

This is extremely exciting, It looks great, I'm going to run several tests with new sets of sales orders, Thankyou for your help. This is my next goal, to learn VBA

 

[mumps]

You are very welcome.

 

[MikeJHHT]

So i added new data in Column B and D then hit the magic button and this came up in the debug window
Range("V" & i + 1) = arr(i, 11) & ";" & Join(Application.Transpose(Range("P" & fVisRow + 1 & ":P" & lVisRow).Value), ";")
here us the new data in B and D

magic test 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

Try:

Sub MAGIC()
    Application.ScreenUpdating = False
    Dim LastRow As Long, arr As Variant, arr2 As Variant, RngList As Object, Val As String, fVisRow As Long, lVisRow As Long, x As Long: x = 750
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    arr2 = Range("D2:D" & LastRow).Value
    Set RngList = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1)
        If Not RngList.Exists(Val) Then
            RngList.Add Val, Nothing
            With Cells(1, 1).CurrentRegion
                .AutoFilter 4, Val
                Range("A" & i + 1).Resize([subtotal(103,B:B)] - 1) = x
                x = x + 1
            End With
        End If
    Next i
    Range("A1").AutoFilter
    arr = Range("A2:A" & LastRow).Resize(, 12).Value
    For i = 1 To UBound(arr, 1)
        Val = arr(i, 1)
        If WorksheetFunction.CountIf(Range("A:A"), Val) = 1 Then
            Range("U" & i + 1) = Val
            Range("V" & i + 1) = arr(i, 11)
        Else
            If Not RngList.Exists(Val) Then
                RngList.Add Val, Nothing
                Range("U" & i + 1) = Val
                With Cells(1, 1).CurrentRegion
                    .AutoFilter 1, Val
                    fVisRow = Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
                    lVisRow = Cells(Rows.Count, "A").End(xlUp).Row
                    If lVisRow - fVisRow > 1 Then
                        Range("V" & i + 1) = arr(i, 11) & ";" & Join(Application.Transpose(Range("P" & fVisRow + 1 & ":P" & lVisRow).Value), ";")
                    Else
                        Range("V" & i + 1) = arr(i, 11) & ";" & Range("P" & fVisRow + 1).Value
                    End If
                End With
            End If
        End If
        strVal = ""
    Next i
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[MikeJHHT]

I ran into another issue, nothing to do with your macro, that is running perfectly, but im trying to nest an IF(ISNUMBER(SEARCH statement and keep getting errors.
=IF(ISNUMBER(SEARCH(D2,D1)),P2,K2,IF(ISNUMBER(SEARCH(D2,D3)),"",O2))
What im trying to say is
If D2 is the same as D1 then P2 otherwise K2, If D2 is the same as D3 leave no nothing, otherwise O2,

For a better longer explanation, at the END of each of my concats. There is an END, that is hurting me, the END can only be used at end of each truck number and no where in the middle, so i removed it from every concatenate and placed it in O2,
My thought it that this formula will determine when a new truck number will begin and place the END on the previous truck number

 

[mumps]

Unfortunately, working with formulas is not my strong suit. Could I suggest that you start a new thread describing your problem and include a screenshot of your sheet including the expected result (use XL:2BB).