Simple Excel Forumula?

[clock245]

Hi, hopefully someone can help me. I'm looking for a non-VBA solution to a simple problem---i think.

Column A thru C are user entered fields.
Column F is what i'm hoping for some help on.

If a user says Column C is either Partial or No then i'd like Column F to be filled out with the No and Partial. It needs to feed into another spreadsheet with other criteria which is why i can't do a simple filter/pivot table.

Thank you so much for your help! Hopefully the below HTML works!

Excel 2012
	A	B	C	D	E	F
1	Problem	Resolution	Resolve Issue			Remaining Problems
2	Need A	Get A	Yes			Need B
3	Need B	No Plan	No			Need F
4	Need F	Acquire Part of F	Partial			Need G
5	Need 1	Get 1	Yes			Need C
6	Need G	Acquire Part of G	Partial
7	Need C	No Plan	No
8	Need 0	No Plan	Yes
Sheet1

Cell Formulas
Range	Formula
F2	=A3
F3	=A4
F4	=A6
F5	=A7

 

[Aladin Akyurek]

Those formulas are misleading. Entering the desired results manually is the right thing to do.

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF(ISNUMBER(MATCH($C$2:$C$8,{"No","Partial"},0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($F$2:F2))),"")

Or perhaps even better:

Contrl+shift+enter and copy down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF(1-($C$2:$C$8="yes"),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($F$2:F2))),"")

 

[clock245]

This is great! Thank you so much for your help!!!

 

[Aladin Akyurek]

This is great! Thank you so much for your help!!!

You are welcome.

 

[clock245]

Hi, i'm sorry to ask again! As you can see from the below, I've added a column that shows percent of problem solved. So that if there are multiple resolutions required to solve one problem then i can see what they are. I tried incorporating an if/sum if statement to try and have the formula consider lines that are less than 100%, but i can't seem to get that to work. If anyone can help that'd be great!!!

Excel 2012
	A	B	C	D	E	F	G
1	Problem	Resolution	% Solved	Resolve Issue			Remaining Problems
2	Need A	Get A	50%	No			Need A
3	Need A	Get A1	50%	Yes			Need F
4	Need B	No Plan	100%	Yes			Need G
5	Need F	Get F	75%	Partial			Need G
6	Need 1	Get 1	100%	Yes			Need C
7	Need G	Get G	35%	Partial
8	Need G	Get G1	30%	Partial
9	Need C	No Plan	0%	No
10	Need 0	No Plan	100%	Yes
Sheet1

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G2))),"")}
G3	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G3))),"")}
G4	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G4))),"")}
G5	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G5))),"")}
G6	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G6))),"")}
G7	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G7))),"")}
G8	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G8))),"")}
G9	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G9))),"")}
G10	{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(1-($D$2:$D$10="yes"),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($G$2:G10))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

What is the desired output using the new criteria? (I'm not asking for what the current formula that we have delivers.)

 

[clock245]

Oops! I've pasted the desired answer below. You'll notice everything that sums to 100% in Column C does not show under the H column as a remaining problem (e.g. "Need A" is resolved through two different resolution efforts at 100% solved---while "Need G" still requires additional resolution at only 65% solved. Hope that makes sense---Thank you so much for your help!

Excel 2012

	A	B	C	D	E	F	G	H
1	Problem	Resolution	% Solved	Resolve Issue		Previous Formula		Remaining Problem (Desired Answer)
2	Need A	Get A	50%	No		Need A		Need F
3	Need A	Get A1	50%	Yes		Need F		Need G
4	Need B	No Plan	100%	Yes		Need G		Need C
5	Need F	Get F	75%	Partial		Need G
6	Need 1	Get 1	100%	Yes		Need C
7	Need G	Get G	35%	Partial
8	Need G	Get G1	30%	Partial
9	Need C	No Plan	0%	No
10	Need 0	No Plan	100%	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[Aladin Akyurek]

Do the criteria Yes/Parial still play a role in computing H2:H4?

 

[clock245]

That is a very good point---i believe Column D is now redundant. Thank you!

 

[Aladin Akyurek]

That is a very good point---i believe Column D is now redundant. Thank you!

Row\Col	A​	B​	C​	D​	E​	F​
1​	Problem	Resolution	% Solved	Completion		Remaining Problem
2​	Need A	Get A	50%​	1​		Need F
3​	Need A	Get A1	50%​			Need G
4​	Need B	No Plan	100%​	1​		Need C
5​	Need F	Get F	75%​	0.75​
6​	Need 1	Get 1	100%​	1​
7​	Need G	Get G	35%​	0.65​
8​	Need G	Get G1	30%​
9​	Need C	No Plan	0%​	0​
10​	Need 0	No Plan	100%​	1​

In D2 just enter and copy down:

=IF(ISNUMBER(MATCH($A2,$A$1:A1,0)),"",SUMIFS($C$2:$C$10,$A$2:$A$10,$A2))

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER($D$2:$D$10),IF($D$2:$D$10 < 1,ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS($F$2:F2))),"")