yogi_Workaround For Highlighting Duplicates In Nearly The Same Column

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #729   Sep 03, 2012     www.energyefficientbuild.com.

user Kylecore said: (http://productforums.google.com/forum/?zx=nclx24r3fxl3#!category-topic/docs/spreadsheets/OgevrVSm02o)
Yogia help!
I came across this tonight :
http://spreadsheets.google.com/pub?key=tfa10vy3An0f9ES56cnyYWQ&single=true&gid=5&output=html 

It looks exactly like something i need for a task I've been working on except I'm slightly confused on how to implement it and i need a few more specifics.

I have a few things I'd need slighty different however.

One is that i have columns "A"-"I" already in use.
I still only need to check for duplicates in column "A" only but would prefer for it to simply colour the box or text within column "A" instead of putting a colour or word in another column like "J".

I need to compare up to 1000 rows at some times and I do NOT want anything deleted.
I would like to be shown where the duplicates are for me to remove later.

The data being compared in column A is solely numbers with no text, not sure if it makes a difference.

Is there any way this can be done

I've also included an example image to show you what i am requsting

Currently i have a filter set up to remove certain things from my list but no idea on how to accomplish the above.

Any response at all would be greatly appreciated, and thank you very much for reading.

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

following is a solution to the problem

yogi_Consolidate Amounts By Categories From Monthly Data Sheets Into AnnualSummary Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #728   Sep 03, 2012     www.energyefficientbuild.com.

user jstrong10 said: (http://productforums.google.com/forum/?zx=nclx24r3fxl3#!mydiscussions/docs/LbbkQb94ki8)
How to add sumifs from multiple tabs
Hello,

I am trying to create a spreadsheet that will add up a certain criteria from multiple spreadsheets and total it. The formula I used for doing this for just one spreadsheet is the following: Category is for Cars =SUMIF('July Ledger (Daily)'!$C$5:$C$57,$A6,'July Ledger (Daily)'!$B$5:$B$57)

I then have this same formula pulled down over several different categories that I am tracking such as Parts, Employee Cost, etc. Where the "C" column is the category that I wish to be summed and the "B" column is the actual amount that is summed.

My efforts so far have been to create a range of the list of different Tabs/Spreadsheets that I am trying to add up, but have been unsuccessful. For example =SUMIF('July Ledger (Daily):December Ledger (Daily)'!$C$5:$C$57,$A6,'July Ledger (Daily):December Ledger (Daily)'!$B$5:$B$57). I then have Tabs/Spreadsheets labeled July Ledger (Daily) through December Ledger (Daily).

Thank you!

----

This link should allow you to share. - https://docs.google.com/spreadsheet/ccc?key=0Ak33DpvrPkBddFc2V25CUkxCTHp6UUdWLV9QYkhIakE 

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

you may find my following solution to be of some interest

yogi_Add To August Column Numbers From September Column And Sort In Descending Order

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #727   Sep 03, 2012     www.energyefficientbuild.com.

user DanielL said: (http://productforums.google.com/forum/?zx=nclx24r3fxl3#!search/DanielL/docs/dXPusr92Avs/Bm36uR2PV7AJ
how to add 2 columns data to SORT() without script and without extra columns 
I'm currently using the function on the result(G2), if there won't be a performance problem with very large lists I can just keep it like that.

https://docs.google.com/spreadsheet/ccc?key=0AtZISdtCZtl5dDJwMUtUZEtiQW5vc0JGNjZJYXFkeHc

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

typically, a formula based solution would execute faster than a script based solution ... so following is a formula based solution

yogi_Extract Matching Records From Two Sheets Meeting Multiple Criteria Into A Third Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #726   Sep 02, 2012     www.energyefficientbuild.com.

user yachadhoo said: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/P1YTlLT2U3w)
Return duplicate strings in 2 different filtered arrays in a single formula
Thanks so much, Adam!

It definitely worked for the original example I gave.

I don't understand WHY it works, though...which is probably why I can't apply it to my spreadsheet.  

For:

=FILTER(A:A;B:B=1;MATCH(A:A;FILTER(C:C;D:D=3);0)) 

I noticed that if I just took the last part:

=MATCH(A:A;FILTER(C:C;D:D=3);0)

That I get an error (in Q3):

https://docs.google.com/spreadsheet/ccc?key=0ApQ2E8kH4UvzdDlESkJoV3B6VldfbFRod3ppcEk5Y3c

What on earth?

This Match is within a filter...so how can the filter work, when the match returns an error?

= = =

Secondly, I attempted to apply it to my spreadsheet, and it failed.  :(

I get the same error I was getting with my other feeble attempts, "Mismatched Range Lengths"

OK...Fine...I have shared a copy of my main sheet...with the sheet "HELP" with my failed application of your formula, Adam...

I would greatly appreciate any more help you (or anybody else) is willing to offer.

https://docs.google.com/spreadsheet/ccc?key=0ApQ2E8kH4UvzdFJjN3NaMU5QaW53VXk2S2VGVUVCY3c

Thanks in advance!

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

following is a solution to the problem

yogi_Set Up A Book Reading Program With Starting Date Time Interval In Days Number Of Intervals And Progress Report

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #725   Sep 01, 2012     www.energyefficientbuild.com.

user DaltonJ said: (https://productforums.google.com/forum/#!mydiscussions/docs/MbgLk9V0sCw)
Issues with a formula: Date countdown function

Hey everyone

I've got a little issue with something I'm trying to do for my brother. The particular issue I'm trying to fix is with a function dealing with dates. I'm trying to set up about 7 cells in a column to count down days in two week periods. For instance, each cell would start out saying 14. This would stand for 14 days. Each cell would be for a 2 week period. If I started down the countdown from today, I'd want it to countdown from 14 to 0 and that would be two weeks. The day after it hits 0, I would like the two week period in the next cell to begin counting down, and so on.

So if I have 7 cells, each starts off displaying 14. They countdown to 0, then the next cell takes over and proceeds till they've all counted down to zero. I've tried different formulas and keep finding logic errors if not just outright errors that break it. The code I've gotten to now is the following:

=if(date(2012,09,12)-TODAY()<=0,0,if(date(2012,09,12)-TODAY()>14,14,or(date(2012,09,12)-TODAY()<14;date(2012,09,12)-TODAY()>0),date(2012,09,12)-TODAY()))

I looked around and it seemed as if the best way to do it was with nested IF statements. The red section is my first statement. It says that if the target date, minus today's date, is less than or equal to "0", then display zero. This keeps it from going into the negative numbers. The green section says if it's greater than 14, then display 14. I had a problem when I went to the second cell of it being 28 and showing that. It was a logic error. In the blue section, I needed to use an OR statement. I'm not sure if that's legal. I'm not 100% sure how to use the IF statement, but this is how they showed to nest it in another post on this forum. I had to use the OR statement, because I needed to tell it that if the target date, minus today's date, is between 14 and 0, then display it. This would be my 13-1 numbers. If anyone could tell me the most consice way to write this code I would appreciate it. Also, while you're here, there are a couple of other things I'd like to do involving this.

This whole thing is because he wants to read a book series, but has trouble without a deadline. I've set up a whole thing that tells him his average number of pages he's read, how many he has left in each book and total, etc... This issue is because I'm trying to give him 2 weeks per book. I'd like to be able to change that later, but it's what i'm going with now. I'd like to be able to display a message in an adjacent cell depending on how he's doing. Ideally, I would want a formula that would take the number of pages he's got left to read and if it falls within a certain threshold, then display a particular message. For example, if he's halfway through the book and has a week left of his two weeks, then it would say, "You're doing well"; if he has read half of the book and only has 2 days, then it would say, "Hurry up, you're falling behind". If he's finished the book, it would say, "Congradulations, you're done!". That kind of thing. At minimum, I'd want to display these messages based on what number is displayed in the 14 day cell.

Second, I'm currently changing the date in each cell that the 2 week period starts in. In the case above, it would be date(2012,09,12). For the next cell, I would change it to date(2012,09,26), etc... I'd like to have that 2 week period to extrapolate from a cell that he would enter his start date in. So if he were to start today, on 8/30/2012 (aka Cell A1), then the first cell would be date(A1 - today()), then the next countdown cell would be like, date((A1 + 2 weeks)-today()), and so on. I tried to pull the date from a cell for another simpler formula and it wouldn't do it. I think it was in the wrong format.

If you guys/gals could help me with these problems, I would really appreciate it. Thanks for reading all of this.

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

following presentation is a result of a little play I had with this

yogi_Combine List Of Entities In Column A From Page1 and Page2 Into Page3

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #724   Sep 01, 2012     www.energyefficientbuild.com.

user fugai7 said: (https://productforums.google.com/forum/#!category-topic/docs/P5zDL9Pf2Qs)
How do I link data from two different pages in a spreadsheet into one column on a separate page?
I need to combine two lists of names into one list in one column. The first list of names is in, let's say, Page 1 column A, and the second list Page 2 column B, and I would like them to be combined into a Page 3 column C (respectively with the list from 1A then below 2B). Any ideas? Help is greatly appreciated!
----------------------------------------------------------------------------------
following is a solution to the problem

yogi_Check For Duplicates In Columns A And B And Post in Column C Row by Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #723   Sep 01, 2012     www.energyefficientbuild.com.

user jay Curtis said: (https://productforums.google.com/forum/#!category-topic/docs/jF53BFoXkFg)
VLOOKUP to check for same value within column??
This seems like the most valuable use of the VLOOKUP function, yet I can't find a straight answer on how best to implement it. Apologies if this is a regular question, i scanned this section of the forum looking for similar topics..

example:

      Column A:     Column B:

      Date values   Numerical Values (unique URL IDs in numerical form)

1    1/9/12            18340

2    29/8/12          19560

3    27/8/12          17350 

I am looking for a single formula that will scan the whole column within the sheet and alert me if there is more than one entry of the same value just within the column i am entering data into. 

ie:

      Column A:     Column B:

      Date values   Numerical Values (unique URL IDs in numerical form)

1    1/9/12            18340

2    1/9/12            19560

3    27/8/12          18340 

or perhaps: 

      Column A:     Column B:      Column C: (Check value column)           

      Date values   Numerical Values (unique URL IDs in numerical form)

1    1/9/12            18340            A2 : DBL! 

2    1/9/12            19560            B3:  DBL!

3    27/8/12          18340 

      Column A:     Column B:      Column C: (Check value column)

      Date values   Numerical Values (unique URL IDs in numerical form)

1    1/9/12             18340            NO DBL 

2    29/8/12           19560            NO DBL

3    27/8/12           17350 

This seems very simple, just can't work out how to get it to blanket scan for double entries!

Many thanks,

JC 

--------------------------------------------------------------------------------------
following is a solution to the problem