Link to another workbook and find duplicate entries

[sindhuja]

hi,

I have an excel wih huge number of rows. Everyday we will append the values to the same workbook. Due to that the file size increases and evenrytime if changes done it takes very long time to save.

In the file we use countif function to find the duplicate values of newly appended values.

If it is duplicated then that needs to highlighted.

Also my concern here is if there is a way to maintain a different excel and give a link to that to find the duplicate entries.

Thanks,
Sindhuja

[Aussiebear]

What data is duplicated? Rows, Columns or cell values?

[sindhuja]

hi,

I have attahed the sample spreadsheet for your reference.
Reference number duplication to be found out.

Daily we will append the rows to the existing rows.
Is there a way to maintain a master database stored somewhere and when datas are updated in a daily workbook then the reference number in the daily workbook to be compared with the reference number of master database and highlighted if duplicated in daily workbook...

At the end of the day all the rows with status complted to be appended to the master database.


Can this be done using macros...

-sindhuja

[sindhuja]

Attached the sample speadsheet for the reference. The actual file contains huge volumes of rows and many columns.

[sindhuja]

Any help on this pls...

-Sindhuja

[GTO]

In looking at the example workbook you posted at #4, I see the values in C Column that can be duplicates.

It seems very unclear to me what you want to do with the numbers or records.

[sindhuja]

Hi,

As of now i use only one excel wich contains >5000 rows with IF condition.
Hence the time taken to enter a value and save the file is very long.

So, i thought of considering the file now am using as the database and will create a new file in which i use daily file.
what i want now is i need to compare the ref # of the new file with the old database and highlight if duplicated in the duplicate column of both the files with the count.

At the end of the day i have to apend the new file with the status "completed" to the old database.

Am doing this process to avoid duplicate processing.

Am not sure whether this can be done..
Any other idea will also be much appreciated..


Hope i made clear now..

-Sindhuja

[sindhuja]

Any assistance pls...

-Sindhuja

[mohanvijay]

Hai try this

 
Dim datawk As Workbook
Dim last_row, this_last_row As Integer
Dim hold() As Variant
Set datawk = Workbooks.Open("C:\Documents and Settings\User\My Documents\sample.xls")
last_row = Workbooks("sample").Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row
Workbooks("sample").Sheets(1).Activate
ReDim hold(1 To last_row - 1) As Variant
For i = 1 To last_row - 1
    hold(i) = Cells(i + 1, 3).Value
Next i
this_last_row = ThisWorkbook.Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row - 1
ThisWorkbook.Sheets(1).Activate
For ii = 1 To this_last_row
    chk = Cells(ii + 1, 3).Value
    
    For chk_val = 1 To last_row - 1
        
        If chk = hold(chk_val) Then
        
            Cells(ii + 1, 3).Interior.Color = vbRed
        
        End If
    
    Next chk_val
Next ii

Workbooks("sample").Close

[Charlize]

Not sure if I grasp it completely. In col C you got references that can occure more then once in both files (in main and daily). So if it only exists one time (in the daily one) ... what to do. If you find a duplicate, what next ? Is it a duplicate in daily only or one in main and one in daily ?

I assume the daily is an exact copy of the main file, I mean, the same layout .

Charlize

[sindhuja]

Hi,

Daily file is the daily input and main file is the database which contains all the files till date.

I need to check the data in daily file with the data in the main file. if there is duplicate color column 1 with the count of how many times duplicated.

At the end of the day, i need to append the contents from daily file to main file. This happens every day.
Layout is same for both the files.

Main file acts as a database.

-Sindhuja