473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Excel code convert to Access code - Concat & eliminate duplicates

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 !!!

Sep 11 '06 #1
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 !!!
Sep 12 '06 #2
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

Sep 12 '06 #3
create a distinct query to return unique values
then use fConcatChild from Accessweb

http://www.mvps.org/access/modules/mdl0004.htm

Sep 13 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
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...
0
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...
3
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...
4
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"...
2
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...
1
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...
7
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...
6
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...
1
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...
0
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.