Compiling Data From A Filter Worksheet

[Ark68]

I have this filtered data (autofilter) from my worksheet.

2021-2022 Data.xlsx
	A	B										L		N	O
1	RID	Date										Acct		Salt	Sand
114	44549W001	2021-12-19										CW		0.5
115	44549W002	2021-12-19										404		MIN
116	44549W003	2021-12-19										AMCC		0.25
117	44549W004	2021-12-19										WP		0.25
118	44549W005	2021-12-19										CCGG		MIN
119	44549W006	2021-12-19										CW		0.25
120	44549W007	2021-12-19										CW		0.25
121	44549W008	2021-12-19										404		MIN
122	44549W009	2021-12-19										AMCC		MIN
123	44549W010	2021-12-19										MSCC		MIN
124	44549W011	2021-12-19										WMRC		0.25
125	44549W012	2021-12-19										WP		0.25
126	44549W013	2021-12-19										HSP		MIN
127	44549W014	2021-12-19										SC		MIN
128	44549W015	2021-12-19										CW		0.25
129	44549W016	2021-12-19										BIA		MIN
130	44549W017	2021-12-19										ARC		MIN
131	44549W018	2021-12-19										CCGG		MIN
132	44549W019	2021-12-19										WMRC		MIN
133	44549W020	2021-12-19										WTS		MIN
134	44549W021	2021-12-19										SP		0.25
135	44549W022	2021-12-19										WP		MIN
136	44550W001	2021-12-20										CW		0.25
137	44550W002	2021-12-20										SP		MIN
138	44550W003	2021-12-20										CW		0.25
139	44550W004	2021-12-20										CW		0.5
140	44550W005	2021-12-20										CW		0.5
141	44550W006	2021-12-20										SP		0.25
142	44551W001	2021-12-21										CW		0.25
143	44551W002	2021-12-21										BIA		MIN
144	44551W003	2021-12-21										ARC		MIN
145	44551W004	2021-12-21										CCGG		MIN
146	44551W005	2021-12-21										WTS		MIN
147	44551W006	2021-12-21										SP		MIN
148	44551W007	2021-12-21										CW		0.25
149	44551W008	2021-12-21										SP		0.25
150	44551W009	2021-12-21										CW		0.25
151	44551W010	2021-12-21										SP		0.25
152	44551W011	2021-12-21										CW		0.25
153	44551W012	2021-12-21										CW		0.25
154	44551W013	2021-12-21										SP		0.25
155	44552W001	2021-12-22										CW		0.25
156	44552W002	2021-12-22										SP		0.25
157	44552W003	2021-12-22										CW		MIN
158	44552W004	2021-12-22										CW		0.25
159	44552W005	2021-12-22										SP		MIN
160	44552W006	2021-12-22										CW		MIN
161	44552W007	2021-12-22										SP		0.25
162	44552W008	2021-12-22										SP		0.5
163	44552W009	2021-12-22										CW		MIN
164	44552W010	2021-12-22										ARC		MIN
165	44552W011	2021-12-22										UPP		MIN
166	44552W012	2021-12-22										WTS		MIN
167	44552W013	2021-12-22										SP		0.25
168	44553W001	2021-12-23										CW		0.25
169	44553W002	2021-12-23										SP		MIN
170	44553W003	2021-12-23										CW		0.25
171	44553W004	2021-12-23										CW		0.25
172	44553W005	2021-12-23										SP		0.25
173	44553W006	2021-12-23										CW		0.5
174	44553W007	2021-12-23										SP		0.5
175	44554W001	2021-12-24										CW
176	44554W002	2021-12-24										CW
177	44554W003	2021-12-24										CW
178	44554W004	2021-12-24										CW
OPS

I am looking for a VBA solution to get a list of unique values from Column L (Acct). This compiled list will be placed in worksheet "WEEKS", at cell A32.
Then, for each unique value from column L (acct), get the sum of salt and the sum of sand, and the count of the number of occurrences of "MIN", and place the values in "WEEKS", at cells B32, C32 and D32 respectively.

2021-2022 Data.xlsx
	A	B	C	D
31		Sum Salt	Sum Sand	Count "MIN"
32	404			2
33	AMCC	0.250		1
34	ARC			3
35	BIA			2
36	CCGG			3
37	CW	5.750		3
38	HSP			1
39	MSCC			1
40	SC			1
41	SP	3.250		4
42	UPP			1
43	WMRC	0.250		1
44	WP	0.500		1
45	WTS			3
WEEKS

 

[kevin9999]

What is the name of the worksheet with the filtered data?

 

[DanteAmor]

Try this:

Sub CompilingData()
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("OPS").Range("L2:O" & Sheets("OPS").Range("L" & Rows.Count).End(3).Row).SpecialCells(xlCellTypeVisible).Value
  ReDim b(1 To UBound(a, 1), 1 To 4)
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then dic(a(i, 1)) = dic.Count + 1
    j = dic(a(i, 1))
    b(j, 1) = a(i, 1)
    If a(i, 3) <> "MIN" Then b(j, 2) = b(j, 2) + a(i, 3) Else b(j, 4) = b(j, 4) + 1
    If a(i, 4) <> "" Then b(j, 3) = b(j, 3) + a(i, 4)
  Next
  With Sheets("WEEKS")
    .Range("A32").Resize(dic.Count, 4).Value = b
    .Range("A31").Resize(dic.Count + 1, 4).Sort Key1:=.Range("A31"), Order1:=xlAscending, Header:=xlYes
  End With
End Sub

 

[Ark68]

Hello Dante. Thank you so much for spending your time sharing your skill with me. It works wonderfully.
I'm wondering if you have a moment to explain to me how it works.

From what I can gather, "dic" is a "custom dictionary" of sorts that holds the unique values found in column L as they are encountered.
"a" is the filtered column (L) of data? Not the usual way I see a range defined if that is what it is. I don't know what kind of value 'a' holds if any.
Then it looks like an array is created (b?). This is a new concept for me so 'ReDim b(1 To UBound(a, 1), 1 To 4)' is a bit elusive to me.
What happens in the loop I have no idea. This is where all the analysis happens, but I can't understand the references. (likely because they are related to the array?) Seeing If/Then/Else on in one line is also pretty cool.
FInally, the compiled data is presented on the new worksheet. The resize is throwing me off.

 

[DanteAmor]

From what I can gather, "dic" is a "custom dictionary" of sorts that holds the unique values found in column L as they are encountered.

It is right

In the array"a" the data of "L" are loaded but only the visible ones.

Then it looks like an array is created (b?)

In array "b" the result will be stored.

In each key of the dictionary you can store a data, in this case the data that I am storing is the number of unique data, which I use later as the row number in the array "b".
So if I have 15 records, with 2 keys, in row 1 of array "b" the data of the first key goes, in row 2 of array "b" the data of the second key goes.

j = dic(a(i, 1))

So, according to the previous explanation, with the previous line the row number of the key is obtained.
In that line the calculations are put: sums or counts.

Finally, in the WEEKS sheet the array is downloaded.

 

[Ark68]

Thank you once again Dante for providing that explanation. I'll have to digest it a bit more and maybe play around with it a bit more to get a better working knowledge of it. It's a good start though!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback

 

[Ark68]

I have run into a situation where I am needing to add to the code provided above. Unfortunately, I still don't have enough understanding of the array aspect to make my changes work. I've only managed to mess things up.

I have columns for both sand and salt. Each one can have the value "MIN" in them. The code Dante provided for me gives me the sum of each salt and sand and the count of "MIN" for what I believe to be from both salt and sand columns(?)

In addition to the sums of both salt and sand columns, I need the counts of "MIN" for both salt and sand as seperate values.

Destinations columns B will be the sum of salt values; C wioll be the count of "MIN" in the salt column; D the sum of sand values; and E the count of "MIN" in the sand column.

 

[Ark68]

I'm put this last question to a new thread ... find it here.