help with formula

[p4nny]

Hi

Here's what my dataset looks like:

A B C
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area1[/TD]
[TD]Customer[/TD]
[TD]Test1[/TD]
[/TR]
[TR]
[TD]Area2[/TD]
[TD]Customer[/TD]
[TD]Test2[/TD]
[/TR]
[TR]
[TD]Area3[/TD]
[TD]Customer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area4[/TD]
[TD]Customer[/TD]
[TD]Test4[/TD]
[/TR]
</tbody>[/TABLE]


In column D, I would like to show Test 1 in D1, Test 2 in D2, Test 4 in D3. Cell D4 would show as a blank

Any cell within column C could be blank.

would appreciate any help.

Many thanks

 

[anglais428]

Assuming you have shown us columns A B and C, what is your rational upon which you want to determine what goes into Column D?

 

[FormR]

In column D, I would like to show Test 1 in D1, Test 2 in D2, Test 4 in D3. Cell D4 would show as a blank

Hi, if I've understood correctly then here is one option, note the helper formula in D1 which is there to aid performance.

Excel 2013/2016
	A	B	C	D
1				3
2	Area1	Customer	Test1	Test1
3	Area2	Customer	Test2	Test2
4	Area3	Customer		Test4
5	Area4	Customer	Test4
Sheet1

Cell Formulas
Range	Formula
D1	=COUNTIFS($C$2:$C$1000,"?*")
D2	=IF(ROWS(D$2:D2)>$D$1,"",INDEX($C$2:$C$1000,AGGREGATE(15,6,(ROW($C$2:$C$1000)-ROW($C$2)+1)/(LEN($C$2:$C$1000)>0),ROWS(D$2:D2))))

 

[p4nny]

Works perfect, thank you. When you have a spare moment, please could you explain how this formula works?

 

[FormR]

Works perfect, thank you.

Great, that's good to hear.

could you explain how this formula works?

A good place to start would be to limit the ranges of the formula to just a few rows and use the "evaluate formula" option on the "formulas" tab to step through the calculations and follow up with the built in help for each of the functions involved.