Excel Formula

[hrayani]

Hi,

I need an excel formula to show a list if cells in column C is blank..

Kindly have a look below. Formula required starting from cell A14

Mr Excel Query.xlsx
	A	B	C
1	Sample DATA
2	PO #	Target Date	Actual Date
3	510	1-Jan
4	509	1-Jan	5-Jan
5	501	1-Jan
6	825	2-Jan
7	511	5-Jan
8	512	5-Jan
9	498	6-Jan	4-Jan
10	123	3-Jan
11
12	Pending List
13	PO #	Target Date	Actual Date
14	510	1-Jan
15	501	1-Jan
16	825	2-Jan
17	511	5-Jan
18	512	5-Jan
19	123	3-Jan
Sheet1

Cell Formulas
Range	Formula
B14:B19	B14	=VLOOKUP(A14,$A$2:$C$10,2,FALSE)

Any help would be appreciated

Regards,

Humayun

 

[Fluff]

How about
=INDEX($A$3:$A$10,AGGREGATE(15,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($C$3:$C$10=""),ROWS(A$14:A14)))

 

[hrayani]

Thanks Fluff,

as always you are there

Please check this... I have named the ranges A3:A10 > po_no & C3:C10 > actual_date

=IFERROR(INDEX(po_no,AGGREGATE(15,6,(ROW(po_no)-ROW($A$3)+1)/(actual_date=""),ROW(1:1))),"")

Although the formula is working perfect... I would like you to have a look at the bold part...

You have used A2:A10 ... I have changed it to A3:A10 > po_no
next
Row($A$2) is changed to Row($A$3)

are the changes going to be fine or is there anything I have done wrong....... as of now working Perfect though

 

[hrayani]

Sorry My Bad....

The list needs to be generated if the target date is there and actual date is not there

 

[Fluff]

Those changes are correct, it was a typo on my part, but you need to leave the last part as it was.
=IFERROR(INDEX(po_no,AGGREGATE(15,6,(ROW(po_no)-ROW($A$3)+1)/(actual_date=""),ROWS(A$14:A14))),"")

 

[Fluff]

To check if there is a target date, use
=IFERROR(INDEX(po_no,AGGREGATE(15,6,(ROW(po_no)-ROW($A$3)+1)/((actual_date="")*($B$3:$B$10<>"")),ROWS(A$14:A14))),"")

 

[hrayani]

Many Thanks for you help Fluff,

Works like a charm now

But please educate me..... why we should not change the last part

ROWS(A$14:A14) > gives 1 & ROW(1:1) also gives one

and if I drag the formula down both giving the same numbers .....

 

[Fluff]

If the formula is in A14 & you use ROW(1:1) then if you insert a new row in row1 your formula will break.
Notice the difference between col A & C

+Fluff New.xlsm
	A	B	C
1
2	Sample DATA
3	PO #	Target Date	Actual Date
4	510	01/01/2020
5	509	01/01/2020	05/01/2020
6	501	01/01/2020
7	825	02/01/2020
8	511	05/01/2020
9	512	05/01/2020
10	498	06/01/2020	04/01/2020
11	123	06/01/2020
12
13	Pending List
14	PO #	Target Date	Actual Date
15	510	01/01/2020	501
16	501	01/01/2020	825
17	825	02/01/2020	511
18	511	05/01/2020	512
19	512	05/01/2020	123
20	123	06/01/2020
Main

Cell Formulas
Range	Formula
A15:A20	A15	=IFERROR(INDEX(po_no,AGGREGATE(15,6,(ROW(po_no)-ROW($A$4)+1)/((actual_date="")*($B$4:$B$11<>"")),ROWS(A$15:A15))),"")
B15:B20	B15	=VLOOKUP(A15,$A$3:$C$11,2,FALSE)
C15:C20	C15	=IFERROR(INDEX(po_no,AGGREGATE(15,6,(ROW(po_no)-ROW($A$4)+1)/((actual_date="")*($B$4:$B$11<>"")),ROW(2:2))),"")

 

[hrayani]

Thanks once again....

The concept is very clear now

 

[hrayani]

Hi,

One last question..... Can we ignore the repeated PO # .... can we get the unique list...