Return non-blank values from multiple rows and common third date row

[John92108]

Hello,

I am seeking help for an Excel (2013 version) formula to return all non-blank values from 2 different rows and an associated 3rd date row
and transpose this into a simple 3 column list for charting. The date will be output only if either of the rows (or both) have data.

My example input data in 3 rows (starting in A1 in this example)
Example-Input-3-Rows.png

My expected output in 3 columns for chart:
Example-Ouput-3-Colums-RevA.png

I expect to have 3 formulas in the output columns for A, B, and C.

I would imagine that a combination of index, small, and match might work but am seeking some assistance.

Thank you,
John

[snolem75]

in cell 6a:
=OFFSET(B1,0,COLUMN()-1)

In Cell B6:C12
Starting in B6
=INDEX($B$2:$J$3,Match(B$5,$A$2:$A$3,0),Match($A6,$B$2:$J$2,0))



You could also just paste special and transpose.

[sandy666]

• Ctrl+C
• Alt-H-V-T

[John92108]

Thanks for your reply. I'm trying to get it to work now...

I'm current getting all #N/A values for the Plan Actual matrix.

Column A offset formula doesn't yield the correct series of dates...only first date in cell A6 (2/28) is correct. Others are not correct.
Even with 2/28 in A6 - both Plan and Actual yield #N/A.

Output-with-formula.png

A6 =OFFSET(B1,0,COLUMN()-1)
A7 =OFFSET(B2,0,COLUMN()-1)
B6 =INDEX($B$2:$J$3,MATCH(B$5,$A$2:$A$3,0),MATCH($A6,$B$2:$J$2,0))
C6 =INDEX($B$2:$J$3,MATCH(C$5,$A$2:$A$3,0),MATCH($A6,$B$2:$J$2,0))

I've tried both with and without Cntrl Enter array formats and neither helps - same results.
The blank cells are really blank (not null)....so ISBLANK returns True for B2, I3, etc.

I need a formula for this in Excel. I realize (and can't use) the special and transpose paste functions.

Thanks again - John

[snolem75]

Sorry, you need fixed reference for offset.

A6 should be
=OFFSET($B$1,0,COLUMN()-1)
copy and paste down.

And the second match needs to be referring to the dates.

I apologize, my bad! Match was going to the wrong row.
B6 =INDEX($B$2:$J$3,MATCH(B$5,$A$2:$A$3,0),MATCH($A6,$B$1:$J$1,0))
C6 =INDEX($B$2:$J$3,MATCH(C$5,$A$2:$A$3,0),MATCH($A6,$B$1:$J$1,0))

[John92108]

Thanks again - getting much better....almost there....
Date propagation in Col A is invariant... with all values in Col A showing 2/28 - needs to auto increment over C1, D1... etc.
Output-with-updated-formula.png
I think the B6 and C6 formulas and columns are o.k. now though....

thxs

[snolem75]

Sorry again... I must have a case of the Mondays.

We want to offset the original location by 1 additional number with each time the formula moves down... Unfortunately I told you to use column even though the column number is staying the same
Change the column portion of the formula in column A to instead ROW() and you should finally be golden.

Again, sorry for the run-around.

[AlKey]

Try this
Enter formula in B6 and drag across to C6 and down.

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G	H	I	J
1	Date	2/28	3/1	3/2	3/3	3/4	3/5	3/7	3/8	3/9
2	Plan		35		7			5	6
3	Actual	2				16		4		9
4
5	Date	Plan	Actual
6	2/28		2
7	3/1	35
8	3/2
9	3/3	7
10	3/4		16
11	3/5
12	3/7	5	4
13	3/8	6
14	3/9		9

[John92108]

Thanks however changing A6 =OFFSET($B$1,0,ROW()-1) seems to change the nature of the resulting plan and actual columns. It now shows the first date as 3/5 from Col G probably because the current row is 6. Also the 3/5 date should be skipped since it doesn't have a Plan nor Actual value - it shows both as zero values.
Output-with-forumla-row-0.png

I tried A6 =OFFSET($B$1,0,ROW()-6) which gives me all of the dates with the dates I want skipped in the list. The computation is correct in B6...to C14 but it includes the dates I don't want without Plan or Actual values...it just transposed now.....
Output-with-forumla-row-6.png

I now need to skip the dates that don't have Plan or Actual values for the Chart.

Thanks for your help.

[John92108]

- Ignore please

[John92108]

AlKey. - Thanks for your alternative answer.

Very good answer and similar to the other answer but a bit cleaner with blanks instead of zeros.
However still left with dates that are blank values for both Plan AND Actual that I don't want in the Chart sequence at all
(it's a much bigger list for the real application).

The result is clean - but I need the date propagation sequence in Col A (A6...A14 here)... to automatically skip over the dates that are
blanks (no data) for both Plan AND Actual dates. In other words - if either Plan or Actual has a value, then I want to include it in my Chart....

So my desired output would look like =>
Example-Ouput-3-Colums-RevA.png

Thanks - John

[bebo021999]

Assuming date is in increasing order.

Try in A6:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Drag down

Try in B6 then drag down and accross:

Please Login or Register  to view this content.

[John92108]

Thank you very much bebo021999 ! - Excellent worked correctly.

Thanks also to both AlKey and snolem75 for their help.

- John