HTC Data Use Tool -User`s Manual - Global Health Sciences Data Use
Contents
1. and support any PMTCT ART PEP Youth friendly VCT PMTCT Coverage of PMTCT annual births pregnant women with 1 ANC visit pregnant women receiving PMTCT including testing receiving complete course of ART Awareness of HIV status Perceived risk of HIV infection Prevalence of HIV risk related behaviors of sexual partners condom use circumcision age at first sex Geographic size of regions sq km Geo coordinates for HTC health facility sites e g ART HTC site locations etc Road shapefiles 162. sort A to Z from the Home tab and choosing to Expand the selection as shown in Figure 7 Now the contents of Tool 1 are displayed in the exact same order as the contents of Tool 2 Figure 7 2 Select Sort A to Z mM a408 Calibri u gt Wrap Text General ey J E Atg b ty Copy a oy E E e ys ri aro Paste FE AA E i T GEE HMegeacente S 9 8 PS gt gt gt woes A p te st 1 Highlight column A meina a T a i cn PP ort Ztos y _ gt E B c D E F G H ja Custom Sort f K Data Age Percent ever q ai receive ree_Year Province District Sex Grou Population Population densit tested p in APR 2010 Central Chibombo Both lt 1 APR2010 Central Chibombo Both _ lt 5 APR 2010 Sort Warning p gt J Microsoft Office Excel found data next to your selection Since you have not Y selected this data it wil not be sorted APR2010 byt b b b b 3 Choose to Expand What do you want to do a Py emsemee nd the Lessenssisaceensinitans the selection Continue ani be vat lac _ _ qQ lt AA J To compare the contents of the two Tools first open a new workbook and copy Tool 1 and Tool 2 into separate tabs in this new workbook Rename each tab Tool 1 and Tool 2 respectively as shown in Figure 8 Figure 8 P B Coil E F w X y aonni a a a ee O mee Data Age women with positive circumcised as services for
3. HIV and T amp C 1 Source_Year Province District Sex Grou known HIV status pregnant partofthe received their test for HIV 2 APR2010 Central Chibombo Both lt 1 3 APR 2010 fantral Chihamhoa Rath A 4 APR2010 1 Open a new Excel 3068 5 APR2010 ook 1037 6 APR2010 y i 12665 7 APR2010 Central Chibombo Female All 556 649 8 APR 2010 Central Chibombo Male lt 1 0 9 APR2010 Central Chibombo Male lt 15 985 10 APR2010 Central Chibombo Male 1 14 11 APR 2010 Central Chibombo Male 15 30 5457 AJA PR 2010 p 2 Copy Tool 1 into Tab 1 Delete z z Sl aprzoro MN Right click select delicada Move or Copy and rename the tab Tool 1 14 APR 2010 A view Code f i 13224 dy Protect Sheet p o out Job Color gt 3 Create a new tab copy Tool 2 ae 16 APR 2010 seit into Tab 2 and rename thetab 25079 17 APR 2010 Unhide i N Tool 2 Next use formulas to identify any data entry discrepancies between Tool 1 and Tool 2 To do this first create a new tab and rename it Check In cell A1 of the Check tab enter the following formula Tool 1 A1 Tool 2 A1 Copy this formula across all cells that have contents in Tool 1 and Tool 2 For example if Columns A AG and rows 1 500 have contents then drag this formula down to row 500 and across to column AG as shown in Figure 9 All cells that match exactly in Tool 1 and Tool 2 will appear as TRUE in the Check tab any that do not match will
4. columns A E to compare the two tools In both Tool 1 and Tool 2 insert a new Column A and in cell A2 enter the following formula B28 _ C28 D28g E28 _ F2 which condenses the contents of these data disaggregation cells into one cell Copy this formula by dragging it from cell A2 down to the last row of the Tool that contains data as shown in Figure 6 The key will be used to sort the data so data in each tool is in the same order Figure 6 Coda A R Zambia HTC Tool 6Mar2014v2 Microsof Home Insert Page Layout Formulas Data Review View Acrobat B da Cut Calibri Ju jA al s v Z Wrap Text General x d 3 Copy Past kd bee dd v v v a C d ste AFfomatpainter B 7 UEO A E R E H Merge amp Center 9 68 8 Cond Clipboard Fa Font g Alignment g Number g A y la fe 828 _ 8 C028 _ 8 D28 _ 2 E28 _ 8 F2 l Insert a new Column A B G D E F Data Age y Source_Year Province District v Sex Grou Popu APF 2 In cell A2 enter th lt 1 9 Centrel o Bo APF B28 amp C2 amp amp D2 amp _ amp E2 h lt 5 4 APR 2010 Central Chibombo_Both_ All APKZUIU tentrar unIDombDo soth All 5 APR 2010 Central Chibombo_Female_ lt 15 APR 2010 Central Chibombo Female lt 15 6 APR 2010_Central Chib 3 Copy this formula by dragging it Chibombo Female 15 down Column 1 Next sort both Tool 1 and Tool 2 by the key column A by highlighting column A selecting
5. tool In this tab columns display data vertically represent indicator fields while rows display data horizontally represent single records The first row of this tab is a header that contains the column labels as shown in Figure 1 As described in this module data is input into the tool by completing the following steps Creating a Data Entry Plan Double data entry Checking and rectifying double data entry a ee A Creating a Data Key Figure 1 Example of 2 Inputs tab in HTC Data Use Tool dE HTC Too Tanzania 10Sept2013 xIs Microsoft Excel We NN Se H Insert Page Layout Formula Data Revie ie Add tr Acrobat Qsc ag amp Cut Arial 1 x ws wrap Text Number E HA Percent 2 Normal Y a PY A 4 F pe t ALO BF gt A amp I PH Bimegeacenter BW A cer dee s Bad Good Neutral _ Insert Delete Format 20 s ar ga L134 al te v 10 Wu Regional tested Population all Reported HIV ever ever tested and testedin received counselling receive _1 Data_Sourc Year Region District Y Gender Age Group ages Prevalence tested 7 received results last12mo and testing during ANC results 107 DHS 2010 Dodoma Rukwa Female 15 49 49 7 46 2 23 00 109 DHS 2010 Kilimanjaro Shinyanga Female 15 49 72 5 69 2 32 50 110 DHS 2010 Tanga Kagera Female 15 49 61 4 56 1 27 50 111 DHS 2010 Morogoro Mwanza Female 15 49 52 6 1 3 31 00 112 DHS 2010 Pwani Mara Female 15 49 68 7 62 8 32 80 113 DHS 2010 Dar Es Salaa Manyara Female 1
6. 5 49 70 8 67 7 36 00 114 DHS 2010 Lindi Dodoma Female 15 49 61 3 55 2 28 00 115 DHS 2010 Mtwara Kilimanjaro Female 15 49 57 1 52 5 21 40 116 DHS 2010 Ruvuma Tanga Female 15 49 70 7 67 4 42 30 117 DHS 2010 Iringa Morogoro Female 15 49 65 0 59 2 35 60 113 DHS 2010 Mbeya Pwani Female 15 49 52 7 49 1 25 10 119 DHS 2010 Singida Dar Es Salaam Female 15 49 54 1 50 4 32 00 120 DHS 2010 Tabora Lindi Female 15 49 59 5 56 4 32 70 121 DHS 2010 Rukwa Mtwara Female 15 49 34 8 32 6 16 60 122 DHS 2010 Kigoma Ruvuma Female 15 49 64 8 60 7 35 00 123 DHS 2010 Shinyanga Iringa Female 15 49 60 4 56 9 23 50 124 DHS 2010 Kagera Mbeya Female 15 49 58 5 54 9 30 00 125 DHS 2010 Mwanza Singida Female 15 49 50 6 48 1 28 50 126 DHS 2010 Mara Tabora Female 15 49 57 4 54 1 29 40 127 DHS 2010 Manyara Rukwa Female 15 49 55 2 49 8 27 10 128 DHS 2010 Dodoma Kigoma Male 15 49 39 2 38 1 24 80 130 DHS 2010 Kilimanjaro Kagera Male 15 49 47 7 46 2 29 00 131 DHS 2010 Tanga Mwanza Male 15 49 44 2 41 8 32 60 132 DHS 2010 Morogoro Mara Male 15 49 37 3 33 6 22 00 133 DHS 2010 Pwani Manyara Male 15 49 33 4 33 4 15 50 134 DHS 2010 Dar Es Salaa Arusha Male 15 49 46 7 46 7 01 135 DHS 2010 Lindi Dar Es Salaam Male 15 49 28 8 23 8 14 60 y o gt MEAN 2 Inputs ACTA A ae na j gt Beads QA nf 22 enererle frum IFA ith iana 2 The Data Entry Plan 2 1 Inventory and gather all available data The Data Entry Plan is an Excel spreadsheet that contains information on all available da
7. 61 3 55 2 28 00 115 DHS 2010 Mtwara Kilimanjaro Female 15 49 57 1 52 5 21 40 116 DHS 2010 Ruvuma Tanga Female 15 49 70 7 67 4 42 30 117 DHS 2010 Iringa Morogoro Female 15 49 65 0 59 2 35 60 118 DHS 2010 Mbeya Pwani Female 15 49 52 7 49 1 25 10 119 DHS 2010 Singida Dar Es Salaam Female 15 49 54 1 50 4 32 00 120 DHS 2010 Tabora Lindi Female 15 49 59 5 56 4 32 70 121 DHS 2010 Rukwa Mtwara Female 15 49 34 8 32 6 16 60 122 DHS 2010 Kigoma Ruvuma Female 15 49 64 8 60 7 35 00 123 DHS 2010 Shinyanga Iringa Female 15 49 60 4 56 9 23 50 124 DHS 2010 Kagera Mbeya Female 15 49 58 5 54 9 30 00 125 DHS 2010 Mwanza Singida Female 15 49 50 6 48 1 28 50 126 DHS 2010 Mara Tabora Female 15 49 57 4 54 1 29 40 127 DHS 2010 Manyara Rukwa Female 15 49 55 2 49 8 27 10 128 DHS 2010 Dodoma Kigoma Male 15 49 39 2 38 1 24 80 130 DHS 2010 Kilimanjaro Kagera Male 15 49 47 7 46 2 29 00 131 DHS 2010 Tanga Mwanza Male 15 49 44 2 41 8 32 60 132 DHS 2010 Morogoro Mara Male 15 49 37 3 33 6 22 00 133 DHS 2010 Pwani Manyara Male 15 49 33 4 33 4 15 50 134 DHS 2010 Dar Es Salaa Arusha Male 15 49 46 7 46 7 290 135 DHS 2010 Lindi Dar Es Salaam Male 15 49 28 8 23 8 14 60 r 4 gt ETN 2 Inputs LSP OWA T AETA N us Ready 84 of 335 records found BY UY 100 cm gt 3 34 PM La a m giy ad 5 21 2014 4 Checking and rectifying double data entry After both data entry representatives have entered all data into their respective Tools create a key from the contents of
8. HTC Data Use Tool User s Manual Module 1 Inputting data into the HTC Data Use Tool 3 6 2 638 889 Central 40 0 8 1 APEA i 3 000 E aia ae 2 079 545 tent 56 1 ae 625 000 Nairobi 70 0 8 j North Eastern 34 8 14 9 semis Nyanza 30 7 6 3 pal s ever been tested and or has been Rift valley 30 7 5 4 2 129 63 Western 248 gt ana tested HIV positive by region district N in relation to HIV prevalence 60 0 Ni 50 0 40 0 amp p 1 fi 5 5 30 0 a _ g 2 gt 20 0 10 0 0 0 a E ta a A Ak t 4 k E JIB gt 5 o gt A PS e Fa 3 ES sE Sy y a e a S xX Region District m Population Ever Tested Prevalence Table of Contents Table Or CONTEN irn a N A E O 2 How to Use This Module cccccssssssesesssscsescscscsscscssscscsssescasssscsessescscssscsesessescsssescseseeseicisscseseeetess 3 1 Introduction to inputting data into the HCT Data Use Tool 4 2 The Data Entry Plan ssssseessssssrrerssssssrrrrtrrrrersssssrrrrrrrreessssrreereereeees 5 2 1 Inventory and gather all available data c cccssscsssssssessesessessesecsesscsesesssaessessaesssasseeseaveaes 5 2 2 Developing a Data Entry Plan ccscsssssscsscsssssscssssscsssssscscsssssesssscscssessesessescasasecsenseseseaee 5 3 Double data entran dido 6 4 Checking and rectifying double data entry ooccocccccccccncnnccnncnninns 8 D Th
9. RUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE 3 Look back at the original data to determine if cae hae lea am lee teas teae Tool 1 or Tool 2 needs to be corrected and make TRUE TRUE MOVE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE any necessary changes TRUE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE run 2 grein Jt muwu 3 aye we j hey i LA COUNTIF AL AGS00 false ay UE O UA Y FLY a VE PRS PU JU PN VG FY TY y Y en y VR y Y AG TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE 4 When all discrepancies have been rectified all TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE True Cells will be true and the count O TRUE TRUE TRUE TRUE TRUE TRUE After all errors have been rectified it can be assumed that Tool 1 and Tool 2 contain accurate data that exactly reflect the source data From here you can save the contents of Tool 1 into the Master HTC Data Use Tool To do this highlight and copy the contents of Tool 1 excluding col
10. TC indicators and Data set 15 HMIS 2012 Attendance child health lt 1 year male District Male lt l year HTC indicators and Data set 16 HMIS 2012 Attendance child health lt 1 year male National Male lt l year HTC indicators and Data set 17 HMIS 2012 Attendance child health 1 4 years female National Female 1 4 years HTC indicators and Data set 18 HMIS 2012 Attendance child health 1 4 years female Province Female 1 4 years HTC indicators and Data set 19 HMIS 2012 Attendance child health 1 4 years female District Female 1 4 years HTC indicators and Data set 20 HMIS 2011 Antenatal 1st visit before 20 weeks National All lt 20weeks HTC indicators and Data set_2010_2011 21 HMIS 2011 Antenatal 1st visit before 20 weeks Province All lt 20weeks HTC indicators and Data set_2010_2012 22 HMIS 2011 Antenatal 1st visit before 20 weeks District All lt 20weeks HTC indicators and Data set_2010_2013 23 HMIS 2011 Antenatal 1st visit 20 weeks or later National All 20 weeks or later HTC indicators and Data set_2010_2015 M4 4 M data entry plan 2014 lt Sheet2 Sheet3 3 0 Maece Hrann 3 Double data entry Once all data has been accounted for in the Data Entry Plan two different individuals will transfer data from its original source into two separate spread sheets that resemble the format of the HTC Data Use Tool To start create one HTC Data Use Tool skeleton matching the spreadsheet shown in Figure 3 below Customize the geographic stratifications as appropr
11. TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE To identify and correct errors between the two tools first select a cell containing FALSE The formula bar shows which cell in each of the tools is discrepant As shown in Figure 12 below the contents of cell AA4 in Tool 1 and Tool 2 do not match From here look back at the original data to determine what the correct value for that cell is and update it in the Tool 1 and or Tool 2 tabs Once the contents of this cell match in Tool 1 and Tool 2 it will change to TRUE and the count will be reduced by one in the Check tab 10 Figure 12 ol Misa x TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE AD AE AE AG AH 1 J pa z i e A e e 1 Select a false cell TF tt 2 Formula shows there is a TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TF di b Tool 1 TRUE TRUE TRUE UE TRUE TRUE TRUE TRUE TRUE 3 7 discrepancy between loo TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE T and Tool 2 in cell AA4 TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TF TRUE TRUE TRUE TRUE TRUE TRUE T
12. appear as FALSE Figure 9 lemba HTC Tool Screenshots version 1 Autozaved a Nocrosoht Exe Ac x eS a pa bine r j u Ha Y Hi o P q Calibri u AK wee wrote General nH Bh oe ot i am Y A Paste F iis Conditional Formst Ceti insert Delete Format Sort amp Find amp n J Format Painter Big tti a A ENT Pj Merge amp Center 0143 Formatting as Table Styles La gt Filter Select Cipboard la Fort 5 Algresent a Number Styles Com Eating fe Tool 1 tA4 Tool 2 1A4 TRUE TRUE TRUE TRUE TRUE 2 In cell A1 enter have contents in Tool 1 and Tool 2 For Tool 1 A1 Tool 2 A1 E example if Columns A AG and Rows 1 lg 500 have contents then drag the ae formula down to row 500 and across to 1 me column AG al TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE Create a new tab rename the tab Check TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE To help identify which cells do not match between the two tool skeletons highlight all FALSE cells in red To do this first select all cells in the spreadsheet then select under the Home tab Conditional Formatting gt Highlight Cell Rules gt Equal To In the Equal To dialog box type false to specify that you want to Format cells that are equal to false with Light Red fill and Dark Red Text as shown in Figure 10 Figur
13. e 10 ds 2 Under the Home tab select ae ma Page yn Conditional Formatting gt Cell Rules gt Equal To Calibri RUE TRUE TRUE TRUE TRUE TRIE TRI Tae TRIE TRUE e UE TRUE TRUE TRUE TRUE TRUE Ej Data Bars 4 Between TRUE ITRUF TRUI P i 1 Select all cells in the UE TRUE TRUE TRUE TRUE TRUE TRUE gt spreadsheet UE TRUE TRUE TRUE TRUE TRUE 3 Color Scales sp ewte TRUE ui TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE 1 icon sets FEl rent nat Contains TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE Y New Rule l A Date Occurring TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE E Clear Rules TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE Tj Manage Bules TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE E ds TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE More Rules TRUE YA TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE mur mur mue TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE E TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE WE 3 Type false to indicate you want M to highlight in red all cells that say false and are therefore not equal in Tool 1 and Tool 2 To identify how many cells do not match between Tool 1 and Tool 2 set a count by selecting any empty cell in the Check spreadsheet and entering the
14. e Data eV osease 12 Appendix 1 HCT Suggested Indicator List cccccceeceeceeceeeeeeeeeeeseeeeeees 15 How to Use This Module This module offers a systematic approach to inputting data into the HIV testing and counseling HTC Data Use Tool and is meant to accompany the HTC Data Use Tool User s Manual Although this guide uses only HTC examples the Tool can be applied and adapted to different data use questions related to HIV prevention treatment and care as well as other programmatic areas The manual is designed to provide users with a solid background and understanding of the data synthesis process It is most useful as a preparation tool for those who will take part in an HTC workshop or those who have completed a workshop and may need to refer back to the concepts and processes at a later time All materials related to this manual and the HTC Data Use and Strategic Planning Tool can be found here http globalhealthsciences ucsf edu prevention public health group global 1 Introduction to inputting data into the HTC Data Use Tool The HIV Testing and Counseling HTC tool is designed to answer key strategic questions related to HTC services through enhanced visualization of data Utilizing Excel and GIS software the tool can organize data display tables and graphs and produce maps that aid strategic planning and policy decisions Data used with the HTC Data Use Tool are input into the 2 nputs tab of this Excel based
15. ear lt 1 year lt 1 year lt i year lt lyear lt 1 year _ 1 4 years gt 1 4 years 1 4 years All All lt 20weeks e Mansroole Paste this list of indicators into Column A of the respective tab in the Data Key workbook e g indicators filtered by female are pasted in the female tab Delete all duplicate indicators rows as shown in Figure 16 Figure 16 fe Antenatal ist visit by woman lt 18 years 1 indicator 2 Antenatal ist visit before 20 weeks Antenatal 1st visit before 20 weeks Antenatal 1st visit before 20 weeks 5 Antenatal 1st visit 20 weeks or later Antenatal 1st visit 20 weeks or later E Ju AN s 3 2 Delete all duplicate B J 0 o A 323 indicators rows within each tab E Y A banana ot a amp Cut ig Co e 1 44 py k 1 A Paste 3 1 Paste Special 1 Insert 1 1 Clear Contents ale l SF Format Cells ale ale 1 al Row Height 2 Hide 1 Paste the indicators into Column A into their respective tabs Male Female Both of the 2 Unhide Data Key Lastly use the filter function in the Data Entry Plan to fill in the remaining contents of each tab For example filter by various sexes ages and geographic disaggregations to determine which indicators are represented and by which source This is an iterative process and will take some time to develop an effective customized strateg
16. ested HIV counseled tested and received results and couples counseled and received results and HIV positive pregnant women who received antiretrovirals for PMTCT ART treatment coverage estimated of persons eligible for treatment estimated of persons receiving treatment Population projections HIV positive Shapefile for administrative levels of interest Geography National Province District Sex male female both Age group lt 15 and 15 ata minimum Geography National Province District Sub district Facility Sex male female both Age group lt 15 and 15 ata minimum Geography National Province District Sub district Facility Sex male female both Age group lt 15 and 15 ata minimum HTC service type e g PMTCT TB mobile outreach etc Geography National Province District Sub district Sex male female both Age group lt 15 and 15 ata minimum 15 Recommended indicators Surveillance data Program Data Behavioral data Geographic data HIV incidence Sexually transmitted disease prevelance HIV prevalence among persons with STI symptoms in the past 12 months TB HIV co infection prevalence TB prevalence Linkages to care and treatment newly diagnosed as HIV newly enrolled in care VCT essential services coverage PMTCT essential services coverage Availability of HIV AIDS services by facility HIV testing Any care
17. exactly the same Data entry tips e For National level indicators enter National into the Region Province column e For Province level indicators leave the District column blank e Convert all PDF tables to Excel soreadsheets so tables can be copied and pasted instead of typed from the source to the skeleton After all data has been entered both Tool 1 and Tool 2 should resemble Figure 5 below Figure 5 Kk ee TC Too Tanzania 10Sept2013 x1s Microsoft Excel ue E ll I e insert Page Layout Formulas Data Rewew iew Add ins Acrobat o c s a cut Arial J2 as amp BF wrap Tet Number E SY Percent 2 Normal za a 2 ee Y A da Copy Fill a A E E Ea Ro Bmergeacener gt st omnim ata Bad Good Neutral insert Delete Format SONA Find amp L134 X te v A 8 c D E F 6 H i J K n M Regional tested Population all Reported HIV ever ever tested and testedin received counselling receive 1 Data_Sourc Year Region District x Gender Age Group ages Prevalence tested 7 received results last12mo and testing during ANC results 107 DHS 2010 Dodoma Rukwa Female 15 49 49 7 46 2 23 00 109 DHS 2010 Kilimanjaro Shinyanga Female 15 49 72 5 69 2 32 50 110 DHS 2010 Tanga Kagera Female 15 49 61 4 56 1 27 50 111 DHS 2010 Morogoro Mwanza Female 15 49 52 6 51 3 31 00 112 DHS 2010 Pwani Mara Female 15 49 68 7 62 8 32 80 113 DHS 2010 Dar Es Salaa Manyara Female 15 49 70 8 67 7 36 00 114 DHS 2010 Lindi Dodoma Female 15 49
18. formula countif A1 AG500 false where AG500 is the cell furthest down and furthest to the right with contents in Tool 1 and Tool 2 as shown in Figure 11 The number that appears in this cell indicates the number of cells that do not match between the two tools As errors are corrected this number will reduce to 0 indicating no data discrepancies exist between Tool 1 and Tool 2 Figure 11 iy us 3 L a Pe h E Autosum Aw aA Ju Ad Srp Tert General E H A Br tte 1 8 i en oa Dl A E EEEE eng Center o 8 3 one Loros a Insert Delete Format This indicates that there al G Aligameni fs Number Gs Styles Celis are three cells that do SOUNTIALAGIOS Talta not match in Tool 1 and S T u v w x y Z AA AB AC AD JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE Tool 2 JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUEST RUE mm JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE JE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE
19. iate Figure 3 Population density Column A Source and year Column F Indicators Column B Province Column C District if applicable Column D Gender Column E Age Group mn o Next make a copy of this skeleton and paste into a new workbook by right clicking on the Sheet 1 tab and following the steps shown in Figure 4 below Save one spreadsheet as Tool 1 for one person to complete and the other spreadsheet as Tool 2 for the other person Figure 4 ARA a c D E F 1 Source_Year Province District Sex Age Group Population 2 ali 12 13 Move or Copy 14 Move selected sheets 15 To book 16 Insert new rk afa an US ent ers 17 EX Delete 19 20 Q View Code g Protect Sheet 21 Wa P Tab Color 22 Y Create a copy 23 Hide 24 25 Select All Sheets M 4 gt gt Sheetl t3 Each data entry representative should enter data into their respective tools Disaggregations e g Columns A E sex age group etc and indicator text should be entered exactly as they appear in the Data Entry Plan Columns A E should have text in every row of the tool with data whereas columns F will only have data for that indicator column as it is available for that disaggregation row The data in Tool 1 will be compared to the data in Tool 2 in a later step so it is important that all text e g numbers spelling etc in both tools are
20. icator y Geographic disagreagations 7 disaggregations y diaggregations published sourc y Ly iy 2 HMIS 2012 Antenatal 1st visit before 20 weeks National All lt 20weeks HTC indicators and Data set 3 HMIS2012 Antenatal 1st visit before 20 weeks Province All lt 20weeks HTC indicators and Data set 4 HMIS 2012 Antenatal 1st visit before 20 weeks District All lt 20weeks HTC indicators and Data set 5 HMIS 2012 Antenatal 1st visit 20 weeks or later National All 20 weeks or later HTC indicators and Data set 6 HMIS 2012 Antenatal 1st visit 20 weeks or later Province All 20 weeks orlater HTC indicators and Data set 7 HMIS 2012 Antenatal 1st visit 20 weeks or later District All 20 weeks or later HTC indicators and Data set 8 HMIS2012 Antenatal 1st visit by woman lt 18 years National Female lt 18 years HTC indicators and Data set 9 HMIS2012 Antenatal 1st visit by woman lt 18 years Province Female lt 18 years HTC indicators and Data set 10 HMIS 2012 Antenatal 1st visit by woman lt 18 years District Female lt 18 years HTC indicators and Data set a HMIS 2012 Attendance child health lt 1 year female National Female lt l year HTC indicators and Data set 12 HMIS 2012 Attendance child health lt 1 year female Province Female lt l year HTC indicators and Data set 13 HMIS 2012 Attendance child health lt 1 year female District Female lt l year HTC indicators and Data set 14 HMIS 2012 Attendance child health lt 1 year male Province Male lt l year H
21. onal information about the FF APR2010 Copperbelt Chingo SOUrces and or indicators you want 11 9 The Data Key The Data Key is an Excel based spreadsheet guide that shows which indicators are included in the HTC Data Tool including their respective disaggregations and sources This dynamic guide will be used as a reference when creating graphs and maps from this data As such it should be revised when updating the HTC Data Use Tool The Data Key is separated by three tabs each titled by the sex disaggregations in the tool Within each tab Column A lists the indicators that contain data for that sex row 1 describes age disaggregations while row 2 describes geographic disaggregations The contents of this table indicate the source from which these data come To start open a new Excel workbook and create three tabs renamed male female and both Sexes as shown in Figure 14 Figure 14 a aon iba 1 An Tm gt IP Wrap Text General F ES A TX i Pa C Inse e Format h EM OZ i E f gt N 0 Pp a R u 1 Open a new workbook 18 2 Create three tabs titled Male Female and both sexes Returning to the Data Entry Plan filter the data by each sex and copy all indicators with data in the Tool by that sex as shown in Figure 15 12 Figure 15 s y Geographic disagreagations Gender y disaggregations A sortAtoz Z So
22. rtZtoA Sort by Color Filter by Color Text Filters Distri 1 Filter Data Entry plan by Natic each gender dissagregation _1 Source Indicator 2 HMIS2012 Antenatal 1st visit before 20 weeks National 3 HMIS2012 Antenatal 1st visit before 20 weeks Province 4 HMIS2012 Antenatal 1st visit before 20 weeks District 5 His 2012 Antenatal ist visit 20 weeks or later Iational 6 HMIS2012 Antenatal 1st visit 20 weeks or later Province 7 HMIS2012 Antenatal 1st visit 20 weeks or later District 8 HMIS2012 Antenatal ist visit by woman lt 18 years National 9 HMIS2012 Antenatal 1st visit by woman lt 18 years Province 10 HMIS2012 Antenatal ist visit by woman lt 18 years District 11 HMIS 2012 Attendance child health lt 1 year female National 12 HMIS2012 Attendance child health lt 1 year female Province 13 HMIS2012 Attendance child health lt 1 year female District 14 HMIS 2012 Attendance child health lt 1 year male Provi 15 HMIS2012 Attendance child health lt 1 year male 16 HMIS 2012 Attendance child health lt 1 year male 17 HMIS 2012 Attendance child health 1 4 years female Natiorver AS 202 A co py all indicators for o 19 HMIS 2012 Att District 20 mis 2011 ant that gender National 4 27 HUMIC 3N1 1 Antanatal tet vicit hafare Miusaalre Drewuinra rt han ls Cancel Age Y diaggregations lt 20weeks lt 20weeks lt 20weeks 20 weeks or lat x 20 weeks or lat 20 weeks or lat I 18 years lt 18 years lt 18 years lt 1 y
23. ta and will guide the data entry process The first step in creating a Data Entry Plan is to inventory and gather all available data Data to be input into the tool should be of various sources e g Population based surveys Surveillance data Program Data Census data etc time points e g most recent year past years to show trends and at a minimum the following stratifications Geography National Province District Sex male female both Age group lt 15 and 15 ata minimum Data should be entered for the most disaggregated level e g if data at the district level is available enter district data See Appendix 1 for the HTC Suggested Indicator list 2 2 Developing a Data Entry Plan This Data Entry Plan should contain the following Columns Column A Source and year Column B Indicator Column C Geographic disaggregations National Province or District Column D Gender dissagregations male female or both Column E Age disaggregations Column F Source location if published source list the page and table number for future reference As a group decide which indicators to include in the tool from each source and complete the Data Entry Plan accordingly Each row should have text in all columns so that each disaggregation appears as its own row in the spreadsheet even if duplicated as shown in Figure 2 below Figure 2 ae A B G D E F G H Gender Age Source location if 1 Source Ind
24. umn A the key In the Master HTC Data Tool select the 2 inputs tab and paste these data as shown in Figure 13 Figure 13 _ Defi Data Source Year 7 o 7 A cu di y a APR 2010_Central_Chibombo_Both_ lt 1 Central ee APR 2010 Central_Chibombo_Both_ lt 5 APR2010 Central Chibombo Both lt 5 Ls APR 2010_Central_Chibombo_Both_All APR 2010 Central Chibombo Both All or cops APR 2010_Central_Chibombo_Female_ lt 15 APR 2010 Central Chibombo Female lt 15 z APR 2010_Central_Chibombo_Female_15 APR2010 Central Chibombo Female 15 w e APR 2010 Cantral Chihamba Camala all LADA IMIN Contral Chihamha Female All e a lt 1 DD hen Comment 1 Highlight the contents of the Sn F tomateen pe E 1 14 Pick From Drop Gown List final verified data excluding the TN lt 1 key in column A lt 5 All Female 27 APR 2010 Central Kapiri Me hi Camala Alt 919 Female 47 APR 2010 Central Mumbn Central 67 APR 2010 Copperbelt Chililat 87 APR 2010 Copperbelt Kalulus to take note of 97 APR2010 Copperbelt Kitwe 3 Paste the final verified data 107 APR2010 Copperbelt Luanshya 398 Female All y 117 APR 2010 Copperbelt Lufwanyama Female All 67 into the 2 Inputs tab of the tool 127 APR 2010 and resave the tool Female All Copperbelt Masaiti 61 427 ADR 201M narh Meer female 37 APR 2010 Central Mkushi 4 Add in the Notes tab any 57 APR2010 Central Serenje additi
25. y Figure 17 is an example of a completed Data Key 13 Figure 17 i a I SOs rie Home Insert Pagelayou Formulas Data Review View Add ins Acrobat oQ 0283 EW i Zl pees EA A Cut Calibri 1 Ka gt is Wrap Text General y Y o Good Eu e ii s utoSum Sy A a A ma qu ra paste B d A gt dl 4 22 Condivonal Formatas NOMAS Check Cell Insert Delete Format sota rinda gt Y Format Painter B22 _ 2 a penna a cana ia a 3 Formatting Table d gt lt 2 Clear Filter Select gt Clipboard Ta Font B Alignment Fa Number Ta Styles Colls Editing G14 W Female 3 at py y wep am an Pp leelo QT SOTE we T 4 gt gt Female Male Both Male amp Female 14 Appendix 1 HTC Suggested Indicator List Indicators to be collected prior to use of the tool include Population based surveys DHS AIS etc Surveillance data ANC PMTCT BSS etc Program Data APR HTC etc Census data Geographic data HIV prevalence and ever HIV tested and HIV tested in the past 12 months and HIV tested in the past 12 months who received their results and received HIV testing and counseling in the past 12 months and HIV tested counseling and received results in the past 12 months HIV prevalence HIV positive and attending ANC with unknown HIV status at entry HIV counseled and t
Download Pdf Manuals
Related Search