Index match for # and date- only non blank cells

ShalDRH

New Member
Joined
Jul 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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​

1627503829098.png


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.

1627503905020.png


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
 

Attachments

  • 1627503009105.png
    1627503009105.png
    21.1 KB · Views: 3
  • 1627503315754.png
    1627503315754.png
    21.5 KB · Views: 5

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Welcome to Mr Excel forum

Maybe something like this

Pasta3
ABCDEFGHIJKL
1
2Take 1 DateJanJanFebMarchAprilMayJuneJulySept
3Take 1 #20
4Take 2 DateApril20,0025,003,0020,00
5Take 2 #25
6Take 3 DateJune
7Take 3 #3
8Take 4 DateSept
9Take 4 #20
10Take 5 Date 
11Take 5 # 
12Take 6 Date 
13Take 6 # 
Plan7
Cell Formulas
RangeFormula
C2:C13C2=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.
 
Solution

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
A picture is not helpful - is not possible to copy/paste your data for testing purposes.

Next time, to post a data sample try

M.
 

ShalDRH

New Member
Joined
Jul 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
T
Welcome to Mr Excel forum

Maybe something like this

Pasta3
ABCDEFGHIJKL
1
2Take 1 DateJanJanFebMarchAprilMayJuneJulySept
3Take 1 #20
4Take 2 DateApril20,0025,003,0020,00
5Take 2 #25
6Take 3 DateJune
7Take 3 #3
8Take 4 DateSept
9Take 4 #20
10Take 5 Date 
11Take 5 # 
12Take 6 Date 
13Take 6 # 
Plan7
Cell Formulas
RangeFormula
C2:C13C2=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!
 

Forum statistics

Threads
1,147,568
Messages
5,741,882
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top