+ Reply to Thread
Results 1 to 11 of 11

Help checking if a line row of cells has been done before

  1. #1
    Registered User
    Join Date
    04-22-2014
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    Help checking if a line row of cells has been done before

    Hello People.

    I am conducting experiments in the real world with spices for a chicken rub.

    I would like to write out a spreadsheet that checks to see if I have done the particular formula before so that I don't waste my time cooking chicken wings that I have already tried.

    Here is a copy of my sheet,
    formula_checker.xlsx
    I have represented each herb and spice with letters instead but as you can see from the spreadsheet I have in recipe number 1, 1qty of A, 0.5qty of B, etc etc.

    What I want to do, is that the cells in column L, check to see if any of the rows of figures are the same as any other of the rows, and if so, highlight it in red for example. See here how cell L6 is RED, because the row 6 is the same as row 2 (cells B2 to K2) and (cells B5 to K5) are identical.

    Please can someone tell me what the formula should be in my column L? To do what I require?

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Help checking if a line row of cells has been done before

    Please see attached file.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Help checking if a line row of cells has been done before

    I can't believe this works but it seems that way. It gives the number of the latest equal recipe.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Jacc; 04-27-2014 at 02:26 PM.

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Help checking if a line row of cells has been done before

    This looks slightly better:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Registered User
    Join Date
    04-22-2014
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Help checking if a line row of cells has been done before

    Thanks AlKey, that works great.

    Jacc, I couldn't get yours to work, but thanks for your efforts, I have added to both of your reputation for trying, I will now mark this thread as solved, you're stars.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Help checking if a line row of cells has been done before

    You're welcome and thank you for the feedback!

  7. #7
    Registered User
    Join Date
    04-22-2014
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Help checking if a line row of cells has been done before

    formula1.xlsxHere is my real world spreadsheet, I have tried and tried to get it to work over the whole range but keep messing up some how. Please could you update the formula so that it works throughout the whole range? I've been at it hours and don't have much hair left.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Help checking if a line row of cells has been done before

    Have you worked with array formulas before? They have to be entered with Ctrl + Shift + Enter. A lazy mans way of expanding the number of columns in this array formula is to select the rightmost column and then hit Ctrl + "+" (or just right click and insert column). As long as you are inside the range the formula will just expand automatically.

    Anyway, attached is your worksheet with my formula implemented, hopefully that will help.
    Attached Files Attached Files

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Help checking if a line row of cells has been done before

    Please see attached Macro-Enabled file. This is a modified version that uses vb UDF (User Defined Function) to concatenate range.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-22-2014
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Help checking if a line row of cells has been done before

    Thanks again guys, it's working a treat now.

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Help checking if a line row of cells has been done before

    I think you were in too much hurry there, AlKey. Your UDF will give the same results for 5 ones in the leftmost columns as 5 ones in the rightmost columns which will give a false yes.

    I just saw a small error in the workbook I just posted. It says in the title of the formula that it gives the row nr of the equal recipe. That is not true, it gives the recipe nr. This can be adjusted either way of course.
    Last edited by Jacc; 04-28-2014 at 02:00 PM.

+ 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] read pdf or doc file line by line and then parse the data into the proper cells and rows.
    By rtphilli in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-04-2014, 03:33 AM
  2. Replies: 7
    Last Post: 04-23-2014, 03:05 PM
  3. Line Chart Question - break in line for blank cells
    By JonPugh in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 12-09-2009, 09:39 AM
  4. Checking the Cells in Sheet1 with Cells in Sheet2 and replace
    By Checking the cells in Sheet1 with Sheet2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-19-2006, 04:29 AM
  5. [SOLVED] Checking names on correct line across sheets
    By Ali in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-17-2006, 03: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