Finding column & row headers for each showing of specific text in a table of data

I'm stuck. I'm trying to find the best approach to analyze a table of data. My table of data consists of data that includes:

• two letter acronyms. For example, RB, FA, CU, etc.
• the table of data is randomly generated
• each column has a set column header (two digit number)
• each row has a set row header (one digit number)

What I am trying to accomplish is a VBA or macro to scan the predefined table and to list EVERY column/row headers where each specific two letter acronym exists. I need the column/row header combined in a single cell (not cell column/row but the headers) for each showing in the table of data. Some two letter acronyms may show more than others. I'm attaching a sample file that I hope helps better explain what I am trying to do. I appreciate any help the community can offer.

Sample File.xlsx

Thank you again for your reply MrIce. Can you see this sample file and explain how I would get the macro to pull the data from the Data tab and push it to the Out tab? I put a Macro button on the Data tab but was getting errors in trying to get your supplied code to push the results to the Out tab. Can you help?Sample File II.xlsm

Martin,

Can you PM me your PayPal address. I would like to shoot you a thank you for your time.

Martin,

I believe I figured out most of it. I have one final question I hope you can help me with. For the results derived from the code you provided to generate data in a new tab: is it possible to cap the results at a set number, for example, say 30 results, and have results 30-59 go to the next line? My table of data will produce a couple of acronyms that will have more results than can fit on one line, causing the output to stretch to two pages. What I am trying to do for those acronyms is have it push to line #2 after X number of results, so I can keep the results to one page. Is that possible?

Martin,

Thank you for your reply. When using the above code...I get the following error:

"Run-time error '91': Object variable or With block variable not set"

It is referencing this line of code: TargetRow = Sheets(2).Columns(1).Find(Cells(N, M), , xlValues, xlWhole).Row

Any suggestions?

I figured out what the issue was....auto calculation of the sheet was enabled and it was causing the error.

I do have one other question...is there a way to order the results from smallest to largest, so the results are in order?

Martin,

If you look at my original sample file, you will see the following listed under my results desired:

FA 011 021 031 033

Your code is spitting out the results accurately but with my larger set of data (much bigger than sample) I would like to numbers to be listed in order from smallest to largest, if possible. I believe the code you supplied is listing them in order by which they are found row by row. Is it possible to list them in order from smallest number to largest?

Martin,

That did it! The columns/rows will always be in ascending order.

Is there any way to have the two letter acronyms listed in alphabetical order too? That would put the results in alphabetical order, with the numbers listed to the right from smallest to largest.

I will mark this thread as Solved and will PayPal you a thank you for your time. I REALLY appreciate it!!

Martin,

That did not work. What I am attempting to accomplish is to generate the following by swapping the rows and columns:

Acronym A Acronym B Acronym C
011 201 014
210 311 113

I would like the acronyms listed horizontally across the top (columns), with a row for each showing of that acronym in the table. I would like the rows of data to go from smallest to largest (this would be vertically). This would push the data down the page, as opposed to across the page. Does that make sense?

Sorry. My sample mentioned above did not display very well. Trying again.

AA AB AC AD
102 103 124 111
104 105 212 412
141 143 225 413
303 205 252 454
503 302 305
323 353
444 404
411
415

Martin,

That did it. Thank you. Three small questions.

1. How do I get the order of the columns to be alphabetical? A -> Z?
2. How do I push the results down to row two, instead of row 1?
3. How do I push the results left to column #1, instead of column #2?

Thank you kindly for your help.