2-dimensional index match, return nth non-blank value?

[JMB10101]

Hello all,

I receive new raw data files each day, and my data will appear in different cell addresses within a fixed matrix from day to day. I need a way to pull out the data "clump" and place it in a standardized location on another tab, intact.

My matrix is constructed in the following format:
1 2 3 4 5 6 7 8 9 10 etc...
A
A
A
B
B (data) (data) (data)
C (data) (data) (data)
C (data) (data) (data)
C
C
D
etc...

The row labels have multiple instances of a given category (A, B, etc.) and the column labels are all distinct. There are blank cells all around the data-filled cells (i.e. A1, A2, B1 etc. above are all blank).

My current thought is to find a formula that returns the nth non-blank value from a matrix of data using a two-dimensional index match. The problem is I don't know how to do this! I have found advice on two-dimensional index match, and advice on finding nth non-blank values in lists, but am unsure of how to combine them.

Thank you for any advice you may have!!

[JMB10101]

Okay, so the formatting I tried to create in the matrix example didn't show up, but suffice to say that the data I want to collect and recreate on another tab will be "floating" somewhere in the matrix, with blanks all around it.

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[JMB10101]

Here's an example. The first tab represents the raw data I will receive and will copy into my template document. The columns across the top will remain static. The row names (Frog, Toad...) will stay the same each time, but the number of instances of each name (i.e. number of Frog rows, Toad rows, etc) will vary from day to day. One day I might have only one "Toad" row, and sometimes there will be 15 or more. I need to translate each chunk of data to its appropriate summary tab template, accounting for this variation.

[FlameRetired]

Hi JMB10101,

Since the data under Salamanders (and Newts) was generated by RANDBETWEEN it made it difficult to cross validate the results. I copied and pasted values back unto themselves to remedy this.

Try array entering this formula in B3 of 'Toads'. Then fill down and across to D22. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then copy that entire range and paste into B3 of 'Salamanders'.

Do I have the concept?

[FlameRetired]

I failed to mention that I extended the reference ranges to accommodate more data.

If these ranges aren't sufficient for future data feeds extend them to something a little larger than anticipated.

I tried to avoid referencing whole columns in this formula as they often result in slowed performance ... multiple calculations of over one million rows.

[JMB10101]

Thanks so much - this mostly works. The formula works in the example sheet, but when I plug the formula into my real worksheet I seem to have an issue where a few of the columns in the summary tabs show up as all zeros, even though everything is correct and I entered it as an array formula there. Most of the columns are fine but a small portion are problematic. All of the headings match up and everything looks good... At first I thought it might be related to the formatting of the cells, but all are formatted as "general". Any thoughts on this?

The frog/toad sheet was just an example. Unfortunately I can't post my real worksheet because of confidentiality issues.

[JMB10101]

Never mind, I figured it out! Formula works great, I really appreciate your help!

[FlameRetired]

Glad to hear it! Thanks for the feedback.