yogi_From Table of Times Course Names And Attendees Pull Times And Course Names For Specified Attendee

                           Google Spreadsheet   Post  #1717
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-31-2014
post by J. Bennett (HCC):
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/LJC1roCsIuM)
HLOOKUP "in reverse"

I'm looking for a function (or possible build a formula) that will find a particular value in an array, and return the value at the top of the array in the same column. I have a signup sheet for one of three workshops offered at a particular time. The top row is the title of each workshop, and users are filling in there names in each column. I would like to automate the creation of agendas for each person by having the formula find each users name, and then returning the title of the workshop at the top of that column.

Example Spreadsheet

All e-mail correspondence to and from this address is subject to the North Carolina Public Records Law as defined under N.C.G.S. §132.1, which may result in monitoring and disclosure to third parties, including law enforcement and the media.
----------------------------------------------------------------------------------------------------------

yogi_Consolidate Table Of Quantities And Parts Description By Part Numbers

                           Google Spreadsheet   Post  #1716
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-30-2014
post by Steve G Horning:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/bnu1VgnqKzY)
Write a query to consolidate duplicate part numbers in a bill of material

I have a bill of material that has duplicate part numbers and I want to consolidate the quantities in column B for the duplicate parts.  The spreadsheet linked below has an example.  The Red area is before the consolidation and the Green area is the result I'm looking for.  I'm trying a query for the first time and I probably have some syntax wrong.  Any help would be greatly appreciated.

=query(A:E,"select * Sum(B) group by C order by A",1)

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

Thanks,

Steve

---------------------------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Cell B2 To B If The Entry In Column B Also Occurs In B2 To B In Sheet1

                           Google Spreadsheet   Post  #1715
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-30-2014
post by scooter69:
(https://productforums.google.com/forum/#!mydiscussions/docs/LuDUM11pipo)
Using Countif across multiple sheets

Is it possible to use countif across multiple sheets to weed out duplicates?

I have a database of names and twitter handles spread onto 3 sheets and for the sake of this post I cannot merge them all into 1 sheet and I don't want to use any scripts / add-on's (the add-on I was using works fine, but it doesn't update itself. I have to re-run it every time I make a new entry to check if I am duplicating something that is on one of the other 3 pages)

I currently have countif forumulas that highlight duplicates within single columns on individual sheets. So on 'Sheet 1' in Column A I can see duplicates that are in 'Sheet 1' Column A, but I cannot see if there are any duplicates between 'Sheet 1' Column A and 'Sheet 2' Column A.

------------------------------------------------------------------------------------------------------

I did not use the COUNTIF() function ... in the following is a solution
using MATCH() function

yogi_Conditionally Format Cells If The Integer Value Of Cell Is A Specified Value

                           Google Spreadsheet   Post  #1714
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-30-2014
post by Emmett Armstrong:
(https://productforums.google.com/forum/#!mydiscussions/docs/OsjqOTXZQl4)
New Conditional Formatting option - Custom formula is
Hi Yogi,

This isn't exactly the problem I'm having. I've created a sheet with example data to show my problem more clearly: 

https://docs.google.com/a/revthink.com/spreadsheets/d/1B8ObKoXMPbZvkTSrZXEj2rYkSmfBj6qbkE5GfY2MxVY/edit#gid=0

Thank you for all you do to support this community Yogi!

Emmett

---

Yogi,

My problem is with conditional formatting, not a specific formula. I'm sorry I'm not explaining this very well. 

Cell G7 of Sheet 1 on this sheet contains an example as well as a description of my problem:

Conditional Formatting Example

------------------------------------------------------------------------------------------------------------------------------

formatting of a cell can not be used as a creiterion for Conditional Formatting in a formula based approach -- do have a look at my use of INT() function to meet the intent of your inquiry

yogi_Count Row By Row Cumulative Number Of Times Username Appears In Column B

                           Google Spreadsheet   Post  #1713
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-29-2014
post by Alan Brito:
(https://productforums.google.com/forum/#!mydiscussions/docs/C3JDc6x8zXk)
Formula to seek repeated values
hi, i want to make a formula to seek repepated values on a single column, and anotate them on another one as a counter.

for example.

on a column, i want to know how many times Each name is repeated, and make another column called "counter" wich contains the total number of the value repetitions, im doing this to count bans. For example:

Column A  Column B (Counter)
asd                           1
asd                           2
dea                           1
ad                             1
asd                           3

is it posible to do that? that a formula automatically detects the number of repetitions and continues adding them?

would be thankfull for an answer

-------------------------------------------------------------------------------------------------------------------------

yogi_From Table Of Sales Data For SalesPersons Count Number Of Sales By SalesPerson

                           Google Spreadsheet   Post  #1712
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-28-2014
post by Lance Williams:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/flSMy5GSxQo)
Counting
How can I count the number of sales by salesperson?  If there is a salesperson initials in Column B (SP1) and blank in Column C (SP2) then count a full unit (+1).  If there is a salesperson in both columns then it is a half (+.5) unit for each sales person...as follows:

Customer	SP1	SP2
Smith	RH
Jones	NS	RH
Williams	DT
Perry	AO
Thompson	NS	RH
Driskill	NS	DT
Results
RH	2
NS	1.5
DT	1.5
AO	1

I already have the salespeople listed in alphabetical order with other information so I do not want to Transpose.  Thank you.

------------------------------------------------------------------------------------------------------------------------------------------

yogi_Apply Conditional Formatting To Cells In Column A To Highlight Clients That Are Preferred Clients As Noted In Column B Of Sheet2

                            Google Spreadsheet   Post  #1711
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-28-2014
post by Leon Fishman:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/RKQQbCNNvfI)
Conditional Formatting based on 2 Lists

Hi, 

I was wondering if someone could help me with the following Problem: 

I have 2 lists in Sheets: 

Column A - This has a list of Clients 

Column B - This has a list of PREFERRED Clients

I want to draw attention to values in Column A based on the preset values in column B. 

Is there a single conditional formatting formula that can highlight All values in Column A that are present in Column B as well? 

Thank you :) 

---

I created a sample sheet here: https://docs.google.com/spreadsheets/d/1aoq2g4901BpGD4IdYxXPAeHPETtVfbJ6VxiGZ8nwjUg/edit?usp=sharing

Unfortunately the Countif solution did not work :( 

Would conditional formatting still work if the validation data is located on another sheet? 

-------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Entire Row if Value In Column A (amount owed) is equal to the Value in Column C (amount collected)

                            Google Spreadsheet   Post  #1710
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-26-2014
post by uncel_sean:
(https://productforums.google.com/forum/#!mydiscussions/docs/f-D9nA6FzZA)
Conditional Formatting, Custom Function to highlight a whole row

Hello,

I'm trying to get an entire row's background to turn light green when the following conditions are met: A3 = C3.

For example A3 is "money owed" and C3 is "money collected."  When those 2 cells are equal, I want the whole row to be highlighted in light green.

What do I need to do to make this happen?

-------------------------------------------------------------------------------------------------------

 

yogi_Compute Total Score Row By Row From Form Submissions With Correct Answers In Row 2 And Specified Scoring Strategy

                            Google Spreadsheet   Post  #1709
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-26-2014
post Peter Hegen Stewart:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/0JTCc7b_3jM)
Calculate total correct answers over a range

I want to tally the total correct answers for each form submission.

Currently the second row (A2:CS2) has the model answers. So I'll compare new submissions to this row.

I am currently using the formula below in column CT to get the desired result.

=sum(

if(exact($B$2,$B5),1,0),

if(exact($C$2,$C5),1,0),

if(exact($D$2,$D5),1,0),

...

if(exact($CQ$2,$CQ5),1,0),

if(exact($CR$2,$CR5),1,0),

if(exact($CS$2,$CS5),1,0),

)

So a correct answer scores 1 and a wrong answer scores 0 and those are added up.

Is there a more concise way of doing this?

Thank in advance,

Peter

------------------------------------------------------------------------------------------------------

yogi_Apply Conditional Formatting In Column D To Highlight Entries That Match Upto 7th Decimal Place

                            Google Spreadsheet   Post  #1708
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-25-2014
post by Emmett Armstrong:
(https://productforums.google.com/forum/#!mydiscussions/docs/OsjqOTXZQl4)
----------------------------
Hi Yogi,

I'm wondering if you can help with the issue I've been having in this post:

https://productforums.google.com/forum/#!mydiscussions/docs/DMNIW4qVfjo

Thanks

-----------------------
New google sheets changes rounding procedure?

I'm trying to use conditional formatting based on a minimal decimal addition to cue formatting. For example: 10 turns green when a formula adds .0005858. Conditional formatting looks for 5858, and thus turns green when it finds the value in the decimal place.

The problem I'm having is in the new google sheets. This feature does not seem to work because of a new automatic rounding format for numbers. As I change the amount of decimal places displayed, rather than just displaying a larger or smaller amount of VISIBLE decimal places, the actual VALUE of my cell is changed. .0005858 starts rounding up to .000586 and then to .00059, .0006 etc. The conditional formatting sees .0006 instead of the real value of .0005858. By the time I get to the desired result of no decimals, I've lost formatting. 

 This causes quite a problem because I don't want to have to display 10 decimal places on my spreadsheet. Anyone have ideas?

--------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_From Data In Table By Fruit Location And Day Count Fruit by Farm And Day of Week

                            Google Spreadsheet   Post  #1707
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-24-2014
post by 54612387:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/wC9dWyVZBrk)
Countif help

Trying to count some items, and need some help!

	A	B	C
1	Fruit	Location	Day
2	Apple	Farm 1	Monday
3	Apple	Farm 1	Tuesday
4	Apple	Farm 2	Tuesday
5	Apple	Farm 2	Monday
6	Apple	Farm 1	Monday
7	Apple	Farm 3	Monday

I am trying to create a formula that will tell me the number of occurrences of Apple: at each farm, and on each day of the week. For example, Apple at Farm 1 on Monday occurs twice. 

---------------------------------------------------------------------------------------------------------------------------------

yogi_Merge Data From Sheets Named 'ATC' And 'Users' With Different Field Configurations Into A Single Sheet With Common Field Configuration

                            Google Spreadsheet   Post  #1706
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-23-2014
post by:archiewood :
(https://productforums.google.com/forum/#!mydiscussions/docs/XgjyZPElVzk)
Combining pages into a single sheet

Hi all,

I've got a sheet with three pages, two of which are populated by (separate) forms. Both forms collect information about the same thing, but one collects less than the other. Form B's fields are all also contained in Form A, but not vice versa.

I would like to be able to combine the two sets of results into one sheet.

Table 1:
  A      B    C1 David  32   M2 Tom    24   M3 Sarah  27   FTable 2:
  A      B1 Alan   32
2 Suzi   25
3 Claire 31
Table 3:
  A      B    C1 David  32   M2 Tom    24   M3 Sarah  27   F4 Alan   32
5 Suzi   25
6 Claire 31


Table 3 above is the target. Is this possible?

---

Hi Ivanna, thanks for the effort you're making trying to help me. I clearly haven't effectively communicated the problem to begin with, so I'll try again.

The problem isn't just that the two sheets have different numbers of columns, but that the columns that do match are in a different order. My example did not reflect this. This is the first few columns from the actual sheets:

Sheet ATC:

Timestamp  |Callsign (if applicable)  |Aircraft registration  |Aircraft type              |Type of flight
Sheet Users:
Timestamp  |Captain                   |Aircraft operator      |Callsign (if applicable)   |Aircraft registration

There is some commonality between the two, but this out-of-step order is typical of the whole thing. Clearly if I just lump the rows of ATC and Users together, the result will be a meaningless mess.

Presenting it this way makes me think it was ridiculous to even ask whether a formula could reorganise these with any efficiency. I think the best thing is to use one of your earlier formulas, and just put blocking columns in the 'smaller' sheet so that the columns will line up once they're combined?

-----------------------------------------------------------------------------------------------------------------------------------------------

I don't see Ivanna around at the moment, so allow me to suggest that you the solution I have proposed in the following:

yogi_List In Column C Starting From Date In Cell A2 Weekdays In Cell A6 For Number Of Weeks In Cell A9

                            Google Spreadsheet   Post  #1705
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-22-2014
post by: Jessica Dial:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/JZbQ0-lFUWo)
How do I populate dates for Fridays only for 78 weeks?

I'm trying to populate dates that fall every Friday for 78 weeks.

I have clients who send in their production numbers on Fridays for the entire week. So I just need it to populate for ever Friday for 78 weeks! Is this possible?

Thank you,

    Jessica

------------------------------------------------------------------------------------------------------------------------------------------

yogi_Count Instances In Columns H and I of Sheet Named 'Raw Data' Where 'Raw Data' M19 to M = 'New & Upcoming' And 'Raw Data' C19 to C <> 1

                             Google Spreadsheet   Post  #1704
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-21-2014
post by:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/pBt-aBj61bo)
countifs that covers two columns

Hi

In the following formula, I want to count a value if it appears in either of two columns  H and I, however I can't do H19:I as then the range is of a different size. Is there a way to do this without resorting to an either/or?

thank you!

=COUNTIFS('Raw Data'!$H$19:$H,A7,'Raw Data'!$M$19:$M, "New & Upcoming", 'Raw Data'!$C$19:$C,"<>1")

----------------------------------------------------------------------