Monday, December 31, 2018

yogi_Conditionally Format matching and Non_Matching Entries In Columns A and B

Google Spreadsheet   Post  #2559

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2018

question by: Ari R:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/DA2kzqnBsF4;context-place=mydiscussions
Google Sheets matching entries highlighted
I need all matches in Columns A & B to be highlighted in green and which don't match to be highlighted in Red.

yogi_Pull Hours data from Column G of spreadsheet 'Schedule' matching it with 'Date' 'Provider' 'Start Time' and 'Start Location

Google Spreadsheet   Post  #2558

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2018

question by: NJP2017:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/LJDMT51jGWY;context-place=mydiscussions
Can I do a multi paramater index match with one parameter searching a range of values?
I'm not sure if what I am trying to do is even possible but I thought I would bring it here in case anyone can help. I an working with two different Google Sheets documents. One is Audit (https://docs.google.com/spreadsheets/d/1VCuhiYd01pgQKYWWTbXo5mYxW4wsFGe66Wf9jN8O4ng/edit#gid=0) and the other is Schedule (https://docs.google.com/spreadsheets/d/1p65lN0FzxrscEAAEmb_Ohga-XgMSSWXirts5UI1LgRU/edit#gid=0).

I am trying to pull the hours data from the Schedule doc (column G) and match it with the date, provider, start time and start location. However, the start location names aren't consistent so I have the alternate names on the Start Location tab of the Audit sheet. I was trying to just do the range part using another help page (https://stackoverflow.com/questions/14803944/excel-searching-for-multiple-terms-in-a-cell) but I couldn't even get that part to work.

Is there a way to do this or a better method anyone would suggest without altering the data (which I can't on the actual documents).

Sunday, December 30, 2018

yogi_Count PostCodes Based On Just The First Letters

Google Spreadsheet   Post  #2557

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-30-2018

question by: Jack Supertramp102:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/zoul4JWWb2c;context-place=mydiscussions
How do i count postcodes based on just the first letters issue
Hi Everyonre,

I have a spreadsheet where i log postcodes at certain times. I need the spreadsheet to count the post code types based on the first letters of the postcode. It does this already but the issue is if i have a postcode in my log L6 and also LL6,, the spreadsheet thinks there is 2 L postcodes. Another example is i have postcode SK7 and S3. The spreadsheet thinks there is 2 S postcodes because they both start with S. I need each postcode type to be counted seperately without this issue.

Spreadsheet sample link below:

Hope you can help,

Kind Regards,
Jack

Friday, December 28, 2018

yogi_Pull Heater On/Off Data From Sheet1 And Compute Heater On Duration In Minutes

Google Spreadsheet   Post  #2556

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-28-2018

question by: Mark Allenspach:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/A2xTirWzmx4;context-place=mydiscussions

Removing bad data

I have various heater elements that collect data in a google sheet to date/time stamp when the heaters turn on/off.


Normally, the data will show the first time stamp when the heater turned on followed by the next time stamp when it turned off.

Per the snapshot, sometimes I get bad data and have several time stamps in sequence all saying the heater turned on (or off).

When there is such erroneous data, I would like to only keep the 1st time stamp for "ON" and the last time stamp for "OFF".
Capture.JPG
92 KB



Sunday, December 23, 2018

yogi_Conditionally Format Lowest Number in Columns D:Z

Google Spreadsheet   Post  #2555

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-23-2018

question by: topdog2009:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sHFjwihqUK4; context-place=forum/docs

I have a series of columns say D:K
and a series of rows with numbers say 4:23

Each column of numbers is unique.
I want to highlight the lowest number in each column providing it is not blank or zero.

Additionally, the number of columns may increase say to Z

Conditional formatting.