Split data into multiple rows.

[sreek7680]

Hi,

Some one please help me in doing this task with macros.

I have a big term in one row.
Ex: Headache;nausea;vomitting;rash ---- in A1

I have to split this term into
A1 Headache
A2 nausea
A3 vomitting
A4 rash

I have such kind of terms around 100 in 100 rows. Can some one help me how to split all 100 tems at a time?

It would be really greatful if you could help me on this.

Thank you very much,
Sreek.

[Don]

Hi,
You may try the following two steps:
1. Split the data

• Paste a string like Headache;nausea;vomitting;rash into cell A1 on a worksheet and select it.
• From the Data menu in the Toolbar select Text to Columns
• On the Wizzard that opens confirm the selection of Delimited and press Next
• On the next page select Semicolom and press Next
• On this page you can select the Destination cell (it will be A1 by default) & press Finish

This action will write each of the words into a separate column.
You can select more than one cell at a time. In fact, you may select all of column A at once.
There are more adjustments in the Wizzard if you need them.

2. Convert Columns to rows

• Select a range of items in one row
• From the Edit menu select Copy. You can also use Ctl+C.
• Select a single cell, probably on another worksheet you create for this purpose.
• From the Edit menu select Paste Special and check the Transpose checkbox. Press OK.

This action will place the items in the selected row in in a vertical column starting with the cell you selected.
You can't select an entire row. You must select the columns you wish to copy exactly.
You can select several rows in one go.

I hope this helps.

[sreek7680]

Thank you Sisyphus. It is really helpful.

However, I have more than 9000 rows and each row contain more than 30 split terms. It would be really grateful if you can provide me the Macro for the same.

Thank you once again.
Sreek.

[Sisyphus]

Hello Sreek,
The job may be too big for me but User_5 is watching, as are others, and some one may take a hand.
However, before any one can you need to provide a lot more information. All we know is that your 9000 rows have different lengths strings. We don't know in how many columns they are or in which columns. We can only guess, but do not know for sure, that they are all delimited with semicolons.
About your target sheet we know even less. We can guess, but do not know, that there are duplicates in your rows after creating them. We don't know if there might be dupicates even within a single delimited string. What to do with them? We can guess, but do not know, that you would like to have a sheet with the result of all splits in a single column. Perhaps you don't care which column.
Whoever will help you in this has experience with customers who wait until the job is done before they say what they MUST have. That is because at the time of asking for the job to be done they are so engrossed on getting it done that they don't think of what they are going to do with it when they have the result and the ball is back in their court.
Most of us here will appreciate knowing in advance what they are getting in for and may, infact, show their appreciation by doing what you ask.

Meanwhile, have you tried the Text to Column function on all 9000 rows in one go? If that was done successfully the job remaining to be done would be quite different from what you originally described.

[sreek7680]

Hello,

Apologies for all confusions.

I will try to explain this still further
A1: Rash;cough;sneeze;fever
A2: Leg pain;heart failure;stomach pain;fever

The same terms will be in D1 and D2. A1 and A2 are original terms. I want terms to split by forming extra rows.

If we split D1, terms should come into below rows and the next term (A2) should be on A6, so the same term will be on D6 now. We need to separate terms by keeping original terms in A column. One empty row after separation into rows.

After split::
A1 contains --> Rash;cough;sneeze;fever (insert 5 rows)
D1: Rash
D2: cough
D3: seeze
D4: fever
One empty row below i.e D5'

A6: Leg pain;heart failure;stomach pain;fever (insert 5 rows)

D6: Leg pain
D7: heart failure
D8: stomach pain
D9: fever
One empty row i.e D10

and so on...

Hope I am clear.

Thank you sooo much for your help.

Sreek.

[Sisyphus]

Hello Sreek,
After you explained it enough it wasn't so big a job any more and I did it for you. Please find the result attached.

Al code is contained in a single code module, Module1. If you are not running Excel 2003 I recommend that you create another workbook on your own system and transfer the code to that workbook. You can do so by Copy / Paste all the contents of Module1 to a code module in that workbook or Export from the attached workbook and Import from the new workbook. If you need help with this, just ask. The code should be in the same workbook as the data you want to split up.

As I always do, I have added a 'Control Panel' at the top of Module1. I call it 'Control Panel' because that is where you can change all parameters that can be changed without interfering with the code itself. This is a copy of the panel:

Code: Select all

    Const DataSheet As String = "Original"
    Const FirstDataRow As Long = 1
    Const DataCol As String = "A"
   
    Const ResultSheet As String = "Automatic Result"
    Const FirstResultRow As Long = 3
    Const ResultCol As String = "D"


There are three parameters for the DataSheet and another three for the ResultSheet. The Datasheet is the sheet on which you have the original data. The ResultSheet is a blank sheet that you should insert in your workbook for capturing the result. The code doesn't change your original data or the sheet on which they are.
Const DataSheet has the name of the DataSheet. You can use any name at all.
Same for the ResultSheet constant. Make sure that the names on the tabs and in the code are identical.
The FirstDataRow is the first row in the DataSheet that holds data to be treated. In the test I have data starting from the first row, but you can start in any row at all. The code doesn't expect to encounter blank rows. If there are such rows we may have to amend it - or eliminate them. The code will find the end of the datacolumn by itself.
The Datacol constant tells the system in which column the data are in the DataSheet. You can enter any column. In the test sheet it is column "A". A copy of this original data will be written to the same column in the ResultSheet. So, you can't use that column for other purposes in the ResultSheet, either.
FirstResultRow is the first row in the ResultSheet where the split data will be pasted. In the attached example I have intentionally not used FirstDataRow but you can set any value that suits you.
Finally, there is the ResultCol. You told me you wanted the result in column "D", but you can write it in any column except DataCol.

Once you have set all the parameters you can't press a button to start the action. Instead, you must call the macro. You can call the code from the Tools menu. Press Tools / Macro / Macros.
From the list that opens select “SplitSymptoms” and press the Run button.
In the attached workbook the ResultSheet is currently blank. If you run the macro you will see it populated the way you asked.

Enjoy watching Excel do the work.

[sreek7680]

Hay Sisyphus, it's working great...!!!

THANK YOU SO MUCH........You are the BEST...

A small change needed.

Client sends terms and we should not change the format of original terms. I copy same terms into D column and put delimiter for splitting.

A1: Rash.cough.sneeze.fever.
A2: Leg pain.heart failure.stomach pain.fever.

The same terms will be copied in D1 and D2. A1 and A2 are original terms. I will manually put (;) in D row, as the terms might have periods (.), commas, slash or dashes in it.

D1:Rash.;cough.;sneeze.;fever.;
D2: Leg pain.;heart failure.;stomach pain.;fever.;

Your codes works very good, but it puts semicolons in the Automatic Results sheet. I paste all original terms in A row and I copy all these terms and paste into D row and manually insert semi colons in D row for all terms. Once we run the macro all terms should split in the same sheet by not changing any terms in A row. I know I am giving you much trouble, Sisyphus. Please forgive me.

Thank you so much for your time and help.

Regards,
Sreek

[Sisyphus]

Hello Sreek,
Sorry, man! I can't follow your explanations.
My code doesn't put any semicolons anywhere! This is what it does:
[*] Copy the original, semicolon-delimited string from 'Original' to 'Automatic Result'.
[*] Split the strings at the semicolons
[*] Write each element in a separate row

Now, If you were to throw the 'Original' away after this, I believe that the result would be rather similar to what you describe except you say that you copy from column A to column D and then insert semicolons. Note the difference:
[*] You copy from A to D, I copy from Original!A to 'Automatic Result'!A.
[*] You insert semicolons in column D. I expect the semicolons to have been inserted in column A.

At the top of my code sheet (Module1) there is a constant called DataCol. Its value is "A", meaning that data will be picked from Original!A. If you change this value to "D" data would be picked from Original!D. So, if your client sends you the sheet with his list in column A you could copy it to D, insert your semicolons and then run my macro using the data from D. You would then see the data from Original!D in 'Automatic Result'!A.

I can't be of more help becasue I don't understand where the semicolons bother you, since I don't introduce any to the equation. I just use the ones you provide. Perhaps you have another look at the sheet "Manual Result". This is the sample that I have based myself on. If you need something different, perhasp this is the place to describe it.

I asked you about the availability of semicolons as separators and you didn't respond. You should have. Now note the following:
[*] It is very easy to change the separator to any character you want. If your clients use semicolons in the criteria occasionally, you might take the ampersand (&) or the pound (#) or the backslash (\) or any character they never use. This is a truly minor change you need not worry about.
[*] If your clients use different separators but each cell always has the same, we might relieve you of the manual task of setting many separators. I can imagine a little function that replaces the separator used by the client with the separator needed by the macro. Sort of, select the cell, activate the macro (perhaps by double-click), the macro ask you which separator the client used (perhaps we find a way for the macro to find out by itself), the altered string is pasted to column D (leaving the original intact).

Let me know how I can help you.

[sreek7680]

Hello Sisyphus,

Apologies for all confusions.

I will try to explain this still further.

Client sends terms and we dont change the format of original terms. I copy same terms into D column and put delimiter for splitting.

A1: Rash.cough.sneeze.fever.
A2: Leg pain.heart failure.stomach pain.fever.

The same terms will be copied in D1 and D2. A1 and A2 are original terms. I will manually put (;) in D row, as the terms might have periods (.), commas, slash or dashes in it.

D1:Rash.;cough.;sneeze.;fever.;
D2: Leg pain.;heart failure.;stomach pain.;fever.;

We need to create number of rows which are equal to number of semicolons.

(No. of rows = No. of semicolons.)

If we split D1, terms should come into below rows and the next term (A2) should be on A6, so the same term will be on D6 now. We need to separate terms by keeping original terms in A column. One empty row after separation.

After split::
A1 contains --> Rash.cough.sneeze.fever.

D1: Rash
D2: cough
D3: seeze
D4: fever
One empty row below i.e D5'

A6: Leg pain.heart failure.stomach pain.fever.

D6: Leg pain
D7: heart failure
D8: stomach pain
D9: fever
One empty row i.e D10

and so on...

Hope I am clear. Please check the attached file for more details.

Thank you sooo much for your help.

Sreek.

[Sisyphus]

Hi Sreek,
OK, I have remodeled the macro a little and hope that it now does what you want.
In the process the 'Control Panel' became simpler. Here is a copy of it:

Code: Select all

    Const FirstDataRow As Long = 2
    Const DataCol As String = "D"


If the first row containing data in your worksheet isn't 2, please change the row number.
The column to work on is D. A will not be touched.

The program presumes that D contains semicolon-delimited strings which it splits and replaces. All action takes place on the Active Sheet, meaning the data on the sheet which you see when you call the macro will be altered. You can try this out on the attached workbook.