Monday, October 14, 2013

Google Spreadsheet Copy data from one sheet to another if certain conditions are met removing duplicates

Question:

( by tic toc )


I would like to be able to copy data from Sheet1 to Sheet2 displaying only those rows with the highest values from Sheet1.

The following is an example of what I would like to do.  Sheet3 is the result that I would like to achieve.

Sheet1:

Sheet3:

If "Name" and "Value 1" are the same, I only want to copy the row with the highest number in column "Value 2" to Sheet2.  After copying the values to Sheet2 I would like them to display in descending order.
Thanks for the help.

Solution:



Have a look at the following screenshot of "Sheet2":


In the above sheet I have the following formula in Cell A1:
=query(Sheet1!A:C;"select A,B,max(C) group by A,B order by max(C) desc label max(C) 'Value 2'";1)


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

2 comments:

  1. Thank you for the solution. I was able to adapt this to one of my projects, and it is working flawlessly

    ReplyDelete
  2. I have somewhat different request.
    In a sheet stocks of particular A company is bought on different dates at different prices so it appears many times in the sheet. Some stocks of say B, C and D company are bought only single time.
    Is it possible to prepare automatically a new sheet where all entries are copied in a consolidated manner and name of Particular stock (having more than one entry) appear once total quantity (combining all) and average price (combining all purchases of that particular stock at different prices divided by number of shares) is shown.
    While guiding, please take into account that you are guiding a layman.
    Regards

    ReplyDelete