Divide Value of a Cell not to exceed 999 and Copy other Row info down

lslau

New Member
Joined
Apr 11, 2018
Messages
1
I have this sample data:
AcctNo NameReferDateDocNoPatientIDunitsnumCodeServiceClaim ID
361004TEST14/30/20139912099 2.1 COLLECT12/13/20161234568
361005TEST210/31/20149921636 1.6 COLLECT12/13/20161234569
361006TEST310/11/20139931135 1.1 COLLECT12/13/20161234570
361007TEST44/30/201382642548 2.6 COLLECT12/13/20161234571
361008TEST53/12/201581751478 1.5 COLLECT12/13/20161234572
361009TEST64/30/201386564869 4.9 COLLECT12/13/20161234573
361010TEST79/20/20139971058 1.1 COLLECT12/13/20161234574
361011TEST812/23/20169981265 1.3 COLLECT12/13/20161234575
361012TEST92/8/20139994586 4.6 COLLECT12/13/20161234576
361013TEST1011/22/2013664101658 1.7 COLLECT12/13/20161234577
361014TEST113/12/201599111045 1.0 COLLECT12/13/20161234578

<tbody>
</tbody>

Our interface will only allow a maximum of 999 units. Therefore, I need to create multiple lines of the same data that divide the units in each line as to not exceed 999.

Output Example:
AcctNo NameReferDateDocNoPatientIDunitsnumCodeServiceClaim ID
361004TEST14/30/2013991699.67 0.7 COLLECT12/13/20161234568
361004TEST14/30/2013991699.67 0.7 COLLECT12/13/20161234568
361004TEST14/30/2013991699.67 0.7 COLLECT12/13/20161234568
361005TEST210/31/2014992818 0.8 COLLECT12/13/20161234569
361005TEST210/31/2014992818 0.8 COLLECT12/13/20161234569
361006TEST310/11/2013993657.5 0.7 COLLECT12/13/20161234570
361006TEST310/11/2013993657.5 0.7 COLLECT12/13/20161234570
361007TEST44/30/20138264849.33 0.9 COLLECT12/13/20161234571
361007TEST44/30/20138264849.33 0.9 COLLECT12/13/20161234571
361007TEST44/30/20138264849.33 0.9 COLLECT12/13/20161234571
361008TEST53/12/20158175739 0.7 COLLECT12/13/20161234572
361008TEST53/12/20158175739 0.7 COLLECT12/13/20161234572
361009TEST64/30/20138656973.8 1.0 COLLECT12/13/20161234573
361009TEST64/30/20138656973.8 1.0 COLLECT12/13/20161234573
361009TEST64/30/20138656973.8 1.0 COLLECT12/13/20161234573
361009TEST64/30/20138656973.8 1.0 COLLECT12/13/20161234573
361009TEST64/30/20138656973.8 1.0 COLLECT12/13/20161234573
361010TEST79/20/2013997529 0.5 COLLECT12/13/20161234574
361011TEST89/21/2013997529 0.5 COLLECT12/14/20161234575
361011TEST812/23/2016998632.5 0.6 COLLECT12/13/20161234575
361011TEST812/23/2016998632.5 0.6 COLLECT12/13/20161234575
361012TEST92/8/2013999917.2 0.9 COLLECT12/13/20161234576
361012TEST92/8/2013999917.2 0.9 COLLECT12/13/20161234576
361012TEST92/8/2013999917.2 0.9 COLLECT12/13/20161234576
361012TEST92/8/2013999917.2 0.9 COLLECT12/13/20161234576
361012TEST92/8/2013999917.2 0.9 COLLECT12/13/20161234576
361013TEST1011/22/201366410829 0.8 COLLECT12/13/20161234577
361013TEST1011/22/201366410829 0.8 COLLECT12/13/20161234577
361014TEST113/12/20159911522.5 0.5 COLLECT12/13/20161234578
361014TEST113/12/20159911522.5 0.5 COLLECT12/13/20161234578

<tbody>
</tbody>
Thank you in advance for any help!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,108,620
Messages
5,523,944
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top