Data in column changed to delimited string

pjn560

New Member
Joined
Jul 29, 2015
Messages
16
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 # 003602/06/2021 - 03/05/2021TOTALTOTALTOTAL
0036A -Burden rates adjustment for FY 2020 from target to actual ratesBVN # 36BBVN # 36aVOUCHER
0010001001AB
5,257.41​
-9,138.63​
-3,881.22​
SUBCLIN 001001,AB,5257.41,-9138.63
CLIN 0010
0001000101AA
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
000102AC
111.52​
-22,806.29​
-22,694.77​
000103AD
466.76​
-561.15​
-94.39​
000104AE
-272,443.19​
-6,704.06​
-279,147.25​
000117AE
0.00​
0.00​
0.00​
000105AF
0.00​
0.00​
0.00​
000106AG
13,114.93​
-5,304.15​
7,810.78​
000107AH
382.91​
-1,178.25​
-795.34​
000108AJ
0.00​
0.00​
0.00​
000109AK
0.00​
-651.64​
-651.64​
000110AL
0.00​
-1,805.04​
-1,805.04​
000111AM
36,781.66​
-36,721.40​
60.26​
000112AN
0.00​
-704.10​
-704.10​
000113AP
0.00​
-3,052.24​
-3,052.24​
000114AQ
124,571.88​
-5,529.28​
119,042.60​
000115AU
0.00​
-3.32​
-3.32​
000116AV
0.00​
-745.63​
-745.63​
000118AW
3,170.03​
-3,700.29​
-530.26​
000124AW
-174.48​
-2,935.27​
-3,109.75​
000127AW
-102.86​
-627.84​
-730.70​
000119AX
424,920.81​
-454.03​
424,466.78​
000120AY
0.00​
-113.57​
-113.57​
000121AZ
107,101.18​
-11,271.47​
95,829.71​
000122BA
70,874.26​
-8,527.94​
62,346.32​
000125BE
89,082.88​
-2,313.03​
86,769.85​
000134BE
69,370.96​
0.00​
69,370.96​
CLIN 0001
0008000801AA
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
000802AC
0.00​
-19.98​
-19.98​
000803AD
0.00​
-4.84​
-4.84​
000804AE
0.00​
-22.73​
-22.73​
000805AG
0.00​
-303.64​
-303.64​
000806AJ
0.00​
0.00​
0.00​
000807AK
0.00​
11.71​
11.71​
000808AL
0.00​
-14.67​
-14.67​
000809AM
0.00​
-151.00​
-151.00​
000819AM
0.00​
-304.16​
-304.16​
000821AM
0.00​
-387.67​
-387.67​
000810AN
0.00​
0.00​
0.00​
000811AP
0.00​
0.00​
0.00​
000812AQ
0.00​
-13.22​
-13.22​
000823AR
0.00​
0.00​
0.00​
000817AW
114.83​
-73.95​
40.88​
000818AW
727.76​
0.00​
727.76​
000814AY
0.00​
-16.53​
-16.53​
000816BA
544.65​
0.00​
544.65​
000820BF
842.50​
-22.12​
820.38​
000825BG
0.00​
-327.78​
-327.78​
CLIN 0008
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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