Database search and manipulation

lockarde

lockarde

Board Regular
Joined
Oct 23, 2016
Messages
77
Good morning all,

I've hit a bit of a road block with some reporting and database automation and I was hoping one of you could help! I have a databse of product that I'm using to call certain data. For ease of reading the report I'd like to insert rows that have the summary of the product, then move on to the next product. Right now it's all manual, inserting rows, summing the cells above. For example:

SKUProduct Name1st qtr2nd qtr3rd qtr4th qtrSKUProduct Name1st qtr2nd qtr3rd qtr4th qtr
1234567891011Aloha T-Shirt (small, red)3132791234567891011Aloha T-Shirt (small, red)313279
1234567891012Aloha T-Shirt (medium, red)3132791234567891012Aloha T-Shirt (medium, red)313279
1234567891013Aloha T-Shirt (Large, red)3132791234567891013Aloha T-Shirt (Large, red)313279
1234567891014Aloha T-Shirt (Xlarge, red)3132791234567891014Aloha T-Shirt (Xlarge, red)313279
1234567891015Aloha T-Shirt (XXL, red)3132791234567891015Aloha T-Shirt (XXL, red)313279
1110987654312Rain Jacket (small, red)313279Aloha T-ShirtTotal156513545
1110987654313Rain Jacket (medium, red)313279To This ->
1110987654314Rain Jacket (Large, red)3132791110987654312Rain Jacket (small, red)313279
1110987654315Rain Jacket (Xlarge, red)3132791110987654313Rain Jacket (medium, red)313279
1110987654316Rain Jacket (XXL, red)3132791110987654314Rain Jacket (Large, red)313279
1110987654315Rain Jacket (Xlarge, red)313279
1110987654316Rain Jacket (XXL, red)313279
Rain JacketTotal156513545

<tbody>
</tbody>

I'm thinking it might be easiest to just have a set number of rows, regardless of the number of products, there might be empty rows, but it'd be easy to just "copy the SKUs here, every 15 ros, insert name, sum rows here". Normally this wouldn't be an issue, but the extra text (size,color) that is included in the product name column throws me off with a search and copy.

I appreciate any insight you guys might have!

lockarde
 
Sort by date Sort by votes
My apologies. Although that change was necessary, it's still not going to work. Other changes need to be made. I have to go out for a few hours. I will have another look at it as soon as I can and get back to you.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try this macro. It is based on the first 2 words in each product name since that is the only pattern that allows comparison of the names. If any product name doesn't fit this pattern, your results won't be completely accurate.
Code: 
Sub InsertRowsandSum2()
    Application.ScreenUpdating = False
    Dim LastRow As Long, product As Range, rng As Range, val As String, RngList As Object, foundVal As Range, item As Variant, x As Long: x = 2
    Set RngList = CreateObject("Scripting.Dictionary")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rng = Range("B3:B" & LastRow)
    For Each product In rng
        val = Mid(product, 1, Excel.WorksheetFunction.Find(" ", product, WorksheetFunction.Find(" ", product) + 1))
        If Not RngList.Exists(val) Then
            RngList.Add val, Nothing
        End If
    Next product
    For Each item In RngList
        Set foundVal = rng.Find(item, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
        Rows(foundVal.Row + 1).Insert
        Range("C" & foundVal.Row + 1).Formula = "=SUM(C" & x & ":C" & foundVal.Row & ")"
        Range("D" & foundVal.Row + 1).Formula = "=sum(D" & x & ":D" & foundVal.Row & ")"
        Range("E" & foundVal.Row + 1).Formula = "=sum(E" & x & ":E" & foundVal.Row & ")"
        Range("F" & foundVal.Row + 1).Formula = "=sum(F" & x & ":F" & foundVal.Row & ")"
        Range("G" & foundVal.Row + 1).Formula = "=sum(G" & x & ":G" & foundVal.Row & ")"
        Range("H" & foundVal.Row + 1).Formula = "=sum(H" & x & ":H" & foundVal.Row & ")"
        x = foundVal.Row + 2
    Next item
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try this macro. It is based on the first 2 words in each product name since that is the only pattern that allows comparison of the names. If any product name doesn't fit this pattern, your results won't be completely accurate.
Code: 
Sub InsertRowsandSum2()
    Application.ScreenUpdating = False
    Dim LastRow As Long, product As Range, rng As Range, val As String, RngList As Object, foundVal As Range, item As Variant, x As Long: x = 2
    Set RngList = CreateObject("Scripting.Dictionary")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rng = Range("B3:B" & LastRow)
    For Each product In rng
        val = Mid(product, 1, Excel.WorksheetFunction.Find(" ", product, WorksheetFunction.Find(" ", product) + 1))
        If Not RngList.Exists(val) Then
            RngList.Add val, Nothing
        End If
    Next product
    For Each item In RngList
        Set foundVal = rng.Find(item, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
        Rows(foundVal.Row + 1).Insert
        Range("C" & foundVal.Row + 1).Formula = "=SUM(C" & x & ":C" & foundVal.Row & ")"
        Range("D" & foundVal.Row + 1).Formula = "=sum(D" & x & ":D" & foundVal.Row & ")"
        Range("E" & foundVal.Row + 1).Formula = "=sum(E" & x & ":E" & foundVal.Row & ")"
        Range("F" & foundVal.Row + 1).Formula = "=sum(F" & x & ":F" & foundVal.Row & ")"
        Range("G" & foundVal.Row + 1).Formula = "=sum(G" & x & ":G" & foundVal.Row & ")"
        Range("H" & foundVal.Row + 1).Formula = "=sum(H" & x & ":H" & foundVal.Row & ")"
        x = foundVal.Row + 2
    Next item
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub
Click to expand...

mumps, this works PERFECTLY!! Thanks so much for your help on this. I should have no trouble incorporating this into the main report I'm working on.
 
Upvote 0
mumps said:
My pleasure. :)
Click to expand...

mumps, I've got this thing cruisin! However, I've now been asked to include sales data for 2016 + 2017, so that adds 10 columns to the SUM portion of this code. I was wondering if there is a simple way to loop through all of that instead of having to type out "C" through "R"? I can for sure copy + paste and edit, just seems inefficient... If it isn't easy just tell me and don't worry about it!
 
Upvote 0
Can you upload a copy of your file as you did before?
 
Upvote 0
I don't see the data for 2016 + 2017.
 
Upvote 0
Try:
Code: 
'Option Explicit
Sub InsertRowsandSum2()
    Application.ScreenUpdating = False
    Dim LastRow As Long, product As Range, rng As Range, val As String, RngList As Object, foundVal As Range, item As Variant, x As Long: x = 3
    Dim i As Integer, j As Integer
    Set RngList = CreateObject("Scripting.Dictionary")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rng = Range("B3:B" & LastRow)
    For Each product In rng
        val = Mid(product, 1, Excel.WorksheetFunction.Find(" ", product, WorksheetFunction.Find(" ", product) + 1))
        If Not RngList.Exists(val) Then
            RngList.Add val, Nothing
        End If
    Next product
    For Each item In RngList
        Set foundVal = rng.Find(item, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
        'If Range(Cells(foundVal.Row + 1)).MergeCells = False Then
        
        i = 1
        Do While i < 4
            Rows(foundVal.Row + i).Insert
            i = i + 1
        Loop
        Range(Cells(foundVal.Row + 1, 1), Cells(foundVal.Row + 3, 2)).Merge
        Range(Cells(foundVal.Row + 1, 3), Cells(foundVal.Row + 3, 3)).Merge
        Cells(foundVal.Row + 1, 3).Interior.Color = RGB(244, 176, 132)
        Cells(foundVal.Row + 1, 3).HorizontalAlignment = xlCenter
        Cells(foundVal.Row + 1, 3).VerticalAlignment = xlCenter
        i = 4
        Do While i < 9
            
            Cells(2, i).Copy 'with final sheet inttegration make this 3,i
            Cells(foundVal.Row + 1, i).PasteSpecial Paste:=xlPasteValues
            Cells(foundVal.Row + 1, i).PasteSpecial Paste:=xlPasteFormats
            Range(Cells(foundVal.Row + 1, i), Cells(foundVal.Row + 2, i)).Merge
            Range(Cells(foundVal.Row + 1, i), Cells(foundVal.Row + 1, i)).Borders(xlEdgeBottom).LineStyle _
                = xlDouble
            Range(Cells(foundVal.Row + 3, i), Cells(foundVal.Row + 3, i)).Borders(xlEdgeTop).LineStyle _
                = xlDouble
            Range(Cells(foundVal.Row + 1, i), Cells(foundVal.Row + 3, i)). _
                Interior.Color = RGB(169, 208, 142)
            
            i = i + 1
        Loop
        Cells(foundVal.Row + 1, 1).Value = item & "Total Sales"
        Cells(foundVal.Row + 1, 2).HorizontalAlignment = xlRight
        Cells(foundVal.Row + 1, 2).VerticalAlignment = xlCenter
        'i = 3
        'j = 1
        
        'Do while
        Range("C" & foundVal.Row + 1).Formula = "=SUM(C" & x & ":C" & foundVal.Row & ")"
        Range("D" & foundVal.Row + 3).Formula = "=sum(D" & x & ":D" & foundVal.Row & ")"
        Range("D" & foundVal.Row + 3).AutoFill Destination:=Range("D" & foundVal.Row + 3 & ":R" & foundVal.Row + 3)
        x = foundVal.Row + 4
    Next item
    RngList.RemoveAll
    Application.ScreenUpdating = True
        'ElseIf Range(Cell(foundVal.Row + 1)).MergeCells = True Then
           ' i = 1
           ' Do While i < 4
            '    Rows(foundVal + i).Delete
            '    i = i + 1
           ' Loop
       ' End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Mailing Merge that includes multiple rows of data on one page
Replies
0
Views
259
mattmcclements
M
N
  • Solved
Dependant Drop Down Lists that Dynamically Change
Replies
3
Views
443
Fluff
Fluff
k3yn0t3
  • Question
Simple Macro to Copy/Paste Cells onto Another Sheet
Replies
2
Views
219
kevin9999
K
C
  • Question
Adding a new row based on comparing two cells
Replies
1
Views
311
tj4242
T
N
  • Solved
Create a drop down menu that relies on the first drop down menu selection to drive a price result.
Replies
8
Views
409
Eric W
Eric W

Forum statistics

Threads
1,215,421
Messages
6,124,806
Members
449,191
Latest member
rscraig11

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top