Return only whole numbers

[riedyp]

Hello ,
I am trying to make a condensed table(White picture Circled in Red) from returning values in another table(Yellow table). I only want to return the whole numbers without leaving blank spaces. I am currently trying to find the best way to do this. I do not know if its possible to do without VBA. My current method is using the INT function in Column A of the "Yellow picture" to see if column A and B match and then want to return the value of the match to my other table(White Picture" circled in Red) without any spaces between numbers being seen.

 

[KRice]

Yes, from my working copy, that's what the "" at the end of this formula does:
=IF(ROW()-ROW(A$14)<=SUMPRODUCT(--(ProjectTasks!$B$2:$B$13=INT(ProjectTasks!$B$2:$B$13))),ROW()-ROW(A$14),"")

Can you confirm that your formula in K33 matches this one...and that it is copied down your table:
=IF(ROW()-ROW(K$32)<=SUMPRODUCT(--('Project - Gantt Chart'!$B$9:$B$45 =INT('Project - Gantt Chart'!$B$9:$B$45))),ROW()-ROW(K$32),"")
If the number of rows that are being built in your summary table are less than or equal to the number of main project phases found in the ProjectTasks table, then a number is inserted...a number given by ROW()-ROW(K$32) (where in this case ROW(K$32) corresponds to the cell containing your table column header "Phase #"). And once this counter exceeds the number of phases found in the ProjectTasks table, a blank ("") is inserted.

 

[riedyp]

Yes that is the formula i am using, I figured that "' was used for that reason and Iw as trying to figure out why it would not work.

 

[KRice]

Okay, I see the problem. Unpopulated cells are treated as 0 in this part of the K33 formula...
--(ProjectTasks!$B$2:$B$13=INT(ProjectTasks!$B$2:$B$13)
And since 0=0, a row is generated. Let me try a different approach.

 

[riedyp]

Okay thank you for your efforts.

 

[KRice]

Try this in your K column on the summary sheet. An array formula will need to be confirmed with Ctrl-Shift-Enter and then dragged down. Let me know if it works for you.

Book1c.xlsx
	K
32	Phase #
33	1
34	2
35	3
36
37
38
39
40
ProjectSummary

Cell Formulas
Range	Formula
K33:K40	K33	=IF(ROW()-ROW(K$32)<=SUM(IFERROR(--(('Project - Gantt Chart'!$B$9:$B$45/INT('Project - Gantt Chart'!$B$9:$B$45))=1),"")),ROW()-ROW(K$32),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[riedyp]

That worked! Thank you for your help all day, it is very much appreciated.

 

[KRice]

Great! You're welcome...I'm sorry for the trouble with the column K formula. I need to investigate why the original version worked okay on my end, but then when I reconfigured my worksheets to look like yours, I was able to duplicate the issue you encountered...I'm glad we found a work-around.