Macro for double entries

[Baerenstein]

Hey there,

I am having some Troubles with creating a Makro in Visual Basic.
First of all - English is not my native language, so please forgive me, if I'm making any grievious mistakes.

What I am trying to do is the following: I have two columns

A

Text1
Text2
Text3..

and

B

Id1
Id2, Id2.1, Id2.3
Id3

The Texts are connected with the IDs, and some Texts have more than one ID. Now, I want to write a makro, which selects out all the colums which have more than one Id, and for everyone of them, the makro should create a new row, and insert the text and its second ID, and then another row, and its third ID, and so on..

Do you think it is possible to make this? Unfortunately, I am not very experienced with VBA, so if anyone could help me, I would be very grateful.

Greetings, Tanja :)

[SamT]

Is this in an Excel Workbook?
OR
Is this in an Access Database?

[jonh]

In either case why would you want duplicated values? i.e. in this case 3 rows containing Text2?

[SamT]

3 IDs

[jonh]

IDs are meaningless

[SamT]

They are important to the OP.

Have you seen this thread? Separating data with VBA (http://www.vbaexpress.com/forum/showthread.php?57352-Separating-data-with-VBA)

Same situation. Multiple entries in one column.

[jonh]

I haven't and I'm not reading through all of that to see how it's relevant.
ID's are used so that you don't need to duplicate data. If you need 3 ID's to represent 1 value, there's something wrong with your database.

[jonh]

Anyway, whatever, in Access it might be something like this...



Const yourtable As String = "table1"

Private Sub Command0_Click()


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from [" & yourtable & "] where [B] like '*,*'")

Do Until rs.EOF
InsertRecs rs("A"), Split(rs("B"), ",")
rs.movenext
Loop

End Sub


Private Sub InsertRecs(fldA As String, fldB As Variant)
For i = 0 To UBound(fldB)
If i Then
CurrentDb.Execute "insert into [" & yourtable & "] values ('" & fldA & "','" & fldB(i) & "')"
Else
CurrentDb.Execute "update [" & yourtable & "] set [B]= '" & fldB(i) & "' where [A]='" & fldA & "'"
End If
Next
End Sub

[SamT]

ID's are used so that you don't need to duplicate data. If you need 3 ID's to represent 1 value,
I think Column A is one Field of Data and Column B, the Primary Keys, and that the OP is not telling us about the Unique Data that each 'ID' will get, perhaps from a different Sheet.

Isn't that the normal use for 'IDs', as Keys? Whereas most text fields are just Data?

[jonh]

I did word that slightly wrong of course.

This is good

1 Text1
2 Text2
3 Text3

this isn't

1 Text1
2 Text1
3 Text1

But who knows what the data really looks like. He doesn't seem to care anymore anyway.

[SamT]

It happens. Gives us a chance to discuss the more esoteric aspects of VBA. :)

In my mind I was seeing the ultimate result more like

1 . . . Text 1. . . Value A. . . Value B
2 . . . Text 1. . . Value C. . . Value D
3 . . . Text 1. . . Value E. . . Value F
4 . . . Text 2 . . Value A. . . Value B
5 . . . Text 2. . . Value G. . . Value H
6 . . . Text 2. . . Value I. . . Value J
7 . . . Text 3. . . Value A. . . Value K

For example translating an invoice where Texts are the Invoice Number and IDs the part numbers. Then the remaining columns could be part desc, qty, cost, etc.

Who knows how the Excel sheet is structured.

[jonh]

Well that format would make sense, because the data in each row is unique.

While there's nothing wrong with it exactly, it's not very well normalised.
Since Text1 and Text2 are duplicated, you would usually put those values into a separate table of their own and use their ID in the main table.

1. . . Text 1
2. . . Text 2
3. . . Text 3

1 . . . 1. . . Value A. . . Value B
2 . . . 1. . . Value C. . . Value D
3 . . . 1. . . Value E. . . Value F
4 . . . 2 . . Value A. . . Value B
5 . . . 2. . . Value G. . . Value H
6 . . . 2. . . Value I. . . Value J
7 . . . 3. . . Value A. . . Value K

It can save space (a number datatype is often smaller than text) and make updating values a lot easier (you would only need to change a value once to update the entire database.)

I'm looking at this from the perspective of a relational database, and where users select common values from lists (it was posted in the Access forum.)

edit
But I guess it would transfer to Excel just as well. :dunno

[SamT]

yep.

[jonh]

Well, I know you were only humouring me, but how are we to provide accurate code with such poor information?

[SamT]

Perseverance and patience. Sometimes I run out of one or the other. :(

[jonh]

I think you're all good dude.