Search code examples
excelexcel-formuladashboard

Excel data extraction for specific condition

I have my data as mentioned in the below image:

From column A to H is my input field.

enter image description here

I want to automate my data, in such a way that whenever I continuing inserting the data below this, I should get only "CC" (column H) and the data adjacent to it should come in the another subsheet.

Example (output) as mentioned in image below:

enter image description here

Solution

  • Convert your data to table (Insert->Table) so it expands automatically when you add new row and use something like this:

    =Table2[#Headers] in G1 to get table headers.

    =FILTER(Table2,Table2[Data5]="a") in G2 to get filtered data.

    Result:

    enter image description here

    Or do not make it a table and use some kind of placeholder for possible max row:

    =FILTER(A2:E1000,E2:E1000="a")

    enter image description here