1. Looking for a formula to extract values in Column E into Column F.
2. If there is no value in column D (Sales Person), the formula should return blank in Column F
See sample file.
1. Looking for a formula to extract values in Column E into Column F.
2. If there is no value in column D (Sales Person), the formula should return blank in Column F
See sample file.
Hi
a first attempt, in F2 to be copied down
=IF(D2>"",E2,D2)
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi
please try this in F2 and down:
=IF(D2="","",INDEX($E$1:$E$30,AGGREGATE(14,6,(($E$2:E2)<>"")*($D2=D2:D2)*(ROW($D$2:D2)),1)))
bjnockle,
you should have also added that not only that you want an empty cell where D is empty, but you also want in column F to return the last available year if column E is empty.
That's what I inferred from the results you have entered manually, please correct me if I'm wrong....
belinda200
you should have also added that not only that you want an empty cell where D is empty, but you also want in column F to return the last available year if column E is empty. Yes, you are right.
That's what I inferred from the results you have entered manually, please correct me if I'm wrong.... Yes, you are correct.
canapone: did not work correctly.
belinda200
you should have also added that not only that you want an empty cell where D is empty, but you also want in column F to return the last available year if column E is empty. Yes, you are right.
That's what I inferred from the results you have entered manually, please correct me if I'm wrong.... Yes, you are correct.
Hi,
I did not read correctly yellow numbers as expected results:
formula from Belinda
=IF(D2="","",INDEX($E$1:$E$30,AGGREGATE(14,6,(($E$2:E2)<>"")*($D2=D2:D2)*(ROW($D$2:D2)),1)))
works like a charm
Same numbers from
=IF(D2="",D2,AGGREGATE(14,6,E$2:E2/(D$2:D2=D2),1))
Regards
canapone: Formula works like a charm but it forcing my workbook to close abruptly because I have 52,000 data sets. I think the formula is heavy on calculation. Any fix?
Thanks.
belinda200: Formula works like a charm but it forcing my workbook to close abruptly because I have 52,000 data sets. I think the formula is heavy on calculation. Any fix?
Thanks.
Hi,
again MAX under condition (using AGGREGATE,14...)
The following formula -after 50h rows- processes a smaller range: only the previous 50 rows.
In F2
RegardsPlease Login or Register to view this content.
Please try at F2
=IF(D2="","",LOOKUP(2,1/(D$2:D2=D2)/(E$2:E2<>""),E$2:E2))
or faster with less array calculation with more data
=IF(D2="","",LOOKUP(2,1/(INDEX(D$2:D2,MATCH(D2,D$2:D2,)):D2=D2)/(INDEX(E$2:E2,MATCH(D2,D$2:D2,)):E2<>""),INDEX(E$2:E2,MATCH(D2,D$2:D2,)):E2))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks