PC requirement for Excel to run Properly

[ajcarlos18]

Hi,

I have an excel database worksheet and occupied 65,000 rows and 15 columns.Column A had this formula (=IF(COUNTIF(B:B,B7)>1, "Duplicate!","Not Duplicate")), Column B had this forumula (=CONCATENATE(SUBSTITUTE(SUBSTITUTE(C2,",",".")," ",""),D2,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,",","")," ",""),".",""),SUBSTITUTE(SUBSTITUTE(I2,",","")," ","")). My Excel freezes and when I tried to filter the duplicate and not duplicate, it will not be responding along with the prompting memory issue. I'm working on a laptop with 64 bit and 8gb RAM. Do you think upgrading my specs will do?

Thank you very much

 

[shg]

The formula in column A copied down 65,000 rows requires 422 billion comparisons. I'd expect Excel to complain.

If you sorted by column B, a formula would only need to look at two or three cells to detect duplicates.

 

[ajcarlos18]

Thank you very much. Do you have any suggested formula to use to detect duplicates using the column B formulas?

 

[shg]

	A​	B​	C​
1​	​	​	​
2​		2​	A2: =IF(COUNTIF(B1:B3, B2) > 1, "Dup", "")
3​		5​
4​	Dup	6​
5​	Dup	6​
6​		10​
7​	Dup	11​
8​	Dup	11​
9​		12​
10​		14​
11​	Dup	15​
12​	Dup	15​
13​	Dup	15​
14​		16​
15​	Dup	17​
16​	Dup	17​
17​		18​
18​		19​
19​		20​
20​		22​
21​		23​
22​		25​
23​	Dup	29​
24​	Dup	29​

 

[ajcarlos18]

Wow great. Thank you very much for the much needed help

 

[shg]

You're welcome.