Search and list multiple names

Where is this data coming from?

Hi Andeaas, hoe gaan dit daar in die baai? (I grew up in PE, my old stamping grounds )

I will take a look and see what see what I can come up with

My reason for asking where the data comes from, is because I was wondering if the data could be put into a better format/layout?

Excel works best on 2D tables, would it be possible to get/put this data into something like that?
Months across the top, items going down, with qty/item next to the item?

Sorry for not responding sooner

You said that was a shortened version of your data. Did you shorten the material types, or categories or what? It might help to see a bigger sample?

So you would only ever have a max of 50 rows, but a ton of columns?

If that is the case, would you consider transposing the table? (you have far more rows than columns, and we generally construct tables to grow downwards, rather than across)

What is the relationship btw categories and the items? (I have asked for extra help from the other senior members on this, too)

Originally Posted by ANDREAAS

Item is for instance "television 32" model1234" then it would be entered under the electronics category or if the item is "toothbrush" it'll fall under toiletries etc.

OK so there could be multiple items under 1 category?
(geting late here, will pick up again tomorrow, and sorry this is taking forever)

This is kinda what I am working towards, take a look and see if this is something you can work with?

I created a new sheet (Sheet2) which would replace your Data Entry sheet - simple, straightforward data entry method. There is some repeating (dates), but this can be overcome using a small formula)

I added some dummy data, and have started on your Usage sheet

For the average, you would use something like this...
=IF(B3="","",AVERAGEIFS(Sheet2!$C:$C,Sheet2!$B:$B,Usage!B$2,Sheet2!$D:$D,Usage!B3))

Hi,

See if this code can help you. It's using a Dictionary of Dictionaries.
It's not complete because it only compiles the February data but it should get you started. What it does is take the data for February and outputs it to a new sheet called Output.

By adding a loop you could go through each month in the "Data entry" sheet and compile the results.

Sub test()
    Dim ar
    Dim i As Long, j As Long
    Dim DicC, DicP, e, s
    Dim sMonth As String
    Dim m As Integer, n As Long

    Set DicC = CreateObject("Scripting.dictionary")

    ar = Sheets("Data Entry").Range("A1").Cells(1).CurrentRegion.Value
    sMonth = ar(1, 1)

    For i = 3 To UBound(ar, 1)
        If Not DicC.exists(ar(i, 1)) Then

            Set DicC.Item(ar(i, 1)) = CreateObject("Scripting.dictionary")

            For j = 4 To UBound(ar, 2) Step 2
                If ar(i, j) <> "" Then

                    With DicC(ar(i, 1))
                        If Not DicC(ar(i, 1)).exists(ar(i, j)) Then
                            DicC.Item(ar(i, 1))(ar(i, j)) = ar(i, j - 1)
                        Else
                            DicC.Item(ar(i, 1))(ar(i, j)) = DicC.Item(ar(i, 1))(ar(i, j)) + ar(i, j - 1)
                        End If
                    End With

                End If

            Next j
        End If
    Next i

    m = 1
    With Sheets("Output")
        .Range("A1") = sMonth
        For Each e In DicC
            If DicC(e).Count > 0 Then
                .Cells(2, m) = e
                .Cells(2, m + 1) = "Amount"
                n = 3
                For Each s In DicC(e)
                    .Cells(n, m) = s
                    .Cells(n, m + 1) = DicC(e)(s)
                    n = n + 1
                Next s
                m = m + 2
            End If
        Next e
    End With

End Sub