Unable to use If, Countif and Concantenate function together

[ygoyal578]

I was trying to find if result of concatenation is duplicate or not but the formula is not working. Below is the formula that I create. pls help in rectifying the error.

IF(COUNTIF(VALUE(CONCATENATE($E$2:$E$1048576,$M$2:$M$1048576,LEFT($AP$2:$AP$1048576,2))),VALUE(CONCATENATE($E$2:$E$1048576,$M$2:$M$1048576,LEFT($AP$2:$AP$1048576,2))))>1,"Yes","No")

 

[jasonb75]

When you look at the help for COUNTIF, or many similar functions, you will see that is says something like

=COUNTIF(Range, Criteria)

Where it says range, it must be a range, functions like VALUE, CONCATENATE, etc return strings, numbers or arrays, not ranges. This means that they are not valid for use in that part of the formula.
The main functions that you can use in the Range part of the formula are OFFSET, INDEX or INDIRECT.

In reality, how many rows of data do you have? Your formula is referring to the entire column with the exception of the first row, but I doubt that you actually have that much data.

 

[jasonb75]

Trying to makes sense of your formula, I think that what you actually need might be

=IF(COUNTIFS($E:$E,$E2,$M:$M,$M2,$AP:$AP,LEFT($AP2,2)&"*")>1,"Yes","No")

 

[ygoyal578]

Trying to makes sense of your formula, I think that what you actually need might be

=IF(COUNTIFS($E:$E,$E2,$M:$M,$M2,$AP:$AP,LEFT($AP2,2)&"*")>1,"Yes","No")

I can't use the above formula as I have to find unique data and that could be done by concatenating 3 column's data.

 

[ygoyal578]

I

When you look at the help for COUNTIF, or many similar functions, you will see that is says something like

=COUNTIF(Range, Criteria)

Where it says range, it must be a range, functions like VALUE, CONCATENATE, etc return strings, numbers or arrays, not ranges. This means that they are not valid for use in that part of the formula.
The main functions that you can use in the Range part of the formula are OFFSET, INDEX or INDIRECT.

In reality, how many rows of data do you have? Your formula is referring to the entire column with the exception of the first row, but I doubt that you actually have that much data.

I'm having around 10K records and in that I have to find whether there is any duplicate record or not. In addition to that data is such that, the unique record could be identified based on 3 columns.

 

[jasonb75]

I can't use the above formula as I have to find unique data and that could be done by concatenating 3 column's data.

Have you actually tried it, or are you just dismissing an answer that you don't think is right?

Do you want the formula to tell you if each row is unique or duplicate?

Or do you just want a single cell that says there are duplicates somewhere in the list?

 

[ygoyal578]

Have you actually tried it, or are you just dismissing an answer that you don't think is right?

Do you want the formula to tell you if each row is unique or duplicate?

Or do you just want a single cell that says there are duplicates somewhere in the list?

Yes I have tried the formula then only i have posted the question.

And yes i want to tell if record is unique or not across the table. And to make each record unique i have concatenated 3 columns. After concatenating the columns I'm checking if record is unique or not.

 

[jasonb75]

Is this what you mean?

The List.xlsx
	A	B	C	D
1	Column E	Column N	Column AP	Duplicate?
2	a	b	c	Yes
3	a	c	b	No
4	a	b	c	Yes
5	a	b	b	No
Sheet2

 

[ygoyal578]

Yes

Is this what you mean?

The List.xlsx
	A	B	C	D
1	Column E	Column N	Column AP	Duplicate?
2	a	b	c	Yes
3	a	c	b	No
4	a	b	c	Yes
5	a	b	b	No
Sheet2

Yes. This is what I want.

 

[jasonb75]

That was done with the formula from post 4 (the one that you said didn't work).