筛选表以在Excel中创建另一个表 [英] Filtering table to create another table in Excel

问题描述

我需要从满足条件的表中接收值.例如，我有名字，我需要从出现名字的表中获取所有值.例如，表格由日期，名称，支出组成:

I need to receive values from a table where a condition is met. For example I have names and I need to take all values from a table where the name appears. For example the table consists of date, name, expenditures:

Date        Name    Expenditures
2019-01-28  Sara    2.45
2019-04-26  John    32.67
2019-05-07  Peter   55.88
2019-06-14  Sara    62.09
2019-07-03  Sara    12.94
2019-09-30  Peter   5.64

我需要接收Sara支付的所有费用以及完成日期后的 Date 天.结果应为以下内容:

I need to receive all expenditures which has been made by Sara and the Date days when it was done. The result should be the following:

Date        Name    Expenditures
2019-01-28  Sara    2.45
2019-06-14  Sara    62.09
2019-07-03  Sara    12.94

我需要将它显示为表格，因此过滤不适合此处的工作.基本上，我有一个大表，如第一个代码片段中所述，我需要创建另一个结构相同的表，但只按 Name 进行过滤.

I need this to appear as a table, so filtering does not do the job right here. Basically I have a large table as mentioned in the first code snippet and I need to create another table with the same structure but only filtering by the Name.

推荐答案

Sub test()

Dim allData As Range, criteriaRng As Range, i As Long

Set allData = Range("A1").CurrentRegion

For i = 1 To allData.Rows.Count
    If UCase(allData(i, 2)) = "SARA" Then
    If criteriaRng Is Nothing Then
        Set criteriaRng = allData.Range(Cells(i, 1), Cells(i, allData.Columns.Count))
        Else
        Set criteriaRng = Union(criteriaRng, allData.Range(Cells(i, 1), Cells(i, allData.Columns.Count)))
    End If
    End If
Next

criteriaRng.Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A11")

Application.CutCopyMode = False

End Sub

这篇关于筛选表以在Excel中创建另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！