+ Reply to Thread
Results 1 to 7 of 7

Find numbers in rows (some with multiple numbers) and return header name(s) as answer

Hybrid View

pelbert Find numbers in rows (some... 06-16-2020, 12:31 PM
Fluff13 Re: Find numbers in rows... 06-16-2020, 01:00 PM
ChemistB Re: Find numbers in rows... 06-16-2020, 01:12 PM
Fluff13 Re: Find numbers in rows... 06-16-2020, 01:13 PM
ChemistB Re: Find numbers in rows... 06-16-2020, 01:16 PM
pelbert Re: Find numbers in rows... 06-16-2020, 06:17 PM
Fluff13 Re: Find numbers in rows... 06-17-2020, 07:58 AM
  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Gainesville, FL
    MS-Off Ver
    Microsoft 365
    Posts
    83

    Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    Upper Spreadsheet has columns A thru O. Column D thru O have numbers sporadically listed based on the numbers from Column A.

    A quick explanation is that the numbers in Column A are EPA chemical numbers. Column D thru Column O are states that place the products as restrictive in their uses. So, not all states will have the same restriction for each of the products. So, some states my not have show a number.

    My intention is to have a formula in each cell in Column C that will look across the spreadsheet at each respective row and return to Column C the header state name (CA, CT, MA, MI, etc...), if there is a number listed the cells below it.

    The lower spreadsheet example represents how I would like to have Column C return a response to the formula.

    Hopefully, I have projected my request properly so that it is understood by anyone who can help me with this.

    I have tried Isnumber, isblank and a few other formulas....but nothing came together that would work.

    As always, thank you for taking time to assist me with my questions.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,987

    Re: Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    How about
    IF(D2<>"","Federal",TEXTJOIN(",",1,IF(E2:O2<>"",$E$1:$O$1,"")))

    This needs CSE entry, unless you have dynamic arrays.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    Nice solution but I don't think you need CSE. Works for me without CSE
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,987

    Re: Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    Thanks for that, pretty sure it needs CSE if you do not have the dynamic array functions, but have no way to check.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    Good point, I forgot that this computer has the dynamic arrays. oops

  6. #6
    Registered User
    Join Date
    01-12-2012
    Location
    Gainesville, FL
    MS-Off Ver
    Microsoft 365
    Posts
    83

    Re: Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    Fluff
    I applied the formula you suggested and it worked for me without CSE. I am just blown away by this formula becsause I have not heard of "TextJoin" before and now that this has been brought to my attention, I will look for other ways to incorporate this into other possibilities. I really apprecite your taking time to expose this action and formula to me. This will help solve my question I had for this dilema. Thank you both your attention to details. I really appreciate your great work here.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,987

    Re: Find numbers in rows (some with multiple numbers) and return header name(s) as answer

    Glad we could help & thanks for the feedback.

+ 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. Replies: 5
    Last Post: 09-08-2018, 07:20 PM
  2. Find Max value and return column header, multiple rows
    By Setanta11 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2015, 04:58 AM
  3. Create range of numbers on one row from incrementing numbers on multiple rows
    By mexykanu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2014, 04:40 PM
  4. I need answer to only show positive numbers, not minus numbers or zeros
    By stevedork in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-24-2013, 01:18 PM
  5. Replies: 1
    Last Post: 02-06-2013, 10:32 AM
  6. Using multiple numbers to display one answer in one cell
    By fairru in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-11-2012, 11:16 PM
  7. How to find a series of numbers in multiple rows
    By ttsteve in forum Excel General
    Replies: 2
    Last Post: 05-17-2011, 02:20 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