Data Format

[TabulaRasa]

Hi,
I need to convert a string of data within a single cell into multiple rows, for multiple columns. The data in the first column (column A) is in the format of Yes [1], No [2], No [2], etc. and is associated with data in the following column (column B) which is in the format of xxxxx [123.1], xxxxxxxx [123.7], etc.

So essentially my problem is that I need to convert the data within two columns into multiple rows while still keeping it confined to those two columns and maintaining the relationship between them. I would appreciate any advice that anyone has to give!

 

[ranman256]

a before / after example would be nice.

 

[TabulaRasa]

a before / after example would be nice.

Before:	Accessory Information	No [2], No [2], No [2], Yes [3] <tbody> </tbody>	topic number 1 [987.6], topic number 2 [2x8.1], topic, number 3 [5.0], topic number, 4 [ab99] <tbody> </tbody>
After:	Accessory Information	No [2] <tbody> </tbody>	topic number 1 [987.6] <tbody> </tbody>
	Accessory Information	No [2]	topic number 2 [2x8.1] <tbody> </tbody>
	Accessory Information	No [2]	topic, number 3 [5.0] <tbody> </tbody>
	Accessory Information	Yes [3]	topic number, 4 [ab99] <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

This is what I'm going for. Sorry if the formatting isn't standard, this is my first post. Does this help? Thank you for your reply!

 

[ranman256]

put these in a macro, then run PARSETXT

'--------------
Public Sub ParseTxt()
'--------------
Dim col1 As New Collection, col2 As New Collection
Dim vWord1, vWord2
Dim i As Integer
Dim shtNew As Worksheet, shtSrc As Worksheet


Set shtSrc = ActiveSheet
Sheets.Add
Set shtNew = ActiveSheet
shtSrc.Activate
Range("A1").Select
While (ActiveCell.Value) <> ""
   vWord1 = ActiveCell.Value
   vWord2 = ActiveCell.Offset(0, 1).Value
   
   Parse1Cell vWord1, col1
   Parse1Cell vWord2, col2
   
   shtNew.Activate
   For i = 1 To col1.Count
        ActiveCell.Value = col1(i)
        ActiveCell.Offset(0, 1).Value = col2(i)
        ActiveCell.Offset(1, 0).Select  'next row
   Next
   shtSrc.Activate
   ActiveCell.Offset(1, 0).Select  'next row
   Set col1 = New Collection
   Set col2 = New Collection
Wend
shtNew.Activate


Set col1 = Nothing
Set col2 = Nothing
End Sub


'--------------
Private Sub Parse1Cell(ByVal pvWord, pvCol As Collection)
'--------------
Dim vVal
dim i as integer

i = InStr(pvWord, ",")
While i > 0
   vVal = Left(pvWord, i - 1)
   pvWord = Trim(Mid(pvWord, i + 1))
   pvCol.Add vVal
   i = InStr(pvWord, ",")
Wend
pvCol.Add pvWord
End Sub

 

[Rick Rothstein]

Before:	Accessory Information	No [2], No [2], No [2], Yes [3] <tbody> </tbody>	topic number 1 [987.6], topic number 2 [2x8.1], topic, number 3 [5.0], topic number, 4 [ab99] <tbody> </tbody>
After:	Accessory Information	No [2] <tbody> </tbody>	topic number 1 [987.6] <tbody> </tbody>
	Accessory Information	No [2]	topic number 2 [2x8.1] <tbody> </tbody>
	Accessory Information	No [2]	topic, number 3 [5.0] <tbody> </tbody>
	Accessory Information	Yes [3]	topic number, 4 [ab99] <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

This is what I'm going for. Sorry if the formatting isn't standard, this is my first post. Does this help? Thank you for your reply!

How does this table of values relate to the two columns of information you said you had in Message #1... I see the Yes [1], No [2], No [2], etc. part, but what about the xxxxx [123.1], xxxxxxxx [123.7], etc. part? And what about the columns... in Message #1, you said these were in Column A and B, but you show something completely different in Column A in your table... can you clarify that too please.

 

[TabulaRasa]

Ranman, when I run that macro it creates a new sheet but does not populate any data and I receive VBA error 400. I'm unsure of what this means, but hopefully it helps to diagnose the issue.

Mr. Rothstein, I was using general placeholder names for the columns - I did not know that it mattered. As the data that I'm working with is sensitive, I'm trying to give everyone enough information to help without breaching any privacy laws. The particular columns that will need to be converted into rows are I, J, and K (sorry for not including column K earlier - I thought a third column would complicate the explanation). I likely did a poor job with my initial explanation in Message #1. Please allow me to summarize again, and go a bit more in depth.

Currently, there are ~10,000 rows of data. Each row has the following information format in it:

A	B	C	D	E	F	G	H	I	J	K
TEXT	DATE	DATE	TEXT	GENERAL (INCLUDES NUMBERS)	TEXT	CURRENCY	TEXT (INCLUDES NUMBERS)	No [2], Yes [3], etc.	Yes [3], No [2], etc.	TEXT [123.1], TEXT [123.2], etc.

<tbody>
</tbody>

The goal is to take columns I-K and make them multiple rows, with the data staying relative between the columns (for example, the first row would be the following:

I	J	K
No [2]	Yes [3]	TEXT [123.1]

<tbody>
</tbody>

There are two additional complications. The first is that columns A-H need to be duplicated along with the now separated information from columns I-K (it is identifying information). The second complication is that the TEXT information in column K contains commas, (for example: obesity, adult) so any comma delimited approach that I could think of will not work.

Hopefully this explains the issue and the goal a bit more effectively! I really appreciate everyone's assistance.