Saturday, August 24, 2013

Google Spreadsheet Comparing Sheets Of Data and Returning only Unique Values

Question:

( by Lewis Sellers )


Hi All,

We've got a Google spreadsheet file that has 4 sheets within it. The sheets should be compared using Column A (URL) only, then using all the rest of the data to try and fill in gaps that may appear from each one.

What I want to do is create a new sheet that compares all the other 4 sheets and pulls in Unique values only.

For example (hopefully this makes sense... It's only a very small

Sheet A:

Sheet B:

Sheet C:

Sheet D:

Sheet E (New Sheet):

So in theory, the new Sheet E will look through all of the data and compare the data from each of the sheets. I will make sure that all the headings are the same across the pages. What we want to avoid is lots of duplicates so that we have one clear sheet to work with.

There's lots of additional data on the sheets, so we just need to aggregate it into one clean and easy to read list.

Any help would be much appreciated.
Thanks!
Lewis

Solution:

You will need to install script VMerge from Script Gallery.
Go to menu "Tools" > "Script gallery" and then search for "vmerge" and then install it.

After you have installed the script, put the following formula in Cell A2:

=unique(query(vmerge('Sheet A'!A2:C;'Sheet B'!A2:C;'Sheet C'!A2:C;'Sheet D'!A2:C);"select * where Col2<>'' ";0))


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,

7 comments:

  1. Hi Kishan,

    Thank you very much for your help so far, I'm very impressed!

    I've just uploaded the code you've suggested and updated the sheet names accordingly, but I'm having a slight issue where I'm getting the following error - http://d.pr/i/PyfU

    Any ideas why this would happen?

    Kind regards,
    Lewis

    ReplyDelete
    Replies
    1. I can see in the formula you have ( 'Ahrefs'!C:AA )
      Note: You must be having same number of columns from each sheet, so change it to:
      ( 'Ahrefs'!A:C )
      OR
      ( 'Ahrefs'!C:E )

      Delete
    2. that is you must be having same no. of columns in each argument of custom function VMERGE

      Delete
  2. Hi Kishan,

    Brilliant - thank you for your help. Works like a charm - I'll donate now! :)

    Kind regards,
    Lewis

    ReplyDelete
  3. Hi Kishan,

    Just a quick message, I've spotted a potential issue with the little script. If one of the columns is empty, then it doesn't pull the text in. For example, if you had only the URL, but no Contact Name, this wouldn't show on Sheet E.

    Is there a way to get this data to show?

    Thanks,
    Lewis

    ReplyDelete
    Replies
    1. Hi Lewis,

      In the formula:
      =unique(query(vmerge('Sheet A'!A2:C;'Sheet B'!A2:C;'Sheet C'!A2:C;'Sheet D'!A2:C);"select * where Col2<>'' ";0))

      If you can notice >> where Col2<>''
      it means select all rows where Col2 is not empty, so change this to the column in which you will always have the data, if you will always have the data in column no. 1, then try this:

      =unique(query(vmerge('Sheet A'!A2:C;'Sheet B'!A2:C;'Sheet C'!A2:C;'Sheet D'!A2:C);"select * where Col1<>'' ";0))

      and if you have date or numerical values in Column no. 1 then try this:
      =unique(query(vmerge('Sheet A'!A2:C;'Sheet B'!A2:C;'Sheet C'!A2:C;'Sheet D'!A2:C);"select * where Col1 is not null ";0))

      If you still have issues, then let me know and also if possible share your spreadsheet, so that I can quickly solve out the issue..

      Thanks,
      Kishan.

      Delete