Best way to OUTPUT/COPY selected rows and all columns from a dynamic table

Hi,

I need help determining the best way to extract rows from an Excel r x c matrix with all columns extracted and the rows extracted according to a text value that might appear in any, but only one of the array columns.

I am pretty sure I could do it with some combination of LOOKUP, INDEX and MATCH. The only problem I see here is that the r x c matrix steadily increases in size over time, especially the number of rows. I want to automate my analysis as much as possible.

I wonder if I could use either a pivot table or the Power Query function for my recurring analysis. I have tried using a pivot table, but without success. As for the Power Query, it seems to only reduce a database in size by, in my case, removing rows. Instead, I want to copy/extract rows from my r x c matrix to another area of my workbook. I do not want to change the r x c matrix in any way.

Here is my issue

What I want to do: extract from a matrix all rows and all the columns where any column for any row has a specified textual value.

The matrix/table

1. The matrix: no more than twenty columns and over time will expand to rows numbering in triple figures
2. All values are text
3. Each matrix cell has only one text value.
4. Some of the cells in the matrix are blank, but I can I fill these with null characters (e.g. “”) if needed.
5. The text values in each are unique to that row i.e. rows contain no duplicates
6. However, columns will contain duplicates, but these duplicates will not be in the same column down rows. For example, ‘John’ may appear in r2c4, r4c6, r8c4 and r10c12. However, ‘John’ will never appear more than once in the same row.
7. These duplicates do not need removing from the matrix

The analysis/expected result/output

The result should be a reduced version of the original r x c matrix. This reduced matrix will have fewer rows, but the same number of columns as the original matrix. It should be placed in another part of the Excel workbook

1. For example: My matrix is 20 rows by 12 columns and the text value ‘John’ appears in cells r2c4, r4c6, r8c4 and r10c12. I want to extract rows 2, 4, 8 and 10 and all twelve (12) columns because my filter is ‘John’.
2. The constraints are as follows:
a. The number of columns and rows in the matrix will expand over time (the matrix is tags extracted from Zotero, a citation/note taking software program).
b. There is only one tag in each matrix cell
c. Some matrix cells will not have a tag, but can have a filler value (“” or N/A) as needed.
d. The row selection value/filter (‘John’ in the above example in 1.) will be different every time I query the matrix. I want to have this filter in a named cell and be able to enter it automatically. I assume the best way to do this would be i) a macro ii) VBA code. Ultimately, I hope to automate the input, formatting, and outputting of my Zotero tags in Excel with macros/VBA code.
e. I want to automate this matrix querying as much as possible.

I am seeking advice on the best and most economical way of accomplishing this in Excel. I want to minimize my own input (copying ranges, changing formulas, manual sorts). Ideally, I want to do nothing more than enter the filter text value.

My thanks in advance for your advice and suggestions.

Dear Glenn

Apologies for my verbosity. I was trying to make my problem very clear, but obviously failed.

As you requested:

1. Attached are the relevant worksheets from my current workbook. I am sending you real data, not a sample.
2. The table/array I am seeking help with presently has 31 rows by 13 columns
3. In time it may grow to over a thousand rows. The number of columns, however, will increase by only a hand full. I doubt if I will have more than 20 columns.
4. My database contains the tags I add to the citations and notes I record in Zotero, a citation/note taking program. The longer and more extensively I research, the more citations and notes I will be adding to my database. The number of tags (rows) will increase in tandem
5. I have tried to, as you requested, to collapse all the merged cells. I don't think I have missed any. Apologies if I have.
6. Each worksheet has comments in red font explaining the data and how they were input/created.

I thank you most kindly for your offer of help and well-taken advice on how to ask for help.

I hope the attached workbook is more comprehensible than my original post.

If you have any questions, please contact me.

Thanks again for you help.

I look forward to hearing your suggestions and comments.

Sincerely,

Graham

P.S. I followed your directions regarding attaching the Workbook. Let me know if it didn't work and I'll try again.