Filtering a text to show me all its info in separate worksheet

Couple ways to do this. Firstly, there are array formulas to do it. Assuming that "Cluster" data (as you called it) is listed in Sheet1!A1:A13, and the "Sites" in Sheet1!B1:B13, and that cell A1 in Sheet2 (another worksheet in same workbook) would contain an entry that is listed anywhere in column A of Sheet1, then, enter this in A4 of Sheet2 (Confirm with Ctrl Shift Enter) Don't press just enter.

Please Login or Register  to view this content.

Change the ranges to accommodate your sample, drag down as necessary. Now, what is necessary? Well it depends on how many site you'd expect that would pop up. In my case there are only a max of 12 possible answers, so I wouldn't drag down more than 12. Bear the following in mind, array formulas are very slow and memory hungry. So don't go crazy on them place it hundreds of times on your spreadsheet (i.e. drag my formula over hundreds of cells).

If your data table is huge, then, I'd recommend you use AutoFilter. Kindly research into Autofilter and see that would be more appropriate.

You could try using a pivot table with both custer and site moved into the row values area. See attachment. I did it twice, once formatted as outline and once as tabular.