Solved

Multiple Cell Contents To Individual Rows/Records

Posted on 2013-01-17
6
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

726 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