All

I have a large data set (???) that needs tidying before being 'uploaded' to stats program.

As this data set will be updated on a bi annual basis, I am looking for a solution to a main data tidying exercise.

In one field of data, names of authors, there can be a single author or up to 25 authors in a single cell.

What I need to do is to insert duplicate rows for each of these authors. That is if there are 10 authors, I need to insert 9 rows BUT each row will only contain one name.

My solution for this round was to insert rows and then manually remove names so that each author had one row.

(All other data is repeated for each author)

In addition, where there is an * after the name I need to add Int to column D and where there is a # I need to add Ext to column D

See attached example

Any ideas or thoughts would be much appreciated as I don't necessarily want to be copying and pasting and deleting for two weeks again to get data in usable format.

TIA

Shaz

ERA-EE.xlsx

I have a large data set (???) that needs tidying before being 'uploaded' to stats program.

As this data set will be updated on a bi annual basis, I am looking for a solution to a main data tidying exercise.

In one field of data, names of authors, there can be a single author or up to 25 authors in a single cell.

What I need to do is to insert duplicate rows for each of these authors. That is if there are 10 authors, I need to insert 9 rows BUT each row will only contain one name.

My solution for this round was to insert rows and then manually remove names so that each author had one row.

(All other data is repeated for each author)

In addition, where there is an * after the name I need to add Int to column D and where there is a # I need to add Ext to column D

See attached example

Any ideas or thoughts would be much appreciated as I don't necessarily want to be copying and pasting and deleting for two weeks again to get data in usable format.

TIA

Shaz

ERA-EE.xlsx

and only thing i would do is, will add the cleansed data in a new sheet rather than adding new rows inbetween and clean it. Coz, it might cause some disturbance to existing data.

-Bala

Please run the macro "Sample" in the attachment. The output will be generated in a new sheet called Output.

HTH

Sid

```
Sub Sample()
Dim i As Long, j As Long, k As Long, LastRow As Long, r As Long, pos As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim HashArray() As String, StarArray() As String
Dim strTemp As String
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Output").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set ws1 = Sheets("Raw Data")
Set ws2 = Sheets.Add
ws2.Name = "Output"
LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
r = 1
For i = 1 To LastRow
If InStr(1, ws1.Range("B" & i).Value, "#") Or InStr(1, ws1.Range("B" & i).Value, "*") Then
strTemp = ws1.Range("A" & i).Value
ws2.Range("A" & r).Value = ws1.Range("A" & i).Value
HashArray = Split(ws1.Range("B" & i).Value, "#")
For j = 0 To UBound(HashArray) - 1
If InStr(1, HashArray(j), "*") Then
StarArray = Split(HashArray(j), "*")
For k = 0 To UBound(StarArray)
If Left(StarArray(k), 1) = "," Then
ws2.Range("B" & r).Value = Mid(StarArray(k), 3)
Else
ws2.Range("B" & r).Value = StarArray(k)
End If
pos = InStr(1, ws1.Range("B" & i).Value, StarArray(k)) + Len(StarArray(k))
If Mid(ws1.Range("B" & i).Value, pos, 1) = "#" Then
ws2.Range("C" & r).Value = "EXT"
ElseIf Mid(ws1.Range("B" & i).Value, pos, 1) = "*" Then
ws2.Range("C" & r).Value = "INT"
End If
ws2.Range("A" & r).Value = strTemp
r = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1
Next
Else
If Left(HashArray(j), 1) = "," Then
ws2.Range("B" & r).Value = Mid(HashArray(j), 3)
Else
ws2.Range("B" & r).Value = HashArray(j)
End If
pos = InStr(1, ws1.Range("B" & i).Value, HashArray(k)) + Len(HashArray(k))
If Mid(ws1.Range("B" & i).Value, pos, 1) = "#" Then
ws2.Range("C" & r).Value = "EXT"
ElseIf Mid(ws1.Range("B" & i).Value, pos, 1) = "*" Then
ws2.Range("C" & r).Value = "INT"
End If
ws2.Range("A" & r).Value = strTemp
r = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1
End If
Next
End If
r = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1
Next i
End Sub
```

ERA-EE.xlsm
Please try this file. let me know if you see any discrepancies.

Sid

Raw-Data.xls

u r the Expert!!!!

The will have to be amended for 30 columns :)

>>>The only one I can see from what you have done is that the last row does not record and INT or EXT .... Does part of the code need to be 'extended'

Let me check that for you.

Sid

That is good enough. There is just a minor change which I will upload in the next few moments

Sid

I just noticed that it created that extra row when it shouldn't it is just a duplicate of the above row :)

I will incorporate that in the code.

Sid

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.