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

[TABLE="width: 783"]
<tbody>[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Amikacin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Amoxicillin + Clavulanic acid[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ampicillin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ceftriaxone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ciprofloxacin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Cotrimoxazole[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Fosfomycin[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Gentamicin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Imipenem[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Meropenem[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Nitrofurantoin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Piperacillin+Tazobactam[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]05:50.7[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]80[/TD]
[TD]Y[/TD]
[TD]Urine For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Sulzone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Doxycycline[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Gentamicin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Meropenem[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Amikacin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Polymyxin B[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Cotrimoxazole[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Tigecycline[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Amoxicillin + Clavulanic acid[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ceftriaxone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Imipenem[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ampicillin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ciprofloxacin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Sulzone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423279[/TD]
[TD="align: right"]40:50.3[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]55[/TD]
[TD]Y[/TD]
[TD]NBL for C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Piperacillin+Tazobactam[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Cotrimoxazole[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ceftriaxone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Piperacillin+Tazobactam[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Amikacin[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Amoxicillin + Clavulanic acid[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ciprofloxacin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Tigecycline[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Sulzone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Doxycycline[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Gentamicin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Imipenem[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Meropenem[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423316[/TD]
[TD="align: right"]21:01.9[/TD]
[TD]***[/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Y[/TD]
[TD]Fluid For C/S[/TD]
[TD]Klebsiella pneumoniae[/TD]
[TD]Ampicillin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Tigecycline[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Doxycycline[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Ciprofloxacin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Gentamicin[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Amikacin[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Piperacillin+Tazobactam[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Meropenem[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Amoxicillin + Clavulanic acid[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Ceftriaxone[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Sulzone[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Imipenem[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]14423680[/TD]
[TD="align: right"]36:02.2[/TD]
[TD]*[/TD]
[TD]M[/TD]
[TD="align: right"]12[/TD]
[TD]D[/TD]
[TD]Pus for C/S[/TD]
[TD]Klebsiella oxytoca[/TD]
[TD]Cotrimoxazole[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Intended Output
[TABLE="width: 1408"]
<colgroup><col width="64" span="22" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID number[/TD]
[TD="width: 64"]Age[/TD]
[TD="width: 64"]Gender[/TD]
[TD="width: 64"]Isolate[/TD]
[TD="width: 64"]Amikacin[/TD]
[TD="width: 64"]Amoxicillin + Clavulanic acid[/TD]
[TD="width: 64"]Ampicillin[/TD]
[TD="width: 64"]Ceftriaxone[/TD]
[TD="width: 64"]Cefipime[/TD]
[TD="width: 64"]Ceftazidine[/TD]
[TD="width: 64"]Sulzone[/TD]
[TD="width: 64"]Tigecycline[/TD]
[TD="width: 64"]Ciprofloxacin[/TD]
[TD="width: 64"]Cotrimoxazole[/TD]
[TD="width: 64"]Doxycycline[/TD]
[TD="width: 64"]Fosfomycin[/TD]
[TD="width: 64"]Gentamicin[/TD]
[TD="width: 64"]Imipenem[/TD]
[TD="width: 64"]Meropenem[/TD]
[TD="width: 64"]Nitrofurantoin[/TD]
[TD="width: 64"]Piperacillin+Tazobactam[/TD]
[TD="width: 64"]Polymyxin B[/TD]
[/TR]
[TR]
[TD="align: right"]14423230[/TD]
[TD="align: right"]80[/TD]
[TD]M[/TD]
[TD]Klebsiella [/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
116
Fluff
Fluff
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff
H
  • Solved
Retrieve the intersection of multiple cells with respect to columns and rows
2
Replies
10
Views
165
mumps
mumps
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
297
Cquake
C
B
  • Solved
Copying a row of data from another sheet and pasting it into another sheet into the correct column headings
Replies
8
Views
590
kevin9999
K

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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