6

I have 12 sheets in one Google Sheets document labeled for each month (January - December). On each sheet column A contains a project number, e.g. "6091".

I'm trying to find a function that will check all of the other sheets to see if there are duplicate cells in the "project number" column of other sheets.

So: "Do any of the cells in column A, match any of the cells in column A on other sheets".

Is there a quick way to do this?

Michael
  • 79
  • 1
  • 2
  • 3

3 Answers3

2

The formula =arrayformula(iferror(match(A2:A, AnotherSheet!A2:A, 0))) checks each value in A2:A of the present sheet for being in A2:A of AnotherSheet. If it's there, it returns the position in AnotherSheet, otherwise the output is empty (the error #N/A is suppressed by iferror).

You can use the above for each of the sheets separately. Alternatively, if you are not interested in the positions and just want to know which entries from A2:A are found elsewhere, then add the results for each sheet:

=arrayformula(iferror(match(A2:A, AnotherSheet!A2:A, 0)) + iferror(match(A2:A, ThirdSheet!A2:A, 0)))

The output is 0 is there is no match, and a nonzero number if there is.

  • For some reason this isn't working for me. I get a loading bar in google sheets for a few minutes then just an empty cell (even when there should be a match). I assume I should change "AnotherSheet" to the name of the other sheet that needs to be checked so I have this: `=arrayformula(iferror(match(A2:A, April!A2:A, 0)) + iferror(match(A2:A, March!A2:A, 0)))` On the May sheet to check if anything in May exists on April or March – Michael Jun 02 '16 at 15:25
1

You may try using this to find the number of duplicates:

=counta('JAN'!A:A,'FEB'!A:A)-countA(unique({'JAN'!A:A;'FEB'!A:A})

Where A:A is your column for the data you want to check and the respective files.

This formula counts the total number of data you have, minus the unique data, therefore giving you the total number of duplicates in your dataset.

This formula gives you an overview of the total number of duplicates, however, it doesn't show which cell is a duplicate.

Alternatively, you can add a checker column and input the following formula to check if that specific cell is a duplicate.

=match(cell to check,{range 1;range 2;...range 12})

Alternatively, you may use this formula to find the exact duplicates between the ranges, however this formula does not search within the range itself for duplicates.

=arrayformula(filter(range to check,(countif(arrayformula({Range 1;range 2}),{range to check}))>1))

Personally I think that the last option would be the best as it gives you the exact data to correct. I am still new to this so hope this helps:)

edmund chan kei yun
  • 111
  • 7
0

This formula should work for numerical values:

=COUNT(QUERY({March!A:A;April!A:A},"where Col1="&A2))

If you are searching for text values it would be

=COUNTA(QUERY({March!A:A;April!A:A},"where Col1='"&A2&"'"))

Unfortunately the QUERY function does not work within an arrayformula so you would need to copy the formula down the column. You can add in extra sheets into the { } array as required, separated by a semi-colon

Edit: actually, borrowing from @sandwich, this version should work without the need to copy the formula down the column:

=arrayformula(iferror(match(A2:A,{March!A:A;April!A:A},0)))

Chris Hick
  • 3,004
  • 1
  • 13
  • 15