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:

[TABLE="class: grid, width: 1162"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Product Name[/TD]
[TD]1st qtr[/TD]
[TD]2nd qtr[/TD]
[TD]3rd qtr[/TD]
[TD]4th qtr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU[/TD]
[TD]Product Name[/TD]
[TD]1st qtr[/TD]
[TD]2nd qtr[/TD]
[TD]3rd qtr[/TD]
[TD]4th qtr[/TD]
[/TR]
[TR]
[TD="align: right"]1234567891011[/TD]
[TD]Aloha T-Shirt (small, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234567891011[/TD]
[TD]Aloha T-Shirt (small, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1234567891012[/TD]
[TD]Aloha T-Shirt (medium, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234567891012[/TD]
[TD]Aloha T-Shirt (medium, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1234567891013[/TD]
[TD]Aloha T-Shirt (Large, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234567891013[/TD]
[TD]Aloha T-Shirt (Large, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1234567891014[/TD]
[TD]Aloha T-Shirt (Xlarge, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234567891014[/TD]
[TD]Aloha T-Shirt (Xlarge, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1234567891015[/TD]
[TD]Aloha T-Shirt (XXL, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234567891015[/TD]
[TD]Aloha T-Shirt (XXL, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1110987654312[/TD]
[TD]Rain Jacket (small, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Aloha T-Shirt[/TD]
[TD]Total[/TD]
[TD]15[/TD]
[TD]65[/TD]
[TD]135[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD="align: right"]1110987654313[/TD]
[TD]Rain Jacket (medium, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]To This ->[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1110987654314[/TD]
[TD]Rain Jacket (Large, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1110987654312[/TD]
[TD]Rain Jacket (small, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1110987654315[/TD]
[TD]Rain Jacket (Xlarge, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1110987654313[/TD]
[TD]Rain Jacket (medium, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1110987654316[/TD]
[TD]Rain Jacket (XXL, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1110987654314[/TD]
[TD]Rain Jacket (Large, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1110987654315[/TD]
[TD]Rain Jacket (Xlarge, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1110987654316[/TD]
[TD]Rain Jacket (XXL, red)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rain Jacket[/TD]
[TD]Total[/TD]
[TD]15[/TD]
[TD]65[/TD]
[TD]135[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
215
frewert
F
M
  • Question Question
Mailing Merge that includes multiple rows of data on one page
Replies
0
Views
345
mattmcclements
M
N
  • Solved
Dependant Drop Down Lists that Dynamically Change
Replies
3
Views
531
Fluff
Fluff
k3yn0t3
  • Question Question
Simple Macro to Copy/Paste Cells onto Another Sheet
Replies
2
Views
364
kevin9999
K
C
  • Question Question
Adding a new row based on comparing two cells
Replies
1
Views
400
tj4242
T

Forum statistics

Threads
1,223,914
Messages
6,175,351
Members
452,638
Latest member
Oluwabukunmi

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