+ Reply to Thread
Results 1 to 11 of 11

Extract values in Column E into Column F

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Extract values in Column E into Column F

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Extract values in Column E into Column F

    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.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Extract values in Column E into Column F

    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)))

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Extract values in Column E into Column F

    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....

  5. #5
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values in Column E into Column F

    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.

  6. #6
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values in Column E into Column F

    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.

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Extract values in Column E into Column F

    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
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values in Column E into Column F

    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.

  9. #9
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values in Column E into Column F

    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.

  10. #10
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Extract values in Column E into Column F

    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

    Please Login or Register  to view this content.
    Regards

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract values in Column E into Column F

    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))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract Values in Column S into Column R (Desired Output Column)
    By bjnockle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2020, 01:12 PM
  2. [SOLVED] Partial Search to Extract values in a Table (column A2:C142) into column K2:L24
    By bjnockle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-10-2020, 03:45 PM
  3. [SOLVED] Formula Required to Extract data from Column A based on Non -zero values in Column B
    By Ramzan-ul-Mubarak in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-29-2019, 06:07 AM
  4. Extract list of values from one column based on values in another column
    By The Speculator in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-21-2019, 11:22 PM
  5. [SOLVED] How to extract values in one column based on criteria in another column
    By TimLarson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-27-2019, 07:06 PM
  6. Replies: 2
    Last Post: 10-05-2018, 08:28 AM
  7. Replies: 3
    Last Post: 02-14-2013, 10:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1