Looking for most common values over multiple worksheets

[jeff_miller]

I am working in Microsoft Excel 2016

I have 26 sheets of data in a workbook representing weekly inventory part numbers and quantity (along with a bunch of other data regarding the parts).
I am looking to find the highest volume part numbers and analyze trending part numbers over time. I am not very familiar with pivot tables or VB code.
Note not all of the same part numbers would be on each sheet week to week


example

Sheet "03-19-18"

Part number Quantity
701-528 100
701-555 10,000
1259827 15
1241-158887 200


Sheet "03-12-18"

Part number Quantity
705-539 1,500
701-555 10,000
1259854 100
1241-158887 200


Sheet "03-06-18"

Part number Quantity
707-933 17
701-555 10,000
1259827 15
1241-158895 2,500



Looking for results in an separate sheet showing the following data sorted by occurrences/Total Quantity/Part number

Sheet "Analyzed data"

Part number Total Quantity Number of occurrences
701-555 30,000 3
1241-158887 400 2
1259827 30 2
1241-158895 2,500 1
705-539 1,500 1
1259854 100 1
701-528 100 1
707-933 17 1


Any help is appreciated!!

 

[jeff_miller]

sorry, it will not let me post an image of the spreadsheet or an attachment.

 

[jeff_miller]

I assume this will need some VB code correct? What I am mainly looking for is some way to look up all of the part numbers on all of the other sheets and report them on a separate sheet. I can use VLOOKUP to get the quantities after that and COUNTIF to get the number of occurrences.

 

[markmzz]

Hi Jeff,

Maybe the suggestion below can helps:

1) Create the name MySheets for the range E2:E4 (all yours text date sheets - not real dates).

2) Put the formulas below in B2 and C2 and copy down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A2:A9"),A2,INDIRECT("'"&MySheets&"'!B2:B9")))

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A9"),A2))

[TABLE="class: grid, width: 509"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part number[/TD]
[TD]Total Quantity[/TD]
[TD]Number of occurrences[/TD]
[TD][/TD]
[TD]MySheets[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]701-555[/TD]
[TD] 30.000 [/TD]
[TD]3[/TD]
[TD][/TD]
[TD]03-19-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1241-158887[/TD]
[TD] 400 [/TD]
[TD]2[/TD]
[TD][/TD]
[TD]03-12-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1259827[/TD]
[TD] 30 [/TD]
[TD]2[/TD]
[TD][/TD]
[TD]03-06-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1241-158895[/TD]
[TD] 2.500 [/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]705-539[/TD]
[TD] 1.500 [/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1259854[/TD]
[TD] 100 [/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]701-528[/TD]
[TD] 100 [/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]707-933[/TD]
[TD] 17 [/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]************[/TD]
[TD]**************[/TD]
[TD]**********************[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[MickG]

Try this:-
Results on sheet "Analyzed Data", starting "A1"

[COLOR=navy]Sub[/COLOR] MG24Mar44
[COLOR=navy]    Dim[/COLOR] Rng     [COLOR=navy]As[/COLOR] Range
    [COLOR=navy]Dim[/COLOR] Dn      [COLOR=navy]As[/COLOR] Range
    [COLOR=navy]Dim[/COLOR] n        [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
    [COLOR=navy]Dim[/COLOR] Dic      [COLOR=navy]As[/COLOR] Object
    [COLOR=navy]Dim[/COLOR] Ws      [COLOR=navy]As[/COLOR] Worksheet
    [COLOR=navy]Dim[/COLOR] Ray     [COLOR=navy]As[/COLOR] Variant
    [COLOR=navy]Dim[/COLOR] Q        [COLOR=navy]As[/COLOR] Variant
    
    [COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ws [COLOR=navy]In[/COLOR] Worksheets
        [COLOR=navy]If[/COLOR] Not Ws.Name = "Analyzed Data" [COLOR=navy]Then[/COLOR]
            Ray = Ws.Cells(1).CurrentRegion
                [COLOR=navy]If[/COLOR] IsArray(Ray) [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]For[/COLOR] n = 2 To UBound(Ray, 1)
                        [COLOR=navy]If[/COLOR] Not Dic.Exists(Ray(n, 1)) [COLOR=navy]Then[/COLOR]
                            Dic.Add Ray(n, 1), Array(Ray(n, 1), Ray(n, 2), 1)
                        [COLOR=navy]Else[/COLOR]
                            Q = Dic(Ray(n, 1))
                                Q(1) = Q(1) + Ray(n, 2)
                                Q(2) = Q(2) + 1
                            Dic(Ray(n, 1)) = Q
                        [COLOR=navy]End[/COLOR] If
                    [COLOR=navy]Next[/COLOR] n
                [COLOR=navy]End[/COLOR] If
         [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ws
[COLOR=navy]With[/COLOR] Sheets("Analyzed Data").Range("A2").Resize(Dic.Count, 3)
    .Parent.Range("A1").Resize(, 3).Value = Array("Part number", "Total Quantity", "Number of occurrences")
    .Value = Application.Transpose(Application.Transpose(Dic.items))
          [COLOR=navy]With[/COLOR] .Offset(-1).Resize(Dic.Count + 1, 3)
            .Columns.AutoFit
            .Borders.Weight = 2
        [COLOR=navy]End[/COLOR] With
    .Sort .Range("C2"), xlDescending
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[jeff_miller]

Thanks so much Mick! I think we are closer to solving this.


I used this code in the sample workbook I created and it worked perfect.

when I put it into the actual workbook the results were not the same.

Under the part number column it gave me sequential numbers
1
2
3
4
5

In the Total quantity it puts a bunch of different part numbers

In the number of occurrences it places a number (26 for the first row) that repeats may times down the page and that number of occurrences also seems to be how many different part numbers are in the total quantity column.

In the example below the part number 1941-0334540 repeats a few times and then a bunch of other part numbers (26 in total) show up in the total quantity column. I checked the workbook, and 1941-0334540 only shows up in 14 of the dated worksheets.

I could share the workbook with you if I knew how without posting the data publicly here.

below is a copy and past of the first two rows of results.


Part number Total Quantity Number of occurrences
1 1941-03345401941-0334540650-20071801941-0334540650-2007179201-2001203650-02757611941-03321041941-02751371941-0332104650-02757611941-0334540650-0338082650-0275761650-02757611941-03345401941-03345401941-03345401941-03321051941-03345401941-03345401941-03345401941-0334540811-0268667SV-1994441941-0125429 26
2 201-2001203650-20071791101-2005936201-2001203650-2007180502-02663671941-03345401941-0332105201-20012041941-03345401101-20059361101-20059361101-2005936502-0266368650-0334509502-0266368650-0334509650-0334509811-0268619650-0338082650-0338082811-02686191941-03321051941-03156741941-0259561502-0266368 26

 

[jeff_miller]

Thanks Markmzz,

wouldn't I need to know the part numbers to do this?

 

[markmzz]

Thanks Markmzz,

wouldn't I need to know the part numbers to do this?

I think I didn't understand correctly what you want. For my suggestion to work we need the part numbers.

So maybe the MickG's suggestion is the best way.

Markmzz

 

[MickG]

You can attach a File using "Box.com" or similar.

 

[jeff_miller]

MickG

https://www.dropbox.com/s/6cbnc4bspgq8wx7/Bulk historical part#s.xlsx?dl=0