make cell value 0 if number on instances = 1

I've been trying to work out how to ask this question so trying to solve it, well I haven't got a hope LOL, but here goes.

Assuming that sheet1 (which has 1 number per cell) and sheet2 are used here using identicle cells on each sheet, where sheet2 duplicates sheet1 except that sheet2 changes as per my query below.

How do you check a range of single number cells to see if a number only appears once then change that number to a zero?

For example:-

Sheet1 shows as (with headers for ease)

ABCDE
23123

Sheet2 would look like this:-

ABCDE
23023

So Col C, row (where ever it is on sheet1) changes from a 1 to a zero because it only appears once in the range of cells you ask it to look at. The range however will be a block of single cell numbers of multiple cols and rows of my choice as different sheets will be added in time plus the range will change at some also.

Hope I explained it well enough

Many thanks.

Hi

Sheet2!A1: =IF(COUNTIF(Sheet1!$A1:$E1,Sheet1!A1)=1,0,Sheet1!A1)
Copy across to Sheet2!E1

rylo

Absolute legend, thank you

Don't know why I missed it myself.

Cheers

Testing out the formula I have realised I have asked to wrong question, which is my fault sorry, so let me try again.

Same principle almost, lets say each block is a range for which I'll use 3 as an example:

range1 range2 range3
12233 44115 66117

I need a way of checking to see which range has a single number only, so the first one here is range1 which has a lonesome 1, then to check the other ranges for a number that matches and change the value to a 0 which would result as below:

range1 range2 range3
12233 44005 66007

This would also pick up range2 number 5 and range3 number 7 but in this case no further changes to the other range values would change as the 5 and 7 are the only ones there.

Any thoughts?

Hi

Questions:
1) Are you still working with 2 sheets, and the output is on the second sheet.
2) How static are the ranges A:E, F:J, K:O?
3) Do the single number tests apply across all the ranges. So if there was a single 7 in range 1, would it be changed to 0?


rylo

I need to look more into it I think and when I get it right in my head I'll check back as I just did a dry run, flowchart style and what I'm asking for only party worked. Although I know what I want, I'm having difficulty putting it down.

Thanks for trying to help though, much appreciated