vba 使用 Excel 基于 4 个级别的类别创建零件编号，每个级别都有基于类别组合的唯一标识符

Using Excel to create part numbers based on 4 levels of categories that each have unique identifiers based on the category combinations

提问by jihollon

I am using Excel 2010 and have a list of products (~8000) that I need to create product code / part numbers for. These product numbers need to be based on 4 levels of categories, where each category has a code (2-3 digits of numerical values). For example:

我正在使用 Excel 2010 并且有一个我需要为其创建产品代码/部件号的产品列表 (~8000)。这些产品编号需要基于4个级别的类别，每个类别都有一个代码（2-3位数值）。例如：

• Model01: 15
  • Primary Category01: 11
    • Secondary Category01: 45
      • Tertiary Category01: 22
  • Primary Category02: 22
    • Secondary Category02: 50
      • Tertiary Category02: 23

• 型号01：15
  • 主要类别01：11
    • 次要类别01：45
      • 第三类01：22
  • 主要类别02：22
    • 次要类别02：50
      • 第三类02：23

So for example says Product A has the categories of Model01 > Primary Category01 > Secondary Category01 > Tertiary Category01 it would have a product code of 15-114522. I have this where it works in Excel. HOWEVER, at the end of each "Category Code" I also want to have a unique number to differentiate products in the same category combination. So I would want Product A to have a product code of 15-114522-XX (XX being a unique identifier number that auto increments only for that combination of categories). So 15-114522-XX and 15-123456-XX could both use 15 in the place of XX.

例如，假设产品 A 的类别为 Model01 > Primary Category01 > Secondary Category01 > Tertiary Category01，那么它的产品代码为 15-114522。我有它在 Excel 中工作的地方。但是，在每个“类别代码”的末尾，我还希望有一个唯一的编号来区分同一类别组合中的产品。因此，我希望产品 A 的产品代码为 15-114522-XX（XX 是唯一标识符编号，仅针对该类别组合自动递增）。所以 15-114522-XX 和 15-123456-XX 都可以使用 15 代替 XX。

So far I have tried finding the duplicates using COUNTIF and then ranking them sequentially so that each category code that was duplicated was ranked with a unique number, but that ranked all duplicates (different category combinations that were both duplicated. I found the formula to rank sequentially here: http://support.microsoft.com/kb/152567/en-us

到目前为止，我已经尝试使用 COUNTIF 查找重复项，然后按顺序对它们进行排序，以便每个重复的类别代码都使用唯一的编号进行排名，但是这对所有重复项（都重复的不同类别组合）进行了排名。我找到了排名的公式依次在这里：http: //support.microsoft.com/kb/152567/en-us

So I need a way to generate existing part numbers and a way to continually generate new part numbers if we add products.

因此，我需要一种生成现有零件号的方法，以及一种在我们添加产品时不断生成新零件号的方法。

I really feel like Excel is not the best way to do this, so if you have another suggestion for a way to do it I would be glad to hear it. I know some PHP, so if I could use PHP and MySQL Database that would be fine. I feel like this might need to use VBA to accomplish this through Excel(which I do not know, but I am sure I can figure out).

我真的觉得 Excel 不是做到这一点的最佳方式，所以如果您有其他建议，我会很高兴听到它。我知道一些 PHP，所以如果我可以使用 PHP 和 MySQL 数据库就好了。我觉得这可能需要使用 VBA 来通过 Excel 来完成（我不知道，但我相信我可以弄清楚）。

I realize that this is an extremely hard thing to understand so I will also link a Google Doc of the Spreadsheet where you can view it or download it if you would like.

我意识到这是一件非常难以理解的事情，因此我还将链接电子表格的 Google 文档，您可以在其中查看或下载（如果您愿意）。

Google Spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AlXmrYIdv91SdEFQVklURkdTZER0NFkxZ1B4ZFdZYXc

谷歌电子表格：https: //docs.google.com/spreadsheet/ccc? key =0AlXmrYIdv91SdEFQVklURkdTZER0NFkxZ1B4ZFdZYXc

I would appreciate any and all responses. Let me know if you need anymore information and I will do my best. Thank you!

我将不胜感激任何和所有的回应。如果您需要更多信息，请告诉我，我会尽力而为。谢谢！

-Jacob

-雅各布

采纳答案by Mason

Personally I'd place another column right of "Part Number" labeled "Part ID". Place the following into the cell and drag it down to copy the code with the relative cell references.

就我个人而言，我会在“零件编号”的右侧放置另一列，标记为“零件 ID”。将以下内容放入单元格并将其向下拖动以复制具有相关单元格引用的代码。

=H2 & COUNTIF($H:H2,H2)