I need to take columns B - E and put in a comma delimited string. All lines that have the same first four characters for value in column B need to be in same comma delaminated string. Also need to add SUBCLIN followed by a space before the data in Columns B - E. The spreadsheet will vary in length and number of needed strings created. Also if the value in both Columns D & E are zero this line needs to be skipped.
Below is example:
| W9113M-17-D-0008/W9113M-18-FD007 | | | | | | | | | | | BVN # 0036 | 02/06/2021 - 03/05/2021 | TOTAL | TOTAL | TOTAL | | | | | | | 0036A -Burden rates adjustment for FY 2020 from target to actual rates | BVN # 36B | BVN # 36a | VOUCHER | | | | | | | | | | | | | | | | | | | | | | | | | | | | 0010 | 001001 | AB | 5,257.41 | -9,138.63 | -3,881.22 | SUBCLIN 001001,AB,5257.41,-9138.63 | | | | | | | | | | | | | | | CLIN 0010 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 0001 | 000101 | AA | 90.69 | -139.51 | -48.82 | SUBCLIN 000101,AA,90.69,-139.51,SUBCLIN 000102,AC,111.52,-22806.29,SUBCLIN 000103,AD,466.76,-561.15,SUBCLIN 000104,AE,-272443.19,-6704.06,SUBCLIN 000106,AG,13114.93,-5304.15,SUBCLIN 000107,AH,382.91,-1178.25,SUBCLIN 000109,AK,0,-651.64,SUBCLIN 000110,AL,0,-1805.04,SUBCLIN 000111,AM,36781.66,-36721.4,SUBCLIN 000112,AN,0,-704.1,SUBCLIN 000113,AP,0,-3052.24,SUBCLIN 000114,AQ,124571.88,-5529.28,SUBCLIN 000115,AU,0,-3.32,SUBCLIN 000116,AV,0,-745.63,SUBCLIN 000118,AW,3170.03,-3700.29,SUBCLIN 000124,AW,-174.48,-2935.27,SUBCLIN 000127,AW,-102.86,-627.84,SUBCLIN 000119,AX,424920.81,-454.03,SUBCLIN 000120,AY,0,-113.57,SUBCLIN 000121,AZ,107101.18,-11271.47,SUBCLIN 000122,BA,70874.26,-8527.94,SUBCLIN 000125,BE,89082.88,-2313.03,SUBCLIN 000134,BE,69370.96,0 | | | | | 000102 | AC | 111.52 | -22,806.29 | -22,694.77 | | | | | | 000103 | AD | 466.76 | -561.15 | -94.39 | | | | | | 000104 | AE | -272,443.19 | -6,704.06 | -279,147.25 | | | | | | 000117 | AE | 0.00 | 0.00 | 0.00 | | | | | | 000105 | AF | 0.00 | 0.00 | 0.00 | | | | | | 000106 | AG | 13,114.93 | -5,304.15 | 7,810.78 | | | | | | 000107 | AH | 382.91 | -1,178.25 | -795.34 | | | | | | 000108 | AJ | 0.00 | 0.00 | 0.00 | | | | | | 000109 | AK | 0.00 | -651.64 | -651.64 | | | | | | 000110 | AL | 0.00 | -1,805.04 | -1,805.04 | | | | | | 000111 | AM | 36,781.66 | -36,721.40 | 60.26 | | | | | | 000112 | AN | 0.00 | -704.10 | -704.10 | | | | | | 000113 | AP | 0.00 | -3,052.24 | -3,052.24 | | | | | | 000114 | AQ | 124,571.88 | -5,529.28 | 119,042.60 | | | | | | 000115 | AU | 0.00 | -3.32 | -3.32 | | | | | | 000116 | AV | 0.00 | -745.63 | -745.63 | | | | | | 000118 | AW | 3,170.03 | -3,700.29 | -530.26 | | | | | | 000124 | AW | -174.48 | -2,935.27 | -3,109.75 | | | | | | 000127 | AW | -102.86 | -627.84 | -730.70 | | | | | | 000119 | AX | 424,920.81 | -454.03 | 424,466.78 | | | | | | 000120 | AY | 0.00 | -113.57 | -113.57 | | | | | | 000121 | AZ | 107,101.18 | -11,271.47 | 95,829.71 | | | | | | 000122 | BA | 70,874.26 | -8,527.94 | 62,346.32 | | | | | | 000125 | BE | 89,082.88 | -2,313.03 | 86,769.85 | | | | | | 000134 | BE | 69,370.96 | 0.00 | 69,370.96 | | | | | | | | | | | | | | | | CLIN 0001 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 0008 | 000801 | AA | 0.00 | -75.52 | -75.52 | SUBCLIN 000801,AA,0,-75.52,SUBCLIN 000802,AC,0,-19.98,SUBCLIN 000803,AD,0,-4.84,SUBCLIN 000804,AE,0,-22.73,SUBCLIN 000805,AG,0,-303.64,SUBCLIN 000807,AK,0,11.71,SUBCLIN 000808,AL,0,-14.67,SUBCLIN 000809,AM,0,-151,SUBCLIN 000819,AM,0,-304.16,SUBCLIN 000821,AM,0,-387.67,SUBCLIN 000812,AQ,0,-13.22,SUBCLIN 000817,AW,114.83,-73.95,SUBCLIN 000818,AW,727.76,0,SUBCLIN 000814,AY,0,-16.53,SUBCLIN 000816,BA,544.65,0,SUBCLIN 000820,BF,842.5,-22.12,SUBCLIN 000825,BG,0,-327.78 | | | | | 000802 | AC | 0.00 | -19.98 | -19.98 | | | | | | 000803 | AD | 0.00 | -4.84 | -4.84 | | | | | | 000804 | AE | 0.00 | -22.73 | -22.73 | | | | | | 000805 | AG | 0.00 | -303.64 | -303.64 | | | | | | 000806 | AJ | 0.00 | 0.00 | 0.00 | | | | | | 000807 | AK | 0.00 | 11.71 | 11.71 | | | | | | 000808 | AL | 0.00 | -14.67 | -14.67 | | | | | | 000809 | AM | 0.00 | -151.00 | -151.00 | | | | | | 000819 | AM | 0.00 | -304.16 | -304.16 | | | | | | 000821 | AM | 0.00 | -387.67 | -387.67 | | | | | | 000810 | AN | 0.00 | 0.00 | 0.00 | | | | | | 000811 | AP | 0.00 | 0.00 | 0.00 | | | | | | 000812 | AQ | 0.00 | -13.22 | -13.22 | | | | | | 000823 | AR | 0.00 | 0.00 | 0.00 | | | | | | 000817 | AW | 114.83 | -73.95 | 40.88 | | | | | | 000818 | AW | 727.76 | 0.00 | 727.76 | | | | | | 000814 | AY | 0.00 | -16.53 | -16.53 | | | | | | 000816 | BA | 544.65 | 0.00 | 544.65 | | | | | | 000820 | BF | 842.50 | -22.12 | 820.38 | | | | | | 000825 | BG | 0.00 | -327.78 | -327.78 | | | | | | | | | | | | | | | | CLIN 0008 | | | | | | | | | |
| | | | | | |
