[mrincognito]

Hello,

I have 2 data sheets in an Excel workbook that have ~8000 rows of data each. Each row has 2 columns. Col A is an entity ID and Col B contains a set of long string values (i.e., institutional affiliation and location), each value separated by a semicolon (. More specifically, Col A is a paper ID#, Col B includes the institutional affiliations for each author.

What I need to do is to take the distinct values in Col B and separate them into individual rows for data processing. Also, when I separate the values, I need to retain the relationship between the values in Col B and Col A. That is, I need to put each institutional affiliation in its own row, but ensure that the relationship to the paper remains intact. Put another way, I want to cut everything that follows a ; in Col B, insert a row, paste what I cut into the next row in Col B, and then go back up one row, over one cell, copy the unique ID, go down one cell, and paste.

So here is my problem: when I execute the find function in Excel 2008 and search for the semicolon, it returns the entire cell. Normally, if one executes a find command, it highlights the character. The next logical step would be a shift+end to highlight the remainder of the text to the end of the line. This does not work because the find command returns the entire cell. Does anyone have any advice on how to fix this? Right now I am thinking I need to export the data into another program, execute the find and replace, and then re-import the data. I would rather find a more elegant solution to the problem.

-MrInc



Here

[slurpee55]

First, welcome to the Forum!!!
Give me some fake data and I will mess with a solution.

[bomb #21]

You have Mac?

[mrincognito]

See attached file. The data are publicly available, so there are no concerns re: privacy. Thank you for the help.

Yes, I do have a mac. However, I will be doing most of my work on my desktop, which is a Windows machine.

[bomb #21]

Originally Posted by mrincognito

See attached file. The data are publicly available, so there are no concerns re: privacy. Thank you for the help.

Yes, I do have a mac. However, I will be doing most of my work on my desktop, which is a Windows machine.

Yeah, that was directed at slurpee.

Who will now provide your solution.

[bomb #21]

My bad, slurpee's busy.

Tried this on your sample, ran OK.

Make sure your actual data's backed up before you try it on that.

Sub test()
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Output").Delete
Application.DisplayAlerts = True
Home = ActiveSheet.Name
Sheets.Add
ActiveSheet.Name = "Output"
Range("A1") = "ISI": Range("B1") = "Other"
Sheets(Home).Select
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B1:B" & LastRow)
x = Len(Cell) - Len(Replace(Cell, ";", "")) + 1
Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(x) = Cell.Offset(, -1)
AllAdds = Cell & ";"
For i = 1 To x
ThisAdd = Left(AllAdds, InStr(AllAdds, ";") - 1)
Sheets("Output").Range("B" & Rows.Count).End(xlUp).Offset(1) = _
WorksheetFunction.Trim(ThisAdd)
AllAdds = Mid(AllAdds, InStr(AllAdds, ";") + 1, 256)
Next i
Next Cell
Sheets("Output").Select
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

[slurpee55]

Dang, I just got back here! Nice work bomb!
Been listening to the news about more resignations in the UK Parliament....
Oh, and nope, Macs are nice, but too pricey for what I need a PC for.

[slurpee55]

bomb, that went into my folder of useful little VBA codes - I could see using that quite a lot in the future. Thanks!!!!

[bomb #21]

Yeah, the meeeja schadenfreude is in overdrive since ... weeks ago.

YW

[slurpee55]

at first I thought you were referring to yourself...oh, the Parliament, yep....
It seems Canada is having some problems too - here in the US, well, we just assume that the politicians are largely crooks (well, actually, I tend to think they are more stupid than crooked) - Obama excepted!!

[mrincognito]

Thank you very much. This worked very well.

[slurpee55]

mrincognito, please use the button at the top of the page to mark this thread as Solved.
See you around!