Unique Values with Countif

[GN0001]

Hello All,

We know that we can create unique values when our look up values are duplicated.
If we are going to do a look up function and we map George1 in sheet1 to George1 in sheet2, then we have not done a right match. Since the true match of George1 in sheet1 is George2 in sheet2.

Any comment on this?
I appreciate your help to bring light to this matter.

Thanks,
GN0001

 

[NARAYANK991]

Hi ,

On what basis is the formula in the Helper column decided ? If you ask me , it is a concatenation of the name in column A and the address in column C which should be used to derive the result in column B ; just a COUNTIF is not correct.

Narayan

 

[Faseeh]

Hi,

Use this where you want to fetch the correct result, i guess you have to bring correct result from sheet 2 to sheet 1.

=LOOKUP(A3&C3,Sheet2!$A$4:$A$5&Sheet2!$C$4:$C$5,Sheet2!$B$4:$B$5)

 

[GN0001]

Hi ,

On what basis is the formula in the Helper column decided? If you ask me , it is a concatenation of the name in column A and the address in column C which should be used to derive the result in column B ; just a COUNTIF is not correct.

Narayan

Yes, to get the right match, we need to use concatenate rather than CountIF function. Chandoo shows to create unique values for look up functions when we have duplicated values. To me, this approach can't be correct because of the reason I showed on the loaded workbook.

Thanks for all.
GN0001

 

[GN0001]

Hi,

Use this where you want to fetch the correct result, i guess you have to bring correct result from sheet 2 to sheet 1.

=LOOKUP(A3&C3,Sheet2!$A$4:$A$5&Sheet2!$C$4:$C$5,Sheet2!$B$4:$B$5)

Thanks for he response and you look up function.
GN0001

 

[NARAYANK991]

Hi ,

In your uploaded workbook , you have used the formula , but you have not explained the logic.

What is the logic for deciding that the first George on one sheet is George1 , with address A , when the first George on the other sheet is George1 , with a different address ?

If you designate the wrong George as George1 , then how are you going to match correctly ?

I think unless you can explain the logic ( just putting a formula is not enough ) , the problem cannot be resolved. Even if it can be resolved , more data may or may not break the solution which is provided.

Narayan

 

[GN0001]

Hello all,

That is what I am trying to say that we can't only use countif for creating unique values out of duplicate values.

I have no idea what logic we need to have, I only know that using CountIf along with concatenation can't be enough for creating unique values. As you say there should be more logic for creating unique values. Do you know what it is?

Thanks,
GN0001

 

[NARAYANK991]

Hi Guity ,

That is entirely up to you ; you are the owner of the data ; it is significant to you , and you should be able to say what will decide whether a value is unique or not.

I had mentioned that the name + address could be one way of deciding uniqueness , since there cannot be two people with the same name and the same address ; however , you say this method cannot be used.

I have no idea what else can be the deciding factor for making a name unique ; it cannot be a positional location , which is what the COUNTIF function will use.

Narayan

 

[GN0001]

Narayan,

What you said on concatenating name and address is correct; I agreed at it from the beginning. I am saying that using countIf to create numbers such as 1,2,... and then concatenating to the lookup name is not a solution. Since George 1 sheet 1 might not be as Geroge 1 in Sheet 2.
So the idea of using countif for generating unique values is not right for the sake of the reason I mentioned on the top.

Logic: I can't think of any logic that I can make sure that George 1 in Sheet 1 to be same as George 1 in Sheet 2, unless I concatenate George 1 with some other field. Then there is no need to create George 1 from the beginning at all.

so the idea of using CountIf for generating unique numbers for lookup names is not correct.

Regards,
GN0001