Index match for # and date- only non blank cells

[ShalDRH]

Hello! I have a set of month dates across a row and values filled in below for the months when activity occurs, blank when no activity occurs.

I need to look across multiple year spans and create a table to give me each date with a value in a nice little compact view. I've spent a couple of hours trying different solutions and reading other posts and I am ready to admit that I'm not going to be able to self serve! I get it to sorta work in a basic sample but not with real-life data. I don't understand it well enough to find where it is going wrong.

Examples below-

Works in theory to give me the first month and first value, second month and second value etc. However I think it just "happens" to work based on the sample data values I chose, not because the formulas are correct.

Date formula- =IFNA(INDEX(E2:M2,MATCH(TRUE,INDEX(E4:M4>0,0),0)),"")​

second date becomes =IFNA(INDEX(E2:M2,MATCH(TRUE,INDEX(E4:M4-1>0,0),0)),"") with increasing the subtraction part in red for each date field.​

​

Value # formula- =IFNA(INDEX(E4:Q4,SMALL(IF(E4:Q4<>"",COLUMN(E4:Q4)-COLUMN(E4)+1),1)),"")​

second value 3 becomes =IFNA(INDEX(E4:Q4,SMALL(IF(E4:Q4<>"",COLUMN(E4:Q4)-COLUMN(E4)+1),2)),"") with increasing the last # in red for each # field​

Tried with more realistic values below and it does not like my date formula. I guess I don't understand the -1, -2, etc piece. I thought it was telling it to go to the next non-blank... But it finds January as the answer every time.

The only other issue I have is why take #5 & #6 = #NUM error. Is it because there isn't a 5th or 6th value? It's saying it can't find a # but the IFNA part doesn't work because the error isn't NA????

Greatly appreciate any help and advice this group can give!

-S

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

Pasta3
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Take 1 Date	Jan		Jan	Feb	March	April	May	June	July	Sept
3		Take 1 #	20
4		Take 2 Date	April		20,00			25,00		3,00		20,00
5		Take 2 #	25
6		Take 3 Date	June
7		Take 3 #	3
8		Take 4 Date	Sept
9		Take 4 #	20
10		Take 5 Date
11		Take 5 #
12		Take 6 Date
13		Take 6 #
Plan7

Cell Formulas
Range	Formula
C2:C13	C2	=IFERROR(INDEX(IF(ISNUMBER(SEARCH("Date",B2)),E$2:M$2,E$4:M$4),AGGREGATE(15,6,(COLUMN(E$2:M$2)-COLUMN(E$2)+1)/(E$4:M$4<>""),INT((ROWS(C$2:C2)-1)/2)+1)),"")

Hope this helps

M.

 

[Marcelo Branco]

A picture is not helpful - is not possible to copy/paste your data for testing purposes.

Next time, to post a data sample try

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

M.

 

[ShalDRH]

T

Welcome to Mr Excel forum

Maybe something like this

Pasta3
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Take 1 Date	Jan		Jan	Feb	March	April	May	June	July	Sept
3		Take 1 #	20
4		Take 2 Date	April		20,00			25,00		3,00		20,00
5		Take 2 #	25
6		Take 3 Date	June
7		Take 3 #	3
8		Take 4 Date	Sept
9		Take 4 #	20
10		Take 5 Date
11		Take 5 #
12		Take 6 Date
13		Take 6 #
Plan7

Cell Formulas
Range	Formula
C2:C13	C2	=IFERROR(INDEX(IF(ISNUMBER(SEARCH("Date",B2)),E$2:M$2,E$4:M$4),AGGREGATE(15,6,(COLUMN(E$2:M$2)-COLUMN(E$2)+1)/(E$4:M$4<>""),INT((ROWS(C$2:C2)-1)/2)+1)),"")

Hope this helps

M.

This is excellent! Worked perfectly, Thank you so much for your quick reply. And I saw your second message. Thanks for the tip there and I will be sure to do that next time I need assistance!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.