Split One Column at Delimiter While Copying Contents of Second Column to Populate New Rows

Hi

Here goes

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

Here goes
...
rylo

Thank you, rylo. What will happen when Column B does not start with DRUGS: ? I suppose I should have added a wider variety. I did not mean for those samples to be the full representative of the data. Other examples go as follows:

22203 SOCIOLOGICAL DIMENSIONS: CRIME AND VIOLENCE|WOMEN
18444 STATISTICAL TECHNIQUES|TESTS
20633 Survey and Sampling Design|Validity and Reliability
24830 SURVEY AND SAMPLING DESIGN|SURVEY DATA ANALYSIS
24745 SURVEY RESEARCH|VALIDITY AND RELIABILITY
11336 TREATMENT: FAMILY THERAPY|TREATMENT: BEHAVIOR THERAPY
23708 TREATMENT: ALCOHOLICS ANONYMOUS|TREATMENT: EVALUATION OF PROGRAMS
11235 TREATMENT: DESCRIPTIONS OF PROGRAMS: COMMUNITY BASED|TREATMENT: BEHAVIOR THERAPY|TREATMENT: EVALUATION OF PROGRAMS: OUTCOMES
11625 TREATMENT: DESCRIPTIONS OF PROGRAMS|TREATMENT: EVALUATION OF PROGRAMS: STATISTICS|TREATMENT: EVALUATION OF PROGRAMS: UTILIZATION AND COSTS|TREATMENT: PERSONNEL
14473 TREATMENT: SELF HELP|TREATMENT: DESCRIPTIONS OF PROGRAMS
13560 TREATMENT: DESCRIPTIONS OF PROGRAMS: PRISON BASED|TREATMENT: SPECIAL POPULATIONS
11230 TREATMENT: EVALUATION OF PROGRAMS|TREATMENT: DRUG THERAPY
11183 TREATMENT: SELF HELP|WOMEN: TREATMENT
13354 TREATMENT: THEORY|TREATMENT: SELF HELP
13737 TREATMENT: SOCIAL MODEL: HISTORY|TREATMENT: SOCIAL MODEL: LITERATURE

Hi

Can you put up an example file with the representative data. Bit hard to actually determine what goes where from your list on the post.

Also, show how that data should be finally presented.

rylo

Records Samples.xlsx

I have (hopefully successfully) attached a file that shows a wider variety of what I have (in Sheet 1) and a start to what I want it to look like (in Sheet 2 rows 2-10). Note that for each subject in Column B (record 20357 for example has the following subjects - Adolescence: Surveys|Age|Patterns of Drinking Behavior: General and Miscellaneous|S** Differences|Elderly), the record number (20357) has been copied and made into its own row for each Subject that was followed by the delimiter, |.

Technically, this file gets even more complicated with an additional three columns, but if I can get just Column A and Column B doing what I need it to do, I can easily move these records cleanly back to my original file. It is cleaning these two columns up that has proved to be more difficult than I would like

Thanks again for any assistance here!

Thanks, MarvinP. I think I may not have been clear enough as to what I am looking for. The screen-cast really was quite beneficial. Doing the step-by-step allows me to understand it better. It was really appreciated! Unfortunately though, I am actually looking to break up the delimiter to create new rows. What needs to happen is that for each delimiter, I need to create a new row with the next piece of data. This means where Column A is 19685 and Column B reads: DEFINITIONS AND THEORIES|ATTITUDES|PREVENTION AND TREATMENT, Column B would be broken into three rows and each row would have Column A duplicated each time.

*Columns separated with " - " since I can't figure out tabbing issues*

Start with:

ColA - ColB
19685 - DEFINITIONS AND THEORIES|ATTITUDES|PREVENTION AND TREATMENT


And make it become:

ColA - Column B
19685 - DEFINITIONS AND THEORIES
19685 - ATTITUDES
19685 - PREVENTION AND TREATMENT

Try the attached

Please Login or Register  to view this content.

I will have to try this when I get back in just a little bit.
Thanks.

Hey MarvinP, According to the screen-cast at least, it looks exactly like what I am trying to do! Hopefully I'll be able to implement it.

I was hoping to play with the file a bit when I was home last night, but it didn't work out. As soon as I can get into the office today I will certainly be testing this method out.

I'll keep you posted.

jindon,

You method seems to work for what I need, which is keeping the data from subject in non-alpha order. I need the data to stay in the order it was original entered within the delimiter. The trouble is that when I try to use it on my records I continue to receive various errors. I don't know if it is a problem with my macros or security, though I am guessing it is a little of both. Just in case, I have attached a copy of the file I am trying to work with. Sheet1 is the whole file that includes a variety of other column information. Sheet2 is the data pulled out. I am hoping to be able to run the macro and copy and past the data back in.

I will continue to try here and see if I can fix whatever errors are being caused.

Thank you.

subjectOrdering.xlsx

Hello MarvinP,
I know I keep clarifying issues, but I realized something once I got to work. I need the data to stay in the order it was initially entered into the delimiter. It looks like the solution offered by jindon works, but I am having trouble implementing it on my own system here at work. I have attached the file in case it clears up what I am looking for. The long version of the story is that I have a series of data that includes the record number, the subjects listed with a delimiter, and the subjects broken out individually but without a sort order. I need to break the subjects out of their delimiter within the order they were placed within the delimiter so that I can go through and enter the sort order that is intended. It is a meaty project and it seems if I can implement jindon's solution I will have what I need. Otherwise, if I was not as concerned about insertion order, your solution would work to break the files out.

Hopefully I can find some resolution to this shortly.

Thank you for the help.

Originally Posted by medailSacrosanct

jindon,

You method seems to work for what I need, which is keeping the data from subject in non-alpha order. I need the data to stay in the order it was original entered within the delimiter. The trouble is that when I try to use it on my records I continue to receive various errors. I don't know if it is a problem with my macros or security, though I am guessing it is a little of both. Just in case, I have attached a copy of the file I am trying to work with. Sheet1 is the whole file that includes a variety of other column information. Sheet2 is the data pulled out. I am hoping to be able to run the macro and copy and past the data back in.

I will continue to try here and see if I can fix whatever errors are being caused.

Thank you.

Attachment 171484

What are you really trying to do?

Originally Posted by jindon

What are you really trying to do?

Ultimately, I have a table with 5 or 6 columns that are trying to tie in several databases. I needed to parse ColumnB in the order they are separated with a delimiter. Your macro did end up working for me when combined with a second macro to go to the next blank row. I ultimately then created a third macro to shortcut your macro plus the next blank row macro. This allowed me to hold down the shortcut for this third macro.

I now have to go through and enter the numbers 1-... and start with a new range, 1-...., with each new record number. It is an old database with numerous relationship databases for various needs.

I think the only part I have to figure out is how to do the numbering if there is any other way without having to literally count, 1, 2, 3, etc but for now, this question is solved!

Thanks again to everyone for all the help!

Originally Posted by stunn

Which one is closer to reality?

Hello stunn, The closer to reality is the file in post 12. That is why I called the post 6 file, records sample. I did not want to complicate the question because I knew if I could simply parse the second column I could copy and paste the information back to the file as needed. I've always thought on these forums that presenting 15 different problems at one time was a no-no, though perhaps I am wrong?

The ultimate problem that I have is that I have roughly 6500 titles (represented by the column "record number") that need to have their subjects matched up using a subject identification number. In order to begin that process, I needed to take the delimiter out of the data for the list of subjects in the order they were originally entered as these were entered in a priority list. From there I have to go through and number these in a third column so that the "list order" will be recorded. This gains access to a third table. The first table is the books themselves (represented by "record number"), the second table is the list of subjects (at this point represented by the column "subject") and the third table which will be the connecting table which brings together the books and the subject. In this third column I will use the list order of the subjects and the subject identification number (to be determined at a later point once they get "filed away" if you will).

These are all going to play out in a sql database, but I realized I needed to use excel to massage the files themselves and get the data the way that I needed it.

What you see in the file in post 12 is a copy of the data with the information somewhat previously parsed out, but not in any working order. Thus the need to start over and try to reconnect the data in a proper way. It is a large dataset that I realized I needed to rework, thus the pulling of columns A and B to be recopied down the road.

I hope this clarifies exactly what my ultimate goal is and I hope it is clearer why I did not put all of this into writing in the first place. It felt like my initial request of parsing out the second column (where I admittedly forgot to mention I need to do so in the order it was entered) was the clearest way to not become entangled in the bigger project.

Thank you to everyone who offered a suggestion! Your time was appreciated!