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]

Thanks Markmzz

 

[markmzz]

Thanks Markmzz

Hi!

The number of records that you have is +/- 30k and the formulas isn't the way, but I think that the suggestion below can helps with the test of the VBA code.

Lets go:

1) Create the sheet Main.

[TABLE="class: grid, width: 650"]
<tbody>[TR]
[TD]HelpColumn[/TD]
[TD]Part number[/TD]
[TD]Total Quantity[/TD]
[TD]Number of occurrences[/TD]
[TD][/TD]
[TD]MySheets[/TD]
[/TR]
[TR]
[TD]1941-0334540[/TD]
[TD]1941-0334540[/TD]
[TD] 17 [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]03-19-18[/TD]
[/TR]
[TR]
[TD]201-2001203[/TD]
[TD]201-2001203[/TD]
[TD] 40 [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]03-12-18[/TD]
[/TR]
[TR]
[TD]811-0268667[/TD]
[TD]811-0268667[/TD]
[TD] 5 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]03-06-18[/TD]
[/TR]
[TR]
[TD]1941-0102607[/TD]
[TD]1941-0102607[/TD]
[TD] 5 [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1101-0261038[/TD]
[TD]1101-0261038[/TD]
[TD] 7 [/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1101-0268991[/TD]
[TD]1101-0268991[/TD]
[TD] 14 [/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243258[/TD]
[TD]1941-0243258[/TD]
[TD] 12 [/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243260[/TD]
[TD]1941-0243260[/TD]
[TD] 3 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243262[/TD]
[TD]1941-0243262[/TD]
[TD] 26 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243263[/TD]
[TD]1941-0243263[/TD]
[TD] 17 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243264[/TD]
[TD]1941-0243264[/TD]
[TD] 37 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243265[/TD]
[TD]1941-0243265[/TD]
[TD] 6 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1941-0243266[/TD]
[TD]1941-0243266[/TD]
[TD] 32 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]************
[/TD]
[TD]*************
[/TD]
[TD]*********************
[/TD]
[TD="align: right"]****************
[/TD]
[TD]***
[/TD]
[TD]*********[/TD]
[/TR]
</tbody>[/TABLE]

2) Create de names below:

MySheets - Refers to: =$F$2:$F$4

NRwds1 - Refers to:
=MMULT(--(ROW(INDIRECT("1:"&ROWS(MySheets)))>=TRANSPOSE(ROW(INDIRECT("1:"&ROWS(MySheets))))),
SUBTOTAL(3,OFFSET(INDIRECT("'"&MySheets&"'!A2"),0,0,900)))

NRwds0 - Refers to:
=MMULT(--(ROW(INDIRECT("1:"&ROWS(MySheets)))-1>=TRANSPOSE(ROW(INDIRECT("1:"&ROWS(MySheets))))),
SUBTOTAL(3,OFFSET(INDIRECT("'"&MySheets&"'!A2"),0,0,900)))

3) Put the formulas below in A2, B2, C2 and D2 and copy down:

=IF(ROWS(A$2:A2)>MAX(NRwds1),"",
INDEX(INDIRECT("'"&INDEX(MySheets,MATCH(1,--(NRwds1>=ROWS(A$2:A2)),0))&
"'!B2:A2000"),ROWS(A$2:A2)-LOOKUP(ROWS(B$2:B2)-1,NRwds0)))&""

=IFERROR(INDEX(A$2:A$2000,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$2000)+(A$2:A$2000=""),,),0)),"")

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!B2:B1000"),B2,INDIRECT("'"&MySheets&"'!F2:B1000")))

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B2:B2000"),B2))

Below is a link to your file with my formulas. Do some tests.

https://1drv.ms/x/s!AvFxmZVmmL9Sh3juKmbSoRHzRpDr

Markmzz

 

[MickG]

Try this:-
When your satisfied you can remove reference in code to sheet "Analyzed Data (2)". (Used for reference only)
[TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]https://app.box.com/s/26ay7heggvu7yyx61bhlzvmlfomfyhoz
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jeff_miller]

Hello MickG.
wondering if you are interested in looking at this again?

You had helped me with this issue in the past and it has been working well for me.
Now I am getting some bad data as a result of adding a worksheet to the workbook.

I added another sheet to the workbook to use for other calculations, now I am getting some results in the Part number column on the analyzed data sheet that do not look correct.
The name of the sheet that I added is called "Current Looker orders report".
It looks like the bad data was being pulled from column 2 in that worksheet, and compiling with the other data.

I went into the code that you created and edited it as follows.
In the line that said
For Each Ws In Worksheets
If Not Ws.Name = "Analyzed Data" And Not Ws.Name = "Analyzed Data (2)" Then

I changed it to the following

For Each Ws In Worksheets
If Not Ws.Name = "Analyzed Data" And Not Ws.Name = "Analyzed Data (2)" And Not Ws.Name = "Current Looker orders report" Then


That seemed to remove much of the bad data. But not all.
here is the current code




Option Explicit

Private Sub CommandButton1_Click()
Dim Dn As Range
Dim n As Long
Dim Dic As Object
Dim Ws As Worksheet
Dim Ray As Variant
Dim Q As Variant

Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare

For Each Ws In Worksheets
If Not Ws.Name = "Analyzed Data" And Not Ws.Name = "Analyzed Data (2)" And Not Ws.Name = "Current Looker orders report" Then
Ray = Ws.Cells(1).CurrentRegion
If IsArray(Ray) Then

For n = 2 To UBound(Ray, 1)
'If Ray(n, 2) = "1941-0334540" Then Stop

If Not Dic.Exists(Ray(n, 2)) Then
Dic.Add Ray(n, 2), Array(Ray(n, 2), Ray(n, 6), 1)
Else
Q = Dic(Ray(n, 2))
Q(1) = Q(1) + Ray(n, 6)
Q(2) = Q(2) + 1
Dic(Ray(n, 2)) = Q
End If
Next n
End If
End If
Next Ws
With 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))
With .Offset(-1).Resize(Dic.Count + 1, 3)
.Columns.AutoFit
.Borders.Weight = 2
End With
.Sort .Range("C2"), xlDescending
End With
End Sub

 

[MickG]

Have you got the same spelling of "sheet name" in sheet and code, as the worksheet names are case sensitive.

 

[jeff_miller]

Have you got the same spelling of "sheet name" in sheet and code, as the worksheet names are case sensitive.

Hello MickG,
I must not have saved the changes, because when I went back into the code they were not there. It works great now. Thanks again for the help!!!!!!

 

[MickG]

You're welcome