+ Reply to Thread
Results 1 to 6 of 6

Extract Values in Column C into Column J

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

    Extract Values in Column C into Column J

    JeteMc: I agree with you in that I did not include details in my original post. I am providing details here to avoid my initial pitfall.

    The sample file works this way:

    Note: Only need the formula into column J fixed as I was able to create three helper columns to extract desired values in column D into column I.

    Part A: Values in column C are manually entered in cell A2. (black column and text appear in white color)
    For example, if you enter the two letters AA (C3= AA21259159) in A2, Tbra, Cable is automatically pulled into I2 since that is the only AA in column E.

    Part B: Looking for formula to extract the value (in column D) matching Tbra, Cable in column I into column J. For the sample in Part A, the formula should return AA21259159

    Example II:
    if you enter the two letters TA (C2= TA32785655,C31=TA53245548, C32 = TA13291311) in A2, Tbnor M., Aadigan, Tbnor, Aadigan, and Tbnor, Aadigan
    are automatically pulled into I2:I4 since those are the ones with TAs in column E.

    Example III:
    Example II:
    if you enter the three values TA3 (C2= TA32785655) in A2, Tbnor M., Aadigan is automatically pulled into I2 since it is the only TA3 in column E.

    See sample file.

    Thanks.
    Attached Files Attached Files
    Last edited by bjnockle; 01-18-2021 at 05:11 AM.

  2. #2
    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 D and B into Column E and Column F

    Please try at
    E2
    =IF(ROWS(E$2:E2)>COUNTIFS($D$1:$D$99,$D$2),"",$D$2)

    where D2 is the desired Name.

    F2
    =IF(E2="","",INDEX($B$1:$B$99,AGGREGATE(15,6,ROW($D$1:$D$99)/($D$1:$D$99=E2),ROWS(E$2:E2))))
    Attached Files Attached Files

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

    Re: Extract Values in Column D and B into Column E and Column F

    Bo_Ry: When I added middle initial to the first name in D2, the formula is not pulling correctly. Please review and help fix. See attached sample file.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Extract Values in Column D and Column B into Column E and Column F

    Try the following:
    In E2 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In F2 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For future reference please attempt to include details, such as some records having middle initials, in the inceptive request.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

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

    Re: Extract Values in Column D and Column B into Column E and Column F

    JeteMc: I agree with you in that I did not include details in my original post. I am providing details here to avoid my initial pitfall.

    The sample file works this way:

    Note: Only need the formula into column J fixed as I was able to create three helper columns to extract desired values in column D into column I.

    Part A: Values in column C are manually entered in cell A2. (black column and text appear in white color)
    For example, if you enter the two letters AA (C3= AA21259159) in A2, Tbra, Cable is automatically pulled into I2 since that is the only AA in column E.

    Part B: Looking for formula to extract the value (in column D) matching Tbra, Cable in column I into column J. For the sample in Part A, the formula should return AA21259159

    Example II:
    if you enter the two letters TA (C2= TA32785655,C31=TA53245548, C32 = TA13291311) in A2, Tbnor M., Aadigan, Tbnor, Aadigan, and Tbnor, Aadigan
    are automatically pulled into I2:I4 since those are the ones with TAs in column E.

    Example III:
    Example II:
    if you enter the three values TA3 (C2= TA32785655) in A2, Tbnor M., Aadigan is automatically pulled into I2 since it is the only TA3 in column E.

    See sample file.

    Thanks.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Extract Values in Column C into Column J

    Try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ 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. [SOLVED] Extract Data in Column B and Column C Into Column E and F Based on Condition in Column D
    By bjnockle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2021, 12:18 PM
  2. 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
  3. [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
  4. [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
  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