yogi_If A Name In Column A Appears More Than A Specified Number Of Times Replace It With Another String

Google Spreadsheet   Post  #2395

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

question by: Mick Stringfellow
https://productforums.google.com/forum/#!topic/docs/TxgHh6vAddo;context-place=forum/docs
How to replace data if it's repeated 3 times in a column
Hi all,

Not sure if this is do-able.

I have a column of data with people names. If a name is repeated 3 times, i need the lowest name in the column to be changed to another word.
Also,there may be more than one word which is repeated 3 times

This link is an editable example.  I'm not sure if this is possible but hope it is.

Thank you in advance.

I'm out for a couple of hours so apologies if my reply is delayed.

Mick

yogi_Sum And Average Times In Hours Minutes Seconds and MiliSeconds

Google Spreadsheet   Post  #2394

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

question by: Mark Hephner
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/oWMZ01EU8X8;context-place=mydiscussions
I have a sheet that I want to start with a time 8:45 am and add lap times

Trying to find a way to add lap times to a start time to predict a finish time

Also trying to get average lap times as well.

https://drive.google.com/open?id=1zPhwg41PuUhOYf0V02gHRC5ATNm6EbSJiU-mEzxKgLA

yogi_Multi Criteria Count Including WeekNum Given A Column of Dates

Google Spreadsheet   Post  #2393

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

question by: CosM
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/tC4zy_LfAuk;context-place=forum/docs
COUNTIFS by a specific week of the year
I have a spreadsheet which includes 2 thousand rows of data and several columns one of which is date (column E)

I am successfully performing COUNTIFS on my current data and now want to include an additional criteria to include only rows of data in week 10 of the year. So for example my current query is

=COUNTIFS(C2:C2000, "Yes", D2:D2000, "Green")

I have tried unsuccessfully to include a date range too using WEEKNUM

=COUNTIFS(C2:C2000, "Yes", D2:D2000, "Green", WEEKNUM(E2:D2000,2), 10) [where week 10 is the week range I want to filter by)

Any advice is much appreciated

yogi_Multiple Criteria Match In Two Sheets Within A Spreadsheet (WorkBook)

Google Spreadsheet   Post  #2392

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

question by: Rob N.
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/CUD_tKZ1uFg;context-place=forum/docs
Help with an "If formula" with multiple conditions

I have account numbers that are likely to appear in two distinct columns with the same header ("Accounts") located in two separate tabs ("Main" & "Stock"). 

I need help with a formula that would return a specific text ("System") only IF the following conditions are met;

1. Two account numbers under columns B (in separate tabs) are matching; 

2. The adjacent cell ("Main" tab, Column C-"Activity) to the matching account number must contain one of these values: "Repo WL" or "Sold".

A link to the sample data : https://docs.google.com/spreadsheets/d/1KQfCQypF56cAOBtRwyMoefqrzOKwUrOnQt3twyKXlpI/edit?usp=sharing

Thank you for your help.

yogi_For Specified Student By Name Pull Concatenated Column Headers For Points 1 and 0 Separated By ;

Google Spreadsheet   Post  #2391

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

question by: Mr. Clinton
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/UTUHEBTxZB0;context-place=mydiscussions
How to return all column headers that match column A, and match criteria in that row

Hi,

   I'm a teacher trying desperately to automate my heavy workload (caused by the halving or our staff and the remaining of us bearing the workload of the lost teachers in addition to our own....)

I need to be able to look up students on my grading sheets and return the column headers of each instance of the occurrence of both a number >0 in that student's row, and also  a number <0.  These should be joined together in a cell, separated by "; ".  

**I need to return any column headers in row 2 by looking up a student in col A and then matching entries in that student's row that are  "0" (with those column headers returned in the "Negative Points" box, combined and separated with a semicolon and a space) and any entries that are not blank and greater than "0" (with those column headers returned in the "Positive Points" box, combined and separated with a semicolon and a space)**  This will eventually be expanded to look up each student this way and return what categories they earned positive and negative points in respectively....

I am doing this so that I can add to my system that sends each student/parent a rundown of what was worked on in each lesson every week, as well as how the student did.  This system already works, but only sends total points, not where positive or negative points were earned.  I really need to provide my students and parents more specific info.

Thanks for your help!!!

Here's a link to my testing sheet:

https://docs.google.com/spreadsheets/d/1FjFWiC1C44YjUfkGQsIdzVQJgqFM4nYN-Vg5IzH9t4I/edit?usp=sharing

yogi_Parsing hierarchical data to parent/child "indented" two (2) column output

Google Spreadsheet   Post  #2390

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

question by: Andon Sawyer
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/D6MKz8OAaTk/2op_Jw3EBgAJ
Parsing hierarchical data to parent/child "indented" two (2) column output, help please :)

Trying to figure out a way to parse these two columns below... (there are a few hundred rows like this)

https://docs.google.com/spreadsheets/d/1K8OWHk9dPpkzqybzGX115O1_5kpHKZWyY6EhcusP57A/edit?usp=sharing

This is how I am structuring the hierarchy. Maybe I'm wrong to do it this way?

Kit Part	Components
6172DF-A0-P2-MR-BH	6172DFA
6172DF-A0-P2-MR-BH	6172DF0
6172DF-A0-P2-MR-BH	2DFP2
6172DF-A0-P2-MR-BH	6172DFBH
6172DF-A0-P2-MR-BH	2DFMR
6172DF-A0-P2-MD-BH	6172DFA
6172DF-A0-P2-MD-BH	6172DF0
6172DF-A0-P2-MD-BH	2DFP2
6172DF-A0-P2-MD-BH	6172DFBH
6172DF-A0-P2-MD-BH	2DFMD

So I parsed/grouped it to this using formulas thinking it would get me closer to my goal.

UNIQUE(A2:A)	JOIN(",",QUERY($A$2:$B,"Select B where A = '"&E2&"'",0))
6172DF-A0-P2-MR-BH	6172DFA,6172DF0,2DFP2,6172DFBH,2DFMR
6172DF-A0-P2-MD-BH	6172DFA,6172DF0,2DFP2,6172DFBH,2DFMD
6172DF-A0-P2-MH-BH	6172DFA,6172DF0,2DFP2,6172DFBH,2DFMH

which is this :

DESIRED OUTPUT
6172DF-A0-P2-MH-BH
	6172DFA
	6172DF0
	2DFP2
	6172DFBH
	2DFMH
6172DF-A0-P2-MD-BH
	6172DFA
	6172DF0
	2DFP2
	6172DFBH
	2DFMD
ETC ETC...

I have searched and tried all sorts of query, arrayformula, split, joins, transpose and filters... but I am at a loss.

Maybe it just isn't so easy, and maybe I've hit a wall.

Any input would be graciously received.

Cheers

yogi_Compute currency exchange rate for each date noted in column A

Google Spreadsheet   Post  #2389

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

question by: Ged Vengauskas
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/9kfoanQVNJQ;context-place=forum/docs
How to list currency exchange rates for each day on Google Spreadsheet?

I would like to know if it is possible to get currency exchange rates for each past day on Google Spreadsheets. 

Currently I have the following formula (Column B contains all the dates that had cash flow): =GOOGLEFINANCE("CURRENCY:EURGBP", "price", B13, B13+100, "DAILY")

The drawback with this formula is that even though it starts with the first given date it disregards that some of the dates on my spreadsheet appear more than once or that some of the dates are not in subsequent order and simply lists new exchange rates in a regular order. With that said I want the formula to follow the dates on my spreadsheet. For example if I would have 30/01/2018 a few times in a row in Column B I would like the formula to give the same exchange rate in each row as long as the date remains the same.

I have also tried this formula:
=GOOGLEFINANCE("CURRENCY:EURGBP", "price", B13:B100, "DAILY")
However the spreadsheet is giving an error.