+ Reply to Thread
Results 1 to 13 of 13

Quick way to find and highlight multiple rows?

  1. #1
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Exclamation Quick way to find and highlight multiple rows?

    I have a very large spreadsheet (46,000 rows). There are a couple hundred rows I need to find and highlight, and also insert potentially-linked contract numbers in a second column.

    For example:

    Column 1: Column 2:
    Contract # Linked Contract #
    12345 67890



    Is there a quick way to do this without using find and replace to highlight all several hundred of these rows? I have an hour and a half until I leave work and my boss wants it today! Compounded is the problem that there are about 100 versions of this spreadsheet I need to do this for.


    Thanks for your help

  2. #2
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Quick way to find and highlight multiple rows?

    Oh yes, and I should say:

    I currently have Contract # with data filled in, then the next column is "Linked Contract #" which is blank, then there are about a dozen columns after that with data that's already fine the way it is.

    I'm looking to highlight each of the rows with a value in the potentially-linked contract field in yellow and also insert the linked contract # in the linked contract # column. Some of the contracts have more than one potentially linked contract.

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Quick way to find and highlight multiple rows?

    For finding and highlighting - use autofilter.

    For inserting the 'potentially linked contracts' - impossible to say: how are you identifying these?

    Edit: just saw your second post. Can you upload a sample workbook, with examples of both the data you have, and what want it to look like?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Quick way to find and highlight multiple rows?

    I made a sample spreadsheet but I'm not seeing how I can upload it here?

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Quick way to find and highlight multiple rows?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Quick way to find and highlight multiple rows?

    Here is a sample.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    REAL sample

    I apologize- I sent a bad sample. THIS is the actual sample.
    Attached Files Attached Files

  8. #8
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Quick way to find and highlight multiple rows?

    You can easily highlight yellow all cells with a value in column B, by using conditional formatting:

    Select your range of data, click Conditional Formatting > New Rule > use a formula... and enter the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (note - change that row number to the first row of your selected range) - then choose formatting to fill yellow.

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Quick way to find and highlight multiple rows?

    Okay, looking at REAL sample...

    What rules determine which contract number should go into column B? IS this something that can be automated in Excel, or is it just business knowledge? Is there a data source for the links?

  10. #10
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Quick way to find and highlight multiple rows?

    I have a list of contract numbers with a list of linked contract numbers. I put them in a spreadsheet that looks simple

    Row 1 Row 2
    Contract # Linked Contract Number(s)

    As to how the data was selected, my manager gave me this list of contracts he wants highlighted-there are various reasons he wanted these contracts highlighted, and so there wouldn't be a way to automate it. He basically just knew something was "up" with these plans and wants to make them stand out in the 46,000 plan spreadsheet so the plan managers can easily see what plan participants they need to go talk to first.


    I.e.

    Row 1 Row 2
    Contract ABC Linked Contract DEF

  11. #11
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Quick way to find and highlight multiple rows?

    You're really not making this easy to help you. How about including some of that data / structure in the workbook you share with us?

    A VLOOKUP formula in column B (of the sheet you've shared) which looks up the data with the linked contracts (which you haven't shared) should retrieve the linked contracts. The conditional formatting (as I mentioned earlier) should take care of the highlighting.

  12. #12
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Quick way to find and highlight multiple rows?

    I did include the data. I have a spreadsheet without anything in column B (linked contracts). It looks just like the REALsample version I attached. Row 1: Contract #. Row 2: Heading is "linked contracts," but the contract number is blank. The rest of the rows are various data about the plans, and that doesn't matter to what I need to do here so I didn't include it.

    There are no values in column B (linked contracts) now.

    Column B (linked contracts) needs to take data from a second spreadsheet where have only two rows:

    Row 1: Contract number
    Row 2: linked contract number


    Row 2 (linked contract number) is what needs to go in Row 2 (linked contract number) of the main spreadsheet with all the data (which looks like the sample I attached).

    I'm not sure how else to explain it.

    I have to leave now to get ready for Shabbos. I'll check this again Sunday or Monday. I'm sorry I'm not making sense; I thought the sample spreadsheet I made was logical.

    And as to highlighting all cells in a field that have data, that doesn't apply, because right now the data isn't in that column- it's only on the separate list (in spreadsheet form) that has Row 1: contract number and row 2: linked contract number

    Thanks for helping. hope that makes more sense.

  13. #13
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Quick way to find and highlight multiple rows?

    I would show you the exact VLOOKUP to retrieve the matching contract numbers. But I can't as I don't have that data...

    Anyway. I've pointed you at the tools to do what you need. I'm sure you'll figure it.

+ 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. Find and highlight two opposite numbers by multiple criteria(A+B=0)
    By bumbar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-26-2017, 02:18 AM
  2. Replies: 2
    Last Post: 01-29-2013, 10:41 AM
  3. [SOLVED] Using Cell Find in VBA highlight multiple Row
    By evosurf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2013, 12:12 AM
  4. Highlight multiple rows
    By ceodelmundo in forum Excel General
    Replies: 4
    Last Post: 12-16-2009, 11:25 PM
  5. Highlight Multiple Rows
    By av3ng3r85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-14-2008, 02:04 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