Looking to develop a Macro or Formula to Simplify Daily Process

**AbrahamRunning** · 10-07-2009, 04:21 PM

I get Lists of Client names daily that I need to sift through to simplify. I spend a good amount of time doing this daily. I would much appreciate some pointers on how to simplify the process. I initially sort by Term to find items that match the criteria 1030-1060, for the purpose of simplification only 1030 is included in the sample sheets. If any of that criteria match I add a '10' next to the product requested. After this I match up the products ordered by the clients and delete the extra rows. Is there anything that would do this automatically? Sample Files are attached. Thanks.

**JBeaucaire** · 10-07-2009, 05:05 PM

Why is it AB310, AB4 and not AB310, AB410?

And won't the Customer number be different for each person? That's easier to match by than trying to combine the firstname and lastname.

**AbrahamRunning** · 10-07-2009, 05:39 PM

Sorry about that, Only some of the products get the "10" . Yeah they do have individual Customer numbers I usually sort by address 1 to get the products going to each indiviual address, it also helps in pushing the PO Boxes towards the end of the list since those cannot ship UPS.

if d4 equals ab3 and z4 is between 1030-60 then add "10" after the AB3.

but then I don't know how to write the function for it.

**JBeaucaire** · 10-07-2009, 06:20 PM

So, just to emphasize, only product code AB3 gets turned into AB310? And even then only when column Z is 1030-1060?

BTW, you do know the column Z values are text, not numbers?

**JBeaucaire** · 10-07-2009, 06:29 PM

Give this a try on a copy of your sheet:

Option Explicit

Sub Condense()
'JBeaucaire (10/7/2009) Merge customer order lines
Dim LR As Long, i As Long, Trm As Long, Prod As Long, cNum As Long
Application.ScreenUpdating = False

Trm = Rows(1).Find("Term").Column
Prod = Rows(1).Find("Product").Column
cNum = Rows(1).Find("Customer Number").Column
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 3 Step -1
        If Cells(i, cNum) = Cells(i - 1, cNum) Then
            If Cells(i, Trm) >= "1030" And Cells(i, Trm) <= "1060" Then
                If Cells(i, Prod) = "AB3" Then Cells(i, Prod) = "AB310"
                If Cells(i - 1, Prod) = "AB3" Then Cells(i - 1, Prod) = "AB310"
            End If
            Cells(i - 1, Prod) = Cells(i - 1, Prod) & ", " & Cells(i, Prod)
            Rows(i).Delete xlShiftUp
        End If
    Next i
    
Application.ScreenUpdating = True
End Sub

**AbrahamRunning** · 10-07-2009, 07:23 PM

This looks beautiful. I haven't had a chance to test it yet. I will do so in the morning with the new list. How would I add more products that fall under the criteria for adding the "10"

would I add that here:

"If Cells(i, Prod) = "AB3" Then Cells(i, Prod) = "AB310""

**JBeaucaire** · 10-07-2009, 07:31 PM

Here, I've added an array of MyStrings at the top, just put in all the strings you want to add "10" to when they match the 1030-1060 Term.

Option Explicit

Sub Condense()
'JBeaucaire (10/7/2009) Merge customer order lines
Dim LR As Long, i As Long, Trm As Long, Prod As Long, cNum As Long
Dim s As Long, MyStrings
Application.ScreenUpdating = False

MyStrings = Array("AB3", "ZZ99")
Trm = Rows(1).Find("Term").Column
Prod = Rows(1).Find("Product").Column
cNum = Rows(1).Find("Customer Number").Column
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 3 Step -1
        If Cells(i, cNum) = Cells(i - 1, cNum) Then
            If Cells(i, Trm) >= "1030" And Cells(i, Trm) <= "1060" Then
                For s = 0 To UBound(MyStrings)
                    If Cells(i, Prod) = MyStrings(s) Then Cells(i, Prod) = MyStrings(s) & "10"
                    If Cells(i - 1, Prod) = MyStrings(s) Then Cells(i - 1, Prod) = MyStrings(s) & "10"
                Next s
            End If
            Cells(i - 1, Prod) = Cells(i - 1, Prod) & ", " & Cells(i, Prod)
            Rows(i).Delete xlShiftUp
        End If
    Next i
    
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub