Need Formula to display all similar values in column A on a separate tab

[mammatus]

Hello,

I'm working on creating a spreadsheet without using vba to search column A of the master table for a specific value and to display all columns associated with those rows on a separate tab.

I.e.
Column A on the master sheet has each group of people, say Blue, Orange, Green. The Corresponding columns have data relating to each person in the Blue, Orange, etc. groups. So I need a formula that will populate tabs on another sheet that will only show the Blue group in the Blue tab, and separate tab for each group.

What I've done so far is populate each column A of the desired tabs with the corresponding color, down to 300 rows thinking that no group will exceed 300 people. here is the formula that I used to pull the data (Column Headings on destination sheets are in row B):
=IFERROR(INDEX('Master_All Groups'!$A$1:$X$300,(MATCH($A3,'Master_All Groups'!$A2:$A300,0)+ROW($A3)-2),COLUMN(B$2)),"")

This formula works well for the records at the top of the list, but once I exhaust all members of one group it pulls a different set of data, vs showing as blank. I would like the formula to have lower bound so it will only show "Blue" people in the blue tab. This way when I add a few people in the master it will automatically refresh the individual tabs.

Thank you for your help.

[Fotis1991]

Perhaps if we have a small sample workbook..

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mammatus]

Thanks for the quick response:

I've attached a sample file. The master group tab is where the source data will be located. The tabs named by color are were the formulas are located. I'm not exactly sure how to describe this problem further, but I'm happy to answer any question to someone willing to help me out.

Thanks!

Example Color Groups.xlsx

[canapone]

Hi,

please see the example attached: I've used array Index.

All formulas confirmed with control+shift+enter

Example: in B2, sheet "Green"

Please Login or Register  to view this content.

Also a pivot table could be of some help.


Hope that helps


---------------------

Ciao, Fotis!

[canapone]

Hi, again. If colors are sorted, a different approach without array formulas could be:

in A2 - sheet Green- than copy the formula down and to the right.

Please Login or Register  to view this content.

Hope it helps

Attached an example

[mammatus]

Thank you so much! This works very well.

[canapone]

Ciao,

thanks for your kind feedback.

-----------------------

Hoya Saxa!