现在,我正在尝试过滤 Excel 电子表格,以便列中删除所有重复数据。
这通常不会成为问题,但我处理的 Excel 电子表格有 400 列,每列 2000 行,并且逐个单击删除重复项非常乏味。
我尝试突出显示所有数据并点击删除重复项,但它并没有像预期的那样工作。我期望的是,现在每列都只显示该特定列的唯一行。相反,它要么返回一条消息,告诉我没有找到重复的行,要么过滤所有列中的唯一行。就像它只做了的那样:
SELECT DISTINCT *
FROM table
有没有办法过滤所有列,并且只过滤掉特定列的所有重复行?像这样:
SELECT DISTINCT col1
FROM table
SELECT DISTINCT col2
FROM table
...
SELECT DISTINCT coln
FROM table
我真的不认为这是必要的,但是它在这里:
前
+-------+---------------+--------------------+
| Name | City | Position |
+-------+---------------+--------------------+
| John | Philadelphia | Software Developer |
| John | New York City | Software Engineer |
| John | Washington DC | Accountant |
| Henry | Philadelphia | Janitor |
| Jeff | Philadelphia | Project Manager |
| Paul | Philadelphia | Software Engineer |
| Dave | Boston | Accountant |
+-------+---------------+--------------------+
后
+-------+---------------+--------------------+
| Name | City | Position |
+-------+---------------+--------------------+
| John | Philadelphia | Software Developer |
| Henry | New York City | Software Engineer |
| Jeff | Washington DC | Accountant |
| Paul | Boston | Janitor |
| Dave | | Project Manager |
+-------+---------------+--------------------+
答案1
使用 VBA:
Dim i As Integer
For i = 1 To 400
Columns(i).EntireColumn.RemoveDuplicates Columns:=1, Header:=xlYes
Next i