Reorganizing rows and columns in excel

T

tabihussain

New Member
Joined
Oct 24, 2017
Messages
6
Hi there,
I really hope someone has a solution for this problem. I want to analyze data from a clinical lab in SPSS. The data I have has several thousand samples, so I want an easy way of transferring it in SPSS for analysis. Here is a screenshot of the data. Now the problem is against one patient, efficacy to a panel of drugs is tested and between patients, the panel of drugs that is tested can also vary. The data is pasted below. The left most column is the sample ID number as you can see the same ID number prevails for about 13 cases, so this is one patient's result. My problem is I want to export this data in spss in a way that all the drugs (Amikacin, Ampicillin etc etc) become variables (columns), each with values 1,0 etc for one patient, so that all data for one ID number/patient is limited to one row only. Is there any way to reorganize this data? If not a shortcut, at least less tedious than manually copying all rows for one ID? I would want the conversion to somewhat like the second table.
I would really appreciate any ideas.
Best Regards
Tabi

1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeAmikacinR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeAmoxicillin + Clavulanic acidR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeAmpicillinR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeCeftriaxoneR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeCiprofloxacinR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeCotrimoxazoleR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeFosfomycinS
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeGentamicinR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeImipenemR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeMeropenemR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeNitrofurantoinR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaePiperacillin+TazobactamR
1442323005:50.7***M80YUrine For C/SKlebsiella pneumoniaeSulzoneR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeDoxycyclineS
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeGentamicinR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeMeropenemR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeAmikacinR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaePolymyxin BS
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeCotrimoxazoleS
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeTigecyclineI
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeAmoxicillin + Clavulanic acidR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeCeftriaxoneR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeImipenemR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeAmpicillinR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeCiprofloxacinR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaeSulzoneR
1442327940:50.3***M55YNBL for C/SKlebsiella pneumoniaePiperacillin+TazobactamR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeCotrimoxazoleR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeCeftriaxoneR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaePiperacillin+TazobactamS
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeAmikacinI
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeAmoxicillin + Clavulanic acidR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeCiprofloxacinR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeTigecyclineS
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeSulzoneR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeDoxycyclineR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeGentamicinR
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeImipenemS
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeMeropenemS
1442331621:01.9***M60YFluid For C/SKlebsiella pneumoniaeAmpicillinR
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaTigecyclineS
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaDoxycyclineR
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaCiprofloxacinR
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaGentamicinR
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaAmikacinS
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaPiperacillin+TazobactamS
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaMeropenemS
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaAmoxicillin + Clavulanic acidR
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaCeftriaxoneR
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaSulzoneS
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaImipenemS
1442368036:02.2*M12DPus for C/SKlebsiella oxytocaCotrimoxazoleS

<tbody>
</tbody>
Intended Output
ID numberAgeGenderIsolateAmikacinAmoxicillin + Clavulanic acidAmpicillinCeftriaxoneCefipimeCeftazidineSulzoneTigecyclineCiprofloxacinCotrimoxazoleDoxycyclineFosfomycinGentamicinImipenemMeropenemNitrofurantoinPiperacillin+TazobactamPolymyxin B
1442323080MKlebsiella RRRRRRRRRRRSRRRRRR

<colgroup><col width="64" span="22" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
I can build a VBA solution for you so that the data is in the format you want in Excel that can then be exported to SPSS.
 
Upvote 0
With your data on sheet1 starting "A2" and Results on sheet2 starting "A1".
Try this:-

Code: 
[COLOR="Navy"]Sub[/COLOR] MG24Oct53
[COLOR="Navy"]Dim[/COLOR] Hds [COLOR="Navy"]As[/COLOR] Variant, Hdic [COLOR="Navy"]As[/COLOR] Object, H [COLOR="Navy"]As[/COLOR] Variant, Dic [COLOR="Navy"]As[/COLOR] Object, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Hdic = CreateObject("scripting.dictionary")
Hdic.CompareMode = vbTextCompare
  Hds = Array("Amikacin", "Amoxicillin + Clavulanic acid", "Ampicillin", "Ceftriaxone", "Cefipime", "Ceftazidine", "Sulzone", "Tigecycline", "Ciprofloxacin", "Cotrimoxazole", "Doxycycline", "Fosfomycin", "Gentamicin", "Imipenem", "Meropenem", "Nitrofurantoin", "Piperacillin+Tazobactam", " Polymyxin B")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] H [COLOR="Navy"]In[/COLOR] Hds: Hdic(H) = Hdic.Count: [COLOR="Navy"]Next[/COLOR] H
      [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
         Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = Union(Dic(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
    ReDim ray(1 To Dic.Count + 1, 1 To UBound(Hds) + 5)
    ray(1, 1) = "Id": ray(1, 2) = "Age": ray(1, 3) = "Gender": ray(1, 4) = "Isolate"
        [COLOR="Navy"]For[/COLOR] Ac = 0 To UBound(Hds)
            ray(1, Ac + 5) = Hds(Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
   [COLOR="Navy"]Dim[/COLOR] t
   c = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
        c = c + 1
        ray(c, 1) = K
        ray(c, 2) = Dic(K)(1).Offset(, 4)
        ray(c, 3) = Dic(K)(1).Offset(, 3)
        ray(c, 4) = Dic(K)(1).Offset(, 7)
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Dic(K)
                t = Hdic(R.Offset(, 8).Value)
                [COLOR="Navy"]If[/COLOR] Hdic.exists(R.Offset(, 8).Value) [COLOR="Navy"]Then[/COLOR]
                   
                    ray(c, Hdic(R.Offset(, 8).Value) + 5) = R.Offset(, 9).Value
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] R
    [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, UBound(Hds) + 5)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thankyouuu so much Mick!!! you are a genius!!
And thankyou frank_AL for the offer.
So glad! You guys are great :):):)

MickG said:
With your data on sheet1 starting "A2" and Results on sheet2 starting "A1".
Try this:-

Code: 
[COLOR=Navy]Sub[/COLOR] MG24Oct53
[COLOR=Navy]Dim[/COLOR] Hds [COLOR=Navy]As[/COLOR] Variant, Hdic [COLOR=Navy]As[/COLOR] Object, H [COLOR=Navy]As[/COLOR] Variant, Dic [COLOR=Navy]As[/COLOR] Object, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, R [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=Navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]Set[/COLOR] Hdic = CreateObject("scripting.dictionary")
Hdic.CompareMode = vbTextCompare
  Hds = Array("Amikacin", "Amoxicillin + Clavulanic acid", "Ampicillin", "Ceftriaxone", "Cefipime", "Ceftazidine", "Sulzone", "Tigecycline", "Ciprofloxacin", "Cotrimoxazole", "Doxycycline", "Fosfomycin", "Gentamicin", "Imipenem", "Meropenem", "Nitrofurantoin", "Piperacillin+Tazobactam", " Polymyxin B")
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] H [COLOR=Navy]In[/COLOR] Hds: Hdic(H) = Hdic.Count: [COLOR=Navy]Next[/COLOR] H
      [COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
         Dic.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        Dic.Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] Dic(Dn.Value) = Union(Dic(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
    ReDim ray(1 To Dic.Count + 1, 1 To UBound(Hds) + 5)
    ray(1, 1) = "Id": ray(1, 2) = "Age": ray(1, 3) = "Gender": ray(1, 4) = "Isolate"
        [COLOR=Navy]For[/COLOR] Ac = 0 To UBound(Hds)
            ray(1, Ac + 5) = Hds(Ac)
        [COLOR=Navy]Next[/COLOR] Ac
   [COLOR=Navy]Dim[/COLOR] t
   c = 1
   [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] Dic.keys
        c = c + 1
        ray(c, 1) = K
        ray(c, 2) = Dic(K)(1).Offset(, 4)
        ray(c, 3) = Dic(K)(1).Offset(, 3)
        ray(c, 4) = Dic(K)(1).Offset(, 7)
            [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] Dic(K)
                t = Hdic(R.Offset(, 8).Value)
                [COLOR=Navy]If[/COLOR] Hdic.exists(R.Offset(, 8).Value) [COLOR=Navy]Then[/COLOR]
                   
                    ray(c, Hdic(R.Offset(, 8).Value) + 5) = R.Offset(, 9).Value
                [COLOR=Navy]End[/COLOR] If
            [COLOR=Navy]Next[/COLOR] R
    [COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, UBound(Hds) + 5)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...
 
Upvote 0
Also I have one small addition that I want to make, if I want to add a column "Sample Type", in which the data from the seventh column (urine for C/S, blood for C/S etc) goes how do I do that in this code. I suppose I have to write ray(1,5)= "Sample_Type"? Thanks for all the help.
 
Upvote 0
I changed the code a little myself and it seems to do the job now. I have also added a few more drugs. I would like to confirm that I haven't made a mistake and that it will work for all cases. So here it is:

Code: 
Sub MG24Oct53()
Dim Hds As Variant, Hdic As Object, H As Variant, Dic As Object, Ac As Long
Dim Rng As Range, Dn As Range, n As Long, K As Variant, R As Range, c As Long
With Sheets("Sheet1")
    Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With
Set Hdic = CreateObject("scripting.dictionary")
Hdic.CompareMode = vbTextCompare
  Hds = Array("Amikacin", "Amoxicillin + Clavulanic acid", "Ampicillin", "Ceftriaxone", "Cefixime", "Ceftazidine", "Sulzone", "Tigecycline", "Ciprofloxacin", "Cotrimoxazole", "Doxycycline", "Fosfomycin", "Gentamicin", "Imipenem", "Meropenem", "Nitrofurantoin", "Piperacillin+Tazobactam", "Polymyxin B", "Aztreonam", "Levofloxacin", "Moxifloxacin", "Cephradine", "Cefepime")
    For Each H In Hds: Hdic(H) = Hdic.Count: Next H
      Set Dic = CreateObject("scripting.dictionary")
         Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not Dic.exists(Dn.Value) Then
        Dic.Add Dn.Value, Dn
    Else
        Set Dic(Dn.Value) = Union(Dic(Dn.Value), Dn)
    End If
Next
    ReDim ray(1 To Dic.Count + 1, 1 To UBound(Hds) + 6)
    ray(1, 1) = "Id": ray(1, 2) = "Age": ray(1, 3) = "Gender": ray(1, 4) = "Isolate": ray(1, 5) = "Sample"
        For Ac = 0 To UBound(Hds)
            ray(1, Ac + 6) = Hds(Ac)
        Next Ac
   Dim t
   c = 1
   For Each K In Dic.keys
        c = c + 1
        ray(c, 1) = K
        ray(c, 2) = Dic(K)(1).Offset(, 4)
        ray(c, 3) = Dic(K)(1).Offset(, 3)
        ray(c, 4) = Dic(K)(1).Offset(, 7)
        ray(c, 5) = Dic(K)(1).Offset(, 6)
            For Each R In Dic(K)
                t = Hdic(R.Offset(, 8).Value)
                If Hdic.exists(R.Offset(, 8).Value) Then
                   
                    ray(c, Hdic(R.Offset(, 8).Value) + 6) = R.Offset(, 9).Value
                End If
            Next R
    Next K
With Sheets("Sheet2").Range("A1").Resize(c, UBound(Hds) + 6)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
End With
End Sub
Regards
Tabi
 
Upvote 0
You're welcome
The code looks alright, but I think you are in a better place to judge if its working properly or not !!.
I did note that you have changed "Set Rng" to start at "A1", presumably you have no Data Headers.
Because of that, your code on my sheet(with headers) returned the second row in Results sheet, as a blank row???
 
Upvote 0
Yes it does seem to work fine. Yeah I don't have headers as they are no use in spss. Thanks, you solved a big problem for me.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Copying a row of data from another sheet and pasting it into another sheet into the correct column headings
Replies
8
Views
419
kevin9999
K
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
429
harzer
H
H
  • Question
Insert the space character in the penultimate place of all the cells of columns "A" - "B" & "C".
Replies
3
Views
250
Fluff
Fluff
N
  • Question
Advanced Count Formula
Replies
4
Views
492
natheplas
N
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
345
awoohaw
awoohaw

Forum statistics

Threads
1,216,076
Messages
6,128,670
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top