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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#E5E5E5"][/td][td]

2​

[/td][td]A2: =IF(COUNTIF(B1:B3, B2) > 1, "Dup", "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#E5E5E5"][/td][td]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#E5E5E5"][/td][td]

10​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

11​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

11​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#E5E5E5"][/td][td]

12​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td="bgcolor:#E5E5E5"][/td][td]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

15​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

15​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

15​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#E5E5E5"][/td][td]

16​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

17​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

17​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td="bgcolor:#E5E5E5"][/td][td]

18​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td="bgcolor:#E5E5E5"][/td][td]

19​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td="bgcolor:#E5E5E5"][/td][td]

20​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td="bgcolor:#E5E5E5"][/td][td]

22​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td="bgcolor:#E5E5E5"][/td][td]

23​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td="bgcolor:#E5E5E5"][/td][td]

25​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

29​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td="bgcolor:#E5E5E5"]Dup[/td][td]

29​

[/td][td][/td][/tr]
[/table]

 

[ajcarlos18]

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

 

[shg]

You're welcome.