Hi all,
I have a sheet of data with the following:
A1 Text B1 Text 1|Text 2|Text 3|Text 4|
A2 Text B2 Text 5|Text 6|Text 7|Text 8|
I'd like to create a new sheet that would (a) Convert Pipe Delimited Text into rows and (b) append/concatenate A1 Text to each B1 Text Element. So, final list would look like:
A1 Text;B1Text1
A1 Text;B1Text2
A1 Text;B1Text3
A1 Text;B1Text4
A2 Text;B2Text5
A2 Text:B2Text6
A2 Text;B2Text7...etc
I'm trying to create a file for social graph analysis and these are "nodes" in the network. Any help would be GREATLY appreciated. I have a script now that successfully converts Pipe delimited data from B1 in to multiple rows, but it does not append/concatenate data from A1. Here is that script I'm using (if it helps)...
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
Dim commaPos As Integer
' Copy from column A to column B.'
fromCol = "A"
toCol = "B"
fromRow = "1"
toRow = "1"
' Go until no more entries in column A.'
inVal = Range(fromCol + fromRow).Value
While inVal <> ""
' Go until all sub-entries used up.'
While inVal <> ""
Range(fromCol + fromRow).Select
' Extract each subentry.'
commaPos = InStr(1, inVal, "|")
While commaPos <> 0
' and write to output column.'
outVal = Left(inVal, commaPos - 1)
Range(toCol + toRow).Select
Range(toCol + toRow).Value = outVal
toRow = Mid(Str(Val(toRow) + 1), 2)
' Remove that sub-entry.'
inVal = Mid(inVal, commaPos + 1)
While Left(inVal, 1) = " "
inVal = Mid(inVal, 2)
Wend
commaPos = InStr(1, inVal, "|")
Wend
' Get last sub-entry (or full entry if no commas).'
Range(toCol + toRow).Select
Range(toCol + toRow).Value = inVal
toRow = Mid(Str(Val(toRow) + 1), 2)
inVal = ""
Wend
' Advance to next source row.'
fromRow = Mid(Str(Val(fromRow) + 1), 2)
Range(fromCol + fromRow).Select
inVal = Range(fromCol + fromRow).Value
Wend
End Sub
Bookmarks