I am trying to create a macro that will categorize data in one column into multiple columns based on the item type. The data I am trying to categorize is a list of contracts with meta-data on the items in the contract.
我正在尝试创建一个宏,它将根据项类型将一列中的数据分类为多个列。我试图分类的数据是合同列表,其中包含合同中项目的元数据。
The raw data looks like this:
原始数据如下所示:
Contract No Contract Name Item Type Item Description 111111 Chocolate Supplies POTS 5" 111111 Chocolate Supplies POTS 10" 111111 Chocolate Supplies POTS 15" 111111 Chocolate Supplies PANS 5" 111111 Chocolate Supplies PANS 10" 111111 Chocolate Supplies PANS 15" 111111 Chocolate Supplies KNIVES Paring knife 111111 Chocolate Supplies SILVERWARE Salad fork 111111 Chocolate Supplies SILVERWARE Dinner fork 111111 Chocolate Supplies SILVERWARE Dessert fork 111111 Chocolate Supplies SILVERWARE Dessert spoon 111111 Chocolate Supplies SILVERWARE Soup spoon 22222 Soups and Salads Order POTS 10" 22222 Soups and Salads Order POTS 15" 22222 Soups and Salads Order PANS 15" 22222 Soups and Salads Order KNIVES Butter knife 22222 Soups and Salads Order KNIVES Bread knife 22222 Soups and Salads Order KNIVES Paring knife 22222 Soups and Salads Order SILVERWARE Soup spoon
The final data needs to look like this (edited to include image):
最终数据需要如下所示(编辑为包含图像):
Contract Contract Name POTS PANS KNIVES SILVERWARE
111111 Chocolate Supplies 5" 5" Paring knife Salad fork
10" 10" Dinner fork
15" 15" Dessert fork
Dessert spoon
Soup spoon
22222 Soups and Salads Order 10" 15" Butter knife Soup spoon
15" Bread knife
Paring knife
# What I've tried so far #
The current crude solution I am using is to:
- Run the query
- Paste the data into excel
- Create a pivot
- Use a series of count, offset and indirect formulas to reorganize the data as needed
- Since the above process leaves empty rows between each section of contracts, I copy-paste the data into a new worksheet, put an Autofilter and remove the blank rows
... and voila, that's the final report.
#到目前为止我尝试过的#我当前使用的原始解决方案是: - 运行查询 - 将数据粘贴到Excel中 - 创建一个数据库 - 使用一系列计数,偏移和间接公式来根据需要重新组织数据 - 由于上面的过程在每个合同部分之间留下空行,我将数据复制粘贴到新的工作表中,放入自动过滤器并删除空白行...瞧,这是最终报告。
# Possible VBA solution #
I found this tutorial which seems to do exactly what I want, except for the problem where I need the macro to start a new section when the contract no. changes. I don't know how to get the VBA code below to also check for the contract no.
#可能的VBA解决方案#我发现这个教程似乎正是我想要的,除了我需要宏来开始新部分的问题,当合同没有。变化。我不知道如何获得下面的VBA代码也检查合同号。
I'd love any help you could send my way. Thanks in advance!
我很乐意为你提供帮助。提前致谢!
# Code from tutorial on get-digital-help [dot] com by Oscar. #
This is not my code, and I give complete credit to Oscar's tutorial for getting me going in the right direction.
#来自Oscar的get-digital-help [dot] com教程中的代码。 #这不是我的代码,我完全赞同Oscar的教程,让我朝着正确的方向前进。
Sub Categorizedatatocolumns()
Dim rng As Range
Dim dest As Range
Dim vrb As Boolean
Dim i As Integer
Set rng = Sheets("Sheet1").Range("A4")
vrb = False
Do While rng <> ""
Set dest = Sheets("Sheet1").Range("A20")
Do While dest <> ""
If rng.Value = dest.Value Then
vrb = True
End If
Set dest = dest.Offset(0, 1)
Loop
If vrb = False Then
dest.Value = rng.Value
dest.Font.bold = True
End If
vrb = False
Set rng = rng.Offset(1, 0)
Loop
Set rng = Sheets("Sheet1").Range("A4")
Do While rng <> ""
Set dest = Sheets("Sheet1").Range("A20")
Do While dest <> ""
If rng.Value = dest.Value Then
i = 0
Do While dest <> ""
Set dest = dest.Offset(1, 0)
i = i + 1
Loop
Set rng = rng.Offset(0, 1)
dest.Value = rng.Value
Set rng = rng.Offset(0, -1)
Set dest = dest.Offset(-i, 0)
End If
Set dest = dest.Offset(0, 1)
Loop
Set rng = rng.Offset(1, 0)
Loop
End Sub
1 个解决方案
#1
0
You may consider using pivot table which will give similar output.
您可以考虑使用数据透视表,它将提供类似的输出。
Turn off the Subtotal and show data in tabular form for all fields.
关闭小计并以表格形式显示所有字段的数据。
#1
0
You may consider using pivot table which will give similar output.
您可以考虑使用数据透视表,它将提供类似的输出。
Turn off the Subtotal and show data in tabular form for all fields.
关闭小计并以表格形式显示所有字段的数据。