Finding and Highlighting Data in Google Sheets

sime

Member
SoSH Member
Oct 19, 2011
174
Hey folks,
I work with a local delivery company and put together their weekly orders and delivery routes.
Every week I have a spreadsheet with order information which includes, amongst other things, Order Numbers and Tips.
I divvy the orders up to our delivery people based on location.

I am looking for a formula that will find and highlight a specified group of order numbers that are in the spreadsheet.

For example, today we have 150 orders and 40 of those orders are going to Isaac. Is there a way I can paste in the list of Isaac's order numbers and have Google Sheets highlight the rows that these numbers appear in?

I have attached a screenshot of the relevant part of the spreadsheet. Let's say I want to highlight orders 18330, 18784 and 18939.

Any ideas?
Thanks in advance.
 

Attachments

begranter

Couldn't get into a real school
Silver Supporter
SoSH Member
Jul 9, 2007
2,233
Put the list of Isaac's order numbers on another tab, write "Isaac" in the column to the right (better yet use this for all your orders and do all your employees at once -- if you have a master sheet then this would take just about a minute), and do a =vlookup(A2,Range of Isaac's Orders and next column,2,false) in an inserted column B in the screenshot page. Then you can put it into a pivot table so that you can have, without filtering or anything:

Isaac: List of orders and tips etc
Employee 2: List etc
...etc
 

cgori

Member
SoSH Member
Oct 2, 2004
2,897
SF, CA
My excel/sheets-fu is terrible, so the only way I can think of is:

1) I assume there is another sheet (?) where each row has an order number and an associated person ("Isaac" in your example) - or maybe it's a column in this sheet you screenshotted?
2) In this tips sheet add another column that does a VLOOKUP into that other sheet to pull the associated person into this one
3) Sort on the newly added column making all the Isaac's cluster together

If you need step 3 to be automatic, there is a way to conditionally format a range using the sheets API. You need to do the sorting though because ranges must be contiguous.

What you are trying to do is like reason #1001 of why Excel/Sheets are not a database (in a database, you have things like "SELECT * FROM Orders WHERE Person='Isaac'" to get exactly what you want as a report)

EDIT: well, there you go, I had half of it w/ VLOOKUP. And then use a pivot table instead of sort/filter/conditionally format