PC requirement for Excel to run Properly

ajcarlos18

New Member
Joined
Sep 4, 2017
Messages
15
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
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

New Member
Joined
Sep 4, 2017
Messages
15
Thank you very much. Do you have any suggested formula to use to detect duplicates using the column B formulas?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
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​
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,061
Messages
5,472,223
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top