+ Reply to Thread
Results 1 to 7 of 7

Find number in row and display number and column title

  1. #1
    Registered User
    Join Date
    12-23-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2011
    Posts
    4

    Find number in row and display number and column title

    Hi!

    I have a spreadsheet with one customer per row and a large number of columns containing a variety of numbers representing the number of orders for certain parts. A large number of the columns are blank. I'm trying to create a field to summarize all orders for each customer to make it easier to see exactly which each customer has ordered without having to scroll horizontally.

    Is there any way to search each row for numbers, display the numbers found followed by the respective cell column heading?

    Example:

    A B C D
    1 Tires Oilfilters Brakepads
    2 Joe Johnsson 1 0 3

    In the above example I would like the cell in B2 to display 1 Tires, 3 Brakepads. Is there any way to search the row and display the found numbers with the respective column headings in this way? Thanks so much!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find number in row and display number and column title

    I'm not sure about how to do this with formulas, I think if you have the morefunc add in you can do it using mconcat but it is a simple task with a user-defined-function.

    Press Alt+F11 to open the vba editor, click insert->module and then paste this code:
    Please Login or Register  to view this content.
    In B2 enter the formula:
    =OrderConCat(C2:E2)

  3. #3
    Registered User
    Join Date
    12-23-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Find number in row and display number and column title

    Thanks so much for your answer! Unfortunately I'm using a mac and it looks like morefunc only works on windows. Are you aware of a mac-compatible solution? Maybe it's possible to create a solution using vba?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find number in row and display number and column title

    Try the solution I gave you in post 2, it is a vba solution and doesn't use morefunc.

  5. #5
    Registered User
    Join Date
    12-23-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Find number in row and display number and column title

    Ah right, sorry. That actually works perfectly! However, it only works on the first row since the offset is -1. I have 100+ rows with customers and when I copy the formula down to the rest I'm getting the values from the row above instead of the very top row. Is there any way to lock the first row heading in the formula so it won't change when I copy the formula down to the other rows? Thanks!

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find number in row and display number and column title

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-23-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Find number in row and display number and column title

    Bravo! THANKS for the great answer. Worked perfectly!

+ 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: 1
    Last Post: 06-10-2013, 07:08 PM
  2. Find highest revision number in one column of a item number in another column
    By Tasiast in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-01-2013, 01:03 PM
  3. Replies: 4
    Last Post: 10-19-2012, 12:09 PM
  4. Find unique number in columns and return column title
    By mkvassh in forum Excel General
    Replies: 3
    Last Post: 10-21-2010, 10:29 AM
  5. Replies: 7
    Last Post: 12-18-2008, 07:34 PM

Tags for this Thread

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