Creating a Summary Worksheet

[rjenk]

I have a spreadsheet that has two tabs, one is a Summary while the other is a WORK LOAD tab.

The WORK LOAD tab has an Item #, Item Type, Location, and Notes columns. The Item # column has various two to four digit item numbers, they Item Type has up to ten types, and the Location tab is either Shop or Vendor.

On the Summary tab, I am trying to summarize by Location, by Item Type (cell A) and then just list the Item #s separated by a comma (cell B).

Example would be:

Shop Items

Truck | 31, 46
Car | 2415, 2788
Trailer | 4183, 4188

Vendor Items:

Truck | 18, 24, 88
Car | 2111, 2218, 2844
Trailer | 4000

It is basically if Location=Shop and Item=Truck then put Item #'s in a cell separated by a comma...repeat for other combinations.

Hope that makes sense. I have a small sample spreadsheet available if that would help.

Thanks in advance for any assistance.

Ray

 

[Fluff]

Could you post an example of what your data is like & what you want.
There are some tools available here that will allow you to do that https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[rjenk]

Excel Workbook
	A	B	C	D
1	Worklist
2
3	EquiptmentNumber	EquiptmentType	Location	Notes
4	24	Truck	Shop
5	31	Truck	Vendor
6	46	Truck	Vendor
7	99	Truck	Shop
8	1121	Lift	Shop
9	1233	Lift	Shop
10	2415	Car	Vendor
11	2655	Car	Shop
12	2788	Car	Vendor
13	4183	Trailer	Vendor
14	4188	Trailer	Vendor
15	4199	Trailer	Shop
WORK LOAD

 

[rjenk]

Excel Workbook
	A	B
1	Summary
2
3	Unavailable (Shop Repair)
4	Truck
5	Lift
6	Car
7	Trailer
8
9	Unavailable (Vendor Repair)
10	Truck
11	Lift
12	Car
13	Trailer
14
15
16	Example Format Below
17
18	Unavailable (Vendor Repair)
19	Truck	31, 46
20	Lift
21	Car	2415, 2788
22	Trailer	4183, 4188
23
Summary

 

[Fluff]

Thanks for the samples, are you looking for formulae, or VBA, or don't you mind?

Also, for future reference there is a test board here https://www.mrexcel.com/forum/test-here/
where you can test things out to your hearts content, without cluttering up the main board.
I've removed your 1st two attempts.

 

[rjenk]

Thanks for removing the errors.

I had been trying to do it with formulae, but not opposed to VBA.

 

[Fluff]

How about

Sub CreateSummary()

   Dim Cl As Range
   Dim Dic(1 To 2) As Object
   Dim Sws As Worksheet
   Dim Dws As Worksheet
   Dim i As Long
   Dim Rw As Long
   
   Set Sws = Sheets("Work load")
   Set Dws = Sheets("Summary")
   For i = 1 To 2
      Set Dic(i) = CreateObject("scripting.dictionary")
      Dic(i).comparemode = vbTextCompare
   Next i
   For Each Cl In Sws.Range("B4", Sws.Range("B" & Rows.Count).End(xlUp))
      If Cl.Offset(, 1).Value = "Shop" Then
         i = 1
      ElseIf Cl.Offset(, 1).Value = "Vendor" Then
         i = 2
      End If
      If Not Dic(i).exists(Cl.Value) Then
         Dic(i).Add Cl.Value, Cl.Offset(, -1).Value
      Else
         Dic(i).Item(Cl.Value) = Dic(i).Item(Cl.Value) & ", " & Cl.Offset(, -1).Value
      End If
   Next Cl
   For Rw = 4 To Dws.Range("A4").End(xlDown).Row
      Dws.Range("B" & Rw).Value = Dic(1).Item(Dws.Range("A" & Rw).Value)
   Next Rw
   For Rw = 10 To Dws.Range("A10").End(xlDown).Row
      Dws.Range("B" & Rw).Value = Dic(2).Item(Dws.Range("A" & Rw).Value)
   Next Rw

End Sub

This should work on the sample you've shown, but you'll probably need to change the values in red, for your real sheet.

EDIT: I've changed the code to make it case insensitive

 

[rjenk]

Thanks so much, that worked perfect. I really appreciate it.

In my actual spreadsheet, the Shop/Vendor column is K and the summary sections start on A21 (for Shop) and A29 (for Vendor). Can you instruct me on the changes to the script based on those differences? I have been tinkering around, but have not quite figured it out.

 

[rjenk]

Figured it out and have it working...much, much thanks! Had the change the offset to get to column K and change starting rows for the two sections.

Thanks again!

 

[Fluff]

Glad you figured it out & thanks for the feedback.

It would have helped, If I had remembered to add the values in red after posting the modified!