I have a database with 2 columns and more than million rows. The first
column is the id
Example of the data (2 columns)-
04731 CRM
04731 CRM
04731 CRM
04731 RVB
04731 RVB
25475 MMX
25475 MMX
25475 FRB
25475 FRB
Result desired (2 columns)-
04731 CRM; RVB
25475 FRB; MMX
Idea is to summarize the data and eliminate the duplicates
I am using the following Code in Excel it provides the desired result-
Sub testme()
Dim InputRng As Range
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Set wks = Worksheets("Sheet1")
With wks
Set InputRng = .Range("a1:b" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
InputRng.Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("b1"), order2:=xlAscending, _
header:=xlYes
InputRng.AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("D1"),
Unique:=True
.Range("a1:c1").EntireColumn.Delete
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value
Then
'same value
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ", " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
Now my data is in access with more than a million records. Can anybody
help me with doing the same thing in access?
Any help is greatly appreciated.
Thanks !!! 3 2552
On 11 Sep 2006 16:06:05 -0700, "italia" <it*****@gmail.comwrote:
Eliminating duplicates:
Select Distinct Field1, Field2 from TableName
Then put a unique index on the combination of those two fields. If you
had done that in the first place...
-Tom.
>I have a database with 2 columns and more than million rows. The first column is the id
Example of the data (2 columns)-
04731 CRM 04731 CRM 04731 CRM 04731 RVB 04731 RVB 25475 MMX 25475 MMX 25475 FRB 25475 FRB
Result desired (2 columns)-
04731 CRM; RVB 25475 FRB; MMX
Idea is to summarize the data and eliminate the duplicates
I am using the following Code in Excel it provides the desired result-
Sub testme()
Dim InputRng As Range
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Set wks = Worksheets("Sheet1")
With wks
Set InputRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
InputRng.Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("b1"), order2:=xlAscending, _
header:=xlYes
InputRng.AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("D1"), Unique:=True
.Range("a1:c1").EntireColumn.Delete
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'same value
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ", " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
Now my data is in access with more than a million records. Can anybody help me with doing the same thing in access?
Any help is greatly appreciated.
Thanks !!!
Hi Tom,
Thanks for your reply. I got your first step. I am not sure if I
understand what you mean by unique index.
Please see my example, I have these million records and I want the end
result to be something like the following (2nd field comibed and
separeted by semicolon)
04731 CRM; RVB
25475 FRB; MMX
52425 GBH; NNS: USD
Thanks for your help!!!
Regards This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Duncan Allen |
last post by:
I've been using excel within VB.NET applications and I can't get it to close down and remove itself from memory.
I'm using Visual Studio 2003 ver 7.1.3088, Framework 1.1, MS Office 2003 with MS VS...
|
by: Prasad Patil |
last post by:
Hi,
I have a asp.net/c# web application when i try to create the following
excel object using the following code mentioned below
I have Office 2003 installed on my PC, VS.Net 2003. but still it...
|
by: Tony Young |
last post by:
Hi,
I have a multimap container. I want to eliminate all "duplicate"
elements. By duplicate I mean something like (3, 4), (4, 3) and (4, 3),
in which I want to eliminate any two of these...
|
by: D |
last post by:
I've created a report with many subreports of aggregate data. I want my
client to be able to export this data to Excel to make her charts, etc. Only
one problem: one of the fields is a "SchoolYear"...
|
by: nikkii |
last post by:
I currently have a spreadsheet with more than 1000 records. Within
this spreadsheet are many duplicates. In the past I've been using he
filter feature to find the duplictes. However, as the...
|
by: javzxp |
last post by:
Hi
I'd like to use C# to open an existing Excel workbook and save each
worksheet it contains into a new Excel file. The name of each new
Excel file should be the name of the worksheet copied...
|
by: Rich Grise |
last post by:
OK, I don't know if this is Off-Topic for the group(s), because "QT" isn't
"Pure C++", and Slackware is a distro, but those guys are sharp. :-)
And I've crossposted to sci.electroncs.design because...
|
by: slinky |
last post by:
I found the following code to transfer datagrid data to an Excel file.
Is this written in C#?... I'm a vb.netter. I'm just not sure where to
place the code to experiment on it. Should I place it in...
|
by: raul15791 |
last post by:
Hi,
I'm new to C#. I'm writing a program that open a new excel file, write into it and lastly close the file. But the program is that there will be a orphaned process named EXCEL.EXE left on the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |