New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

How to distribute cell contents into multiple new rows below

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

How to distribute cell contents into multiple new rows below

Postby sandhu412 » Fri Dec 09, 2011 11:36 am

Hi Sisyphus,

This is a continue thread from my post from the old forum: http://www.teachexcel.com/forum/viewtopic.php?f=3&t=1058

Below are the answers to the questions:
You asked: The best way to start is to create the final result with hard data. From there you work backward to replace the hard data with soft. So, create a worksheet that holds, say, 3 sets of resolved data.
Answer: Yes, attached is the excel (expected.xlsx) with the expected data. As you can see column D now has each step as an individual row and all the data in the spreadsheet is shifted down. This is final excel format that I am looking to achieve.

You asked: Secondly, provide the source data. Best, provide the same 3 sets of data that you put manually in the Result sheet created in step 1. You say you want to export them to Excel. You also said they are in an Excel cell. What is their original format?
Answer: The source data is the attached spreadsheet: "AcceptanceTestCases.xlsx" where you can see column D just has one row of data for all the steps in each test case. This is the original format of column D and I want the same column to look like what I provided in the spreadsheet: expected.xlsx

Below is an example of one row original vs expected:

Column D Original:
1. Navigate to studio with a PBC in advanced mode http://www.vptest.com/vp/ns/studio3.aspx?pf_id=088&cfs=-1&combo_id=3956&advmode=true 2. Click the add text button in the toolbar 3. Enter text to the text field 4. VERIFY that the new text box has been added to the canvas 5. VERIFY that there is a new text field on the left side of studio named "Text Field 1" 6. Click back side and click the front side (in order to get the doc id) 7. VERIFY that the text to the text field is applied to canvas

Column D expected:
"1. Navigate to studio with a PBC in advanced mode
http://www.vptest.com/vp/ns/studio3.aspx?pf_id=088&cfs=-1&combo_id=3956&advmode=true "
2. Click the add text button in the toolbar
3. Enter text to the text field
4. VERIFY that the new text box has been added to the canvas
5. VERIFY that there is a new text field on the left side of studio named "Text Field 1"
6. Click back side and click the front side (in order to get the doc id)
7. VERIFY that the text to the text field is applied to canvas

You asked:By the way, I find it odd that you want the data in several rows. Mostly, people want data in several columns. Any particular reason for this that you can share with me?
Answer: I want the data in several rows because that is the format I will use to eventually export all the data in the spreadsheet to another tool. This spreadsheet is a placeholder of all data in the required format to get it exported into another inhouse office tool.

Please let me know how to get started with this conversion process. Also at the end of the each step I think there is an enter key to recognize the delimeter. Or can we use "space-number-dot" as a delimeter because each new step starts in that format.

Thanks much,
Sandeep
  • 0

You do not have the required permissions to view the files attached to this post.
sandhu412
Rookie
 
Posts: 1
Joined: Dec 9, 2011
Reputation: 0

Re: How to distribute cell contents into multiple new rows b

Postby Sisyphus » Sat Dec 10, 2011 4:05 am

Hello Sandeep,

I'm using Excel 2003. Perhaps you can make future posts in els format. For now I have all I need. We can continue with design.

I think it would be good practice to write the changed data to another sheet or workbook, not altering the existing data. However, if you have a strong preference for adding rows to your data sheet the code to do I will be much the same.

The next thing you need is a few names both for our correspondence and your work. Let me call the workbook we work in simply Wb. It has two worksheets which I suggest we call 'Data' and 'Output'. Wb needs a code Module. Do you need help creating it? The default name will be 'Module1'. I will refer to it as 'the code module'. In this module you will need a procedure which I call 'SplitD' in reference to the column D that is the base for the split.

I forgot how well versed you are with VBA. One function you will need is Do .. Loop. Please look it up if you need to.
The following code is your action plan. Paste it into your code module:
Code: Select all
Option Explicit

Public Sub SplitD()

    Dim WsR As Worksheet              ' this is the Data sheet
    Dim WsT As Worksheet              ' this is the Output sheet
    Dim R As Long           ' use this to point at the row in WsR you are currently treating
    Dim T As Long           ' use this to point at the row in WsT that you want to write to next
                                    ' this row must always be a blank row (T for Target)
    Dim S as String.          ' read contents of cells(T, 4) to this string for manipulation

    Set WsR = ActiveWorkbook.Sheets("Data")
    Set WsT = ActiveWorkbook.Sheets("Output")
    R = 3                        ' change this number to point at the first row to be treated
    T = 3                        ' change to point at the first Target row
    ' Do Until WsR.Cells(R, 4).Value = vbNullstring.   ' meaning, action stops at the first blank row encountered
          ' WsR.Rows(R).EntireRow.Copy Destination:=WsT.Cells(T, 1)
          S = WsT.Cells(T, 4).Value
          ' Do
               ' Extract the first row from S
               ' Write it to WsT.Cells(T, 4).Value
               ' remove the portion of S that has already been written to WsT
               ' T = T + 1
          ' Loop While Len(S)                 ' there is still another item or more
          ' T = T + 1                              ' create a blank row after each item
          R = R + 1
    ' Loop
End Sub

That is all there is to it. At this time you need to check that everything you want considered and done really is
considered and will be done. Use plain language. Keep the apostrophe at the start of each line to mark it as comment. Make sure you understand every line before running the code.

There are two loops. Solve the inner one first. Keep the outer apostrophed until everything is done. Run the procedure by placing the cursor in it and pressing F5. We can look for another way after it works.

The most important next task will be to determine the end of an item in string S. This you need to do by yourself because you need to be sure that the rule can be applied to every line in your data. It seems that all items start with a number and a period. But may such a combination also exist within items, such as for reference?

It also seems that the second number isn't always 2. But, perhaps the items are numbered in a sequence like *, 0, 1, 2 etc. Or, if the first ID is * the sequence for numbering following items is different from lines starting with 0 or 1. It doesn't really matter how many rules or exceptions there are, so long as you can define all the rules and all the exceptions. Write all these rules into your procedure, under the row "extract the first item" or "remove first item from S".

You have guessed it. After the plan is done you need to replace every line of text with a line of code have a pleasant day! :D
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 47 guests