CODEWITHSUNDEEP
excelexcel-formula
Josiah Hulsey
Josiah Hulsey

Reputation: 499

Excel Formula or function that returns the Nth value from a dynamically generated grouping of cells

I am trying to assemble a index/match combination and am having trouble figuring out how to make it work. I have experience with a lot of the formula types in excel, but unfortunately I am pretty ignorant when it comes to these functions.

I will explain what I am trying to do first, but I have attached 3 images at the end that will probably make things more clear.

In order to identify the specific values I want, I am having to use helper cells. These helper cells are denoted with the (helper) tag in the pictures. These cells go through and grab the adjusted closing price of the stock (column A) at the beginning (column C) and the end (Column D) of a dynamically calculated period.

I would like to consolidate these values into numerical order in columns F and G. The thought is that the first non zero number in C/D is belongs to the first predefined period and should go into columns F/G beside the #1 (column E). This gets carried on through all of the periods (ex: 2nd non zero goes beside the number 2, third nonzero number goes beside the number 3 etc.)

This is just an example of one stock. I need the function or formula to be dynamic enough to work on a wide variety of distributions. Sometimes there are up to 100 dynamically calculated periods within the stock analysis.

Below are the images that should provide more clarity

Image 1 is an example of what the data looks like

Image 2 is a crudely drawn example of how I would like the data to move

Image 3 is the desired result

Image 1Example of data

Image 2 Moving Data

Image 3 Results

Updated image for Scott Craner showing out of order results For Scott Craner

Please let me know if I can clarify any confusion.

Upvotes: 0

Views: 808

Answers (3)

Scott Craner
Scott Craner

Reputation: 152585

Put this formula if F2:

=INDEX(INDEX(C:C,MATCH($E2,$B:$B,0)):INDEX(C:C,MATCH($E2,$B:$B,0)+COUNTIF($B:$B,$E2)-1),MATCH(1E+99,INDEX(C:C,MATCH($E2,$B:$B,0)):INDEX(C:C,MATCH($E2,$B:$B,0)+COUNTIF($B:$B,$E2)-1)))

Copy over one column and down the list.

enter image description here

Upvotes: 0

Chris Moore
Chris Moore

Reputation: 456

If you just need to return the first value of each period (column C) and the last value of each period (column D), you could use index match and lookup to do this without even using helper columns.

Try this in cell F2 

=INDEX(A2:A50,MATCH(E2,B2:B50,0))

And this in cell G2

=LOOKUP(E2,B2:B50,A2:A50)

Depending on much variance is in your overall number of rows, you could use indirect references in the formulas to dynamically update the ranges. Example:

=INDEX(A2:INDIRECT("A"&COUNTA(A:A)),MATCH(E2,B2:INDIRECT("B"&COUNTA(A:A)),0))

Upvotes: 1

Quan Le
Quan Le

Reputation: 11

You will need to open macro. Then do the following in recorded macro. + Filter only non-null value in C/D + Select whole column in C/D then copy the whole column + Turn off Filter + Paste the whole C/D in F/G + Stop macro

Gook Luck

Upvotes: 0

Related Questions