Random table of data - need to find every cell that contains specific data

[stwilson]

Here is what I am attempting to accomplish. I have two small tables of randomly generated data.

• each table has a header row and header column (see Grids)
• randomly generated data consists of two letter abbreviations (see Variables)
• I need to pull from random table data every cell that contains a specific two letter abbreviation
• and show the column header and row header (combined in one cell) for each cell that contains the two letter abbreviation

My question is: Is there a way to systematically find every cell that contains specific text and display the column header/row header for each occurrence found within a range of cells? Please see my attached sample for further clarity.

[dilipandey]

Hi Stwilson,

Welcome to the forum.

How you are getting 031 in B17?

Regards,
DILIPandey

<click on below 'star' if this helps>

[stwilson]

Thank you for your reply DILIPandey. I knew my example would still be confusing. Let me explain further.

The two tables on the Grids tab are randomly generated. They most likely re-randomized...making my example harder to understand.

What I am trying to accomplish is to create a new table for each two letter abbreviation found in the Variables tab. Within each newly created table, I would like to see every cell from the two randomly generated tables that contains that specific two letter abbreviation. For example, CH. Here's the tricky part. I want each cell that contains the two letter abbreviation to be represented by a combination of the headers (column headers - row 1 + row 8 AND the row headers). Sample results: 011, 021, 121, 311, 554.

Find all cells that contain CH and list the column header and row header in a cell together for each occurrence of CH in the two tables on the Grids tab. I am attaching a new sample file with some additional descriptors that I hope will explain more.

Does this make more sense?

[dilipandey]

Hi Stwilson,

I know it may be Random function which is confusion me...
Could you freeze the numbers and let's focus on one result at a time.... I believe Index/ Offset or Match function can help here.. share a small example (without random function) so that we can discuss being on a single page. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[stwilson]

DILIPandey,

Thank you for your reply. I am attaching a new sample file. Here are the changes I have made. There are three tabs. Variables Tab has the variables I am using. Grid tab has the random function I need to use to generate the two tables of random variables (top two tables). IN ADDITION, I created a copy of the two tables BUT pasted the values only into these newly created tables (bottom two tables), so the random function was removed from the equation. The Card Tab includes a sample of what I am now trying to accomplish. I would like the Card tab to extract the locations (column/row) of each specific variable from the bottom two tables on the Grid tab.

I included samples for two variables in the Card tab. They are SB and PO. You will see I would like know the column/row for each showing of each of those variables in the table of data on the Grid tab. The three numbers for each display represents the column name and row number (need to show preceding zero too as I will have some columns that start with zero). That's it. I hope this makes sense.

[stwilson]

DILIPandey???? Any suggestions?

[stwilson]

Anyone else know how I might accomplish the task mentioned?

[dilipandey]

Hi stwilson,

See the attached file where I have extracted the rows and columns (as per excel spreadsheet).. let me know if this helps. thanks.

Random Tables 3(1).xlsx

regards,
DILIPandey

<click on below 'star' if this helps>