Solved

Multiple Cell Contents To Individual Rows/Records

Posted on 2013-01-17
6
396 Views
Last Modified: 2013-01-17
Dear Experts

I have multiple contents in 1 Cell/Field which i would like to put them in individual rows, the items are delimited by the ampersand. I've illustrated my problem in a visio...PDF.

Thank you much Experts!
0
Comment
Question by:Dare626
  • 2
  • 2
  • 2
6 Comments
 

Author Comment

by:Dare626
ID: 38790100
Here is the attachment.
Visio-Cells-Items-To-Rows-Proble.pdf
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 350 total points
ID: 38790127
you will need vba codes to do that

dim rs1 as dao.recordset, rs2 as dao.recordset, vArr() as string, j

set rs1=currentdb.openrecordset("table1")
set rs2=currentdb.openrecordset("table2")

do until rs1.eof
     varr=split(rs1![Measure],"&")
     for j =0 to ubound(vArr)
         rs2.addnew
         rs2!Mem_NO=rs1!Mem_NO
         rs2![Measure]=varr(j)
         rs2.update
     next

rs1.movenext
loop
0
 
LVL 2

Accepted Solution

by:
MMTadmin earned 50 total points
ID: 38790162
Assuming you are savvy with talking to databases?

Public Function SplitMyValues

Dim rs as Recordset
Dim Measures() as String
Dim k as Long

'get the rs of Table1 here

With rs
Do until .eof
Measures = Split(!Measure, " & ")
For k = 0 to ubound(Measures)
sql = insert into Table2(Mem_No, Measure) VALUES (!Mem_No, Measures(k))
cn.execute sql
Next
.movenext
Loop
End with
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 2

Expert Comment

by:MMTadmin
ID: 38790171
capricorn1 >> split character =  " & " unless you want to trim varr(j)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38790181
oh yeah , didn't notice the space, thanks


dim rs1 as dao.recordset, rs2 as dao.recordset, vArr() as string, j

set rs1=currentdb.openrecordset("table1")
set rs2=currentdb.openrecordset("table2")

do until rs1.eof
     varr=split(rs1![Measure],"&")
     for j =0 to ubound(vArr)
         rs2.addnew
         rs2!Mem_NO=rs1!Mem_NO
         rs2![Measure]=trim(varr(j))
         rs2.update
     next

rs1.movenext
loop
0
 

Author Closing Comment

by:Dare626
ID: 38790182
Thank you Capricorn for the fast post and MMTAdmin for expanding on capricorns solution with. I appreciate both of your time answering my question...worked perfect!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question