I wasn't quite sure how to title this question, but it's as concise as I could think of. Sorry if it's junk.
我不太确定如何标题这个问题,但它就像我想象的那样简洁。对不起,如果它是垃圾。
I have a list created on Sheet2 based on info from Sheet1. What I'd like to do is examine ColumnA and remove duplicate rows if the matching value on Sheet1 ColumnB's offset ColumnE doesn't equal "SUB-ASSY".
我有一个基于Sheet1信息在Sheet2上创建的列表。我想要做的是检查ColumnA并删除重复的行,如果Sheet1 ColumnB的偏移ColumnE上的匹配值不等于“SUB-ASSY”。
Basically: Find the value from Sheet2, ColumnA on Sheet1, ColumnB. Get the value from Sheet1, ColumnE If it does NOT match "SUB-ASSY", delete any duplicates on Sheet2 This should leave one instance of all non-subassembly entries and leave subassemblies alone.
基本上:从Sheet1,ColumnB上的Sheet2,ColumnA中查找值。从Sheet1,ColumnE获取值如果它与“SUB-ASSY”不匹配,则删除Sheet2上的任何重复项。这应该保留所有非子组件条目的一个实例并保留单独的子组件。
Does this make any sense?
这有意义吗?
I think I need to do some INDEX/MATCH or VLOOKUP, but I don't know if that'd be the right way to go (or exactly how I'd do it). Does anyone have a good way to do this?
我想我需要做一些INDEX / MATCH或VLOOKUP,但我不知道这是不是正确的方法(或者我是怎么做的)。有没有人有一个很好的方法来做到这一点?
I appreciate any help. I can include the workbook if needed, but it looks up a bunch of stuff on an sql server, so it might not work correctly if you can't connect.
我感谢任何帮助。如果需要,我可以包含工作簿,但它在sql server上查找了很多东西,所以如果你无法连接它可能无法正常工作。
Thanks Again,
-stu
1 个解决方案
#1
Are you looking for a general idea on how to do this, or a VBA macro?
您是否在寻找关于如何执行此操作或VBA宏的一般概念?
One way I'm thinking that could get you going is in your second sheet (which, in the end, will have only unique entries) create a 'helper column'. Let's say Column F is empty. You can do a simple "if" statement that will check for the value on that row, in column A is in that other sheet. If so, return "DUPLICATE" (or some note, "Duplicate, delete", etc.). Then run that down the columns, then sort that whole table by your new column. This will sort all the duplicate values in one big block, which you can highlight and delete.
我认为可以让你前进的一种方法是在你的第二张表格中(最后,它只有唯一的条目)创建一个“辅助列”。我们说列F是空的。您可以执行一个简单的“if”语句来检查该行上的值,A列位于该另一个工作表中。如果是,请返回“DUPLICATE”(或某些注释,“复制,删除”等)。然后在列中运行,然后按新列对整个表进行排序。这将对一个大块中的所有重复值进行排序,您可以突出显示并删除它们。
It could help if you show a little bit of what the data looks like on your Sheet1 and Sheet2 (can be made up data, doesn't have to be SQL server info).
如果您在Sheet1和Sheet2上显示数据的一些内容(可以组成数据,不必是SQL服务器信息),它可能会有所帮助。
#1
Are you looking for a general idea on how to do this, or a VBA macro?
您是否在寻找关于如何执行此操作或VBA宏的一般概念?
One way I'm thinking that could get you going is in your second sheet (which, in the end, will have only unique entries) create a 'helper column'. Let's say Column F is empty. You can do a simple "if" statement that will check for the value on that row, in column A is in that other sheet. If so, return "DUPLICATE" (or some note, "Duplicate, delete", etc.). Then run that down the columns, then sort that whole table by your new column. This will sort all the duplicate values in one big block, which you can highlight and delete.
我认为可以让你前进的一种方法是在你的第二张表格中(最后,它只有唯一的条目)创建一个“辅助列”。我们说列F是空的。您可以执行一个简单的“if”语句来检查该行上的值,A列位于该另一个工作表中。如果是,请返回“DUPLICATE”(或某些注释,“复制,删除”等)。然后在列中运行,然后按新列对整个表进行排序。这将对一个大块中的所有重复值进行排序,您可以突出显示并删除它们。
It could help if you show a little bit of what the data looks like on your Sheet1 and Sheet2 (can be made up data, doesn't have to be SQL server info).
如果您在Sheet1和Sheet2上显示数据的一些内容(可以组成数据,不必是SQL服务器信息),它可能会有所帮助。