Extract some rows & all columns from a table - filter each row across all columns by a text value

[Cozkincaud]

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.

Let me explain.

1. The matrix: no more than twenty columns that over time will expand to rows numbering in the triple digits. I assume this matrix should be defined and named as an Excel Table
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 cell are unique to that row i.e. rows contain no duplicates
6. However, columns will contain duplicates, but these duplicates will not necessarily be in the same column down rows. For example, the value ‘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

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

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

1. For example: My matrix is 20 rows by 12 columns and the text value ‘John’ appears in cells r2c4, r4c6, r8c4 and r10c12. If my filter is 'John', I want to extract rows 2, 4, 8 and 10 and all twelve (12) columns. The point of my analysis is to determine what other text values 'John' is associated with.
2. The constraints are as follows:
   1. The number of columns and rows in the matrix matrix will expand over time (the matrix is tags extracted from Zotero, a citation/note taking software program).
   2. There is only one tag in each matrix cell
   3. Some matrix cells will not have a tag, but can have a filler value (“” or N/A) as needed.
   4. 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.
   5. I want to automate this matrix querying as much as possible.

While I have been using Excel for nearly forty years, until now I have not needed to analyze tables or databases. Microsoft Access provided what I needed for simple database manipulation and I used SPSS for statistical analysis.

Based on what I have Googled to date, I could do my analysis with:

1. VLOOKUP
2. INDEX and MATCH
3. Pivot tables
4. Power Query

My problem is I do not know enough about these options to make an informed choice, especially as I am probably going to incorporate whatever approach I choose into a VBA program. I have not written VBA for something like 15-16 years, so I want to minimize the code I have to write. I suspect the less manual input the approach I choose requires, the less VBA coding I will be writing.

My impression is:

1. LOOKUP, INDEX and MATCH would require quite a lot of manual input with quite complex formulas. Depending on the help fellow forum members could give me, I might need to edit these formulas every time I wanted to query the matrix with a new filter
2. I have tried to query my matrix with a pivot table, but I just cannot see how I could bend a pivot table to my purpose.
3. As for Power Query, as far as I can tell, this Excel feature can only modify a table by reducing the number of rows it contains. I cannot see any way of outputting any of the matrix/table rows to elsewhere in the worksheet. In contrast to this, I want to keep querying the same matrix

So, if anyone could suggest how I might solve what seems to be a very simple problem (filter a matrix by a text value in any column and output the filtered rows/column to another range in the worksheet), I would very beholden to you.

My thanks in advance.