保留Excel工作表中的特定值并删除其他值并计算重复的vbscript的次数

I want to remove all in excel data except for rows 'containing' the name "abc".I am trying to update the excel sheet and then get the count of how many times each duplicate has repeated.The output should be another excel sheet in which i have the condensed duplicate values along with the count i got previously.

我想删除所有excel数据,除了包含'名称'abc“的行。我正在尝试更新excel表,然后计算每个副本重复多少次。输出应该是另一个excel表我有浓缩的重复值以及我之前得到的计数。

What I have done: To get the count of duplicate value I used this:

我做了什么:为了得到重复值的计数,我使用了这个:

      =COUNTIF($A$1:$A$8, A1

But how can i remove the other values and put the remaining in a new Excel Sheet.

但是我如何删除其他值并将剩余的值放在新的Excel表格中。

1 个解决方案

#1

Add a "RowNo" column with the row number or any other Row Id. Next use Microsoft Query in the output Excel file e.g.:

添加带有行号或任何其他行ID的“RowNo”列。接下来在输出Excel文件中使用Microsoft Query例如:

SELECT S1.Val, COUNT(S1.Val) FROM `C:\Book1.xls`.`Sheet1$` as S1 
INNER JOIN `C:\Book1.xls`.`Sheet1$` as S2 
WHERE S1.Val = S2.Val and S1.RowNo > S2.RowNo
GROUP BY S1.Val

Additionally to remove data not containing e.g. 'abc' you can add another condition to the WHERE clause: S1.Val NOT LIKE "abc". See below:

另外,删除不包含例如'abc'你可以在WHERE子句中添加另一个条件:S1.Val NOT LIKE“abc”。见下文:

 SELECT S1.Val, COUNT(S1.Val) FROM `C:\Book1.xls`.`Sheet1$` as S1 
    INNER JOIN `C:\Book1.xls`.`Sheet1$` as S2 
    WHERE S1.Val = S2.Val and S1.RowNo > S2.RowNo AND S1.Val NOT LIKE "*abc*"
    GROUP BY S1.Val

Feel free to test different SQL with my AddIn: link.

随意使用我的AddIn:链接测试不同的SQL。

To set this up via VBscript:

要通过VBscript进行设置:

Step 1: Create an Excel with this Microsoft Query

步骤1:使用此Microsoft Query创建Excel
Step 2: Create a VBscript that will refresh the Microsoft Query in the Excel file (See an example here of how to connect to an Excel file from VBscript link).

步骤2:创建一个VBscript,它将刷新Excel文件中的Microsoft Query(请参阅此处有关如何从VBscript链接连接到Excel文件的示例)。

#1