If found in Column A, display entire row

Question

I am trying to do a lookup but VLOOKUP does not seem to be the answer...maybe an INDEX and MATCH formula but I can't wrap my head around it.

Anyway, I have two tabs, one with data, and the other one will pull parts of the data from the first tab. In tab one my columns look like this (Google Sheets):

TAB 1      A           B          C       D      E
ROW 1 - PRODUCT      DATE        GARY    TOM    MARY
ROW 2 - Apples       9/1/2014    45      22     37
ROW 3 - Pears        9/1/2014    15      12     17
ROW 4 - Oranges      9/1/2014    18      27     61
ROW 5 - Figs         9/1/2014    4       2      7
ROW 6 - Apples       8/1/2014    35      21     31
ROW 7 - Pears        8/1/2014    19      7      11
ROW 8 - Oranges      8/1/2014    48      41     31
ROW 9 - Figs         8/1/2014    16      7      17

In TAB 2, I have the same Columns of Product, Date, Gary, Tom, Mary, but I would like to group their info by product and date. For example, TAB 2 would pull all data that matched Apples and display the entire row. So Tab 2 WOULD give these results:

TAB 2      A           B          C       D      E
ROW 1 - PRODUCT      DATE        GARY    TOM    MARY
ROW 2 - Apples       9/1/2014    45      22     37
ROW 3 - Apples       8/1/2014    35      21     31

I would then repeat this for Tab 3 which would pull data for Pears, tab 4 for Oranges, and so on. Of course we will be adding data to this each month so the formula in tab 2 will need to reflect new additions.

Thoughts?

Answer 1

This is not going to be possible using standard Excel formulas. To accomplish what you wish I would recommend using several Pivot Tables.

Step 1

Start by selecting the data and navigating to Insert -> Pivot Table in the menu (depending on the version of Excel you are using, the Create PivotTable dialog may differ slightly).

Step 2

Select the PRODUCT column for your filter, the DATE column for your row groups, and the sums of GARY, TOM, and MARY for your values. Set the filter value to Apples (or whichever product you wish to display on this worksheet).

Step 3

Make any desired cosmetic changes to the PivotTable, and then repeat for each worksheet.

Notes

• The PivotTable will not update automatically. If you intend to continue to add rows to your first worksheet, I would recommend setting the Table/Range (in step 1) to something like Sheet1!$A$1:$E$1000. Then, when an edit is made you can click the Refresh button in the menu to refresh the data.

• If you don't want to have to refresh all tables manually, you can build a macro that will do so automatically.

Answer 2

I suggest using PivotTables, as opposed to formulas. You can name the data range you use which can be large enough to accommodate future entries and create a PivotTable on each tab quite easily - select the data or named range and do Insert -> PivotTable. Then, within the PivotTable Field List, use the "Choose fields to add to report:" filters and select the specific product you want for a given tab. This would work best if there are only a few handfuls of items as managing 100's of different products or product types may become tedious.

By using a large enough range, you can add data to the set and/or range and use the "Refresh All" button under the 'Data' tab to update the workbook.

Chandoo.org provides some great resources for PivotTable use. Also, when I first started I was fond of fiveminutelessons.com. To be honest, a quick Google search should turn up some decent help topics

Answer 3

1) in TAB 2 go to cell A1. right click, and then click data validation.
set up the box as shown below.

2) in cells B1:E1 put DATE GARY TOM MARY respectively
3)in cell A2 write the following formula: =filter('TAB 1'!A:E,'TAB 1'!A:A=A1)
4)choose your fruit from the dropdown box.
5)it might be worht formatting A1 with a yellow fill or something...