Assign Rows

F

fpskidmark

Board Regular
Joined
Sep 11, 2009
Messages
139
Hello Excel Gurus,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I need your help again; I’m trying to distribute the rows evenly if a condition is met.<o:p></o:p>
If an “Acct Class” = “Inpatient”, then place that entire row to a designated row.<o:p></o:p>
<o:p> </o:p>
Let say, I have a list of 26 accts and 7 accts are “Inpatient” then place the Inpatient” acct on every third row Int(26/7) = 3.<o:p></o:p>
<o:p></o:p>
<o:p>Any suggestion will be helpful.</o:p>
<o:p></o:p>
<o:p>*Sheet layout*</o:p>
<o:p><TABLE style="WIDTH: 676pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=901 border=0 x:str><COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5924" width=162><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 120pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=160 height=17>Patient Name</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=84>Hosp Acct#</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=85>Admit Date</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=71>Disch Date</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=124>Acct Class</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 122pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=162>Financial Class</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 114pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=152>Provider Name</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=63>MRN</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BARELA,SONIA</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018289360">22018289360</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40861">11/14/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40862">11/15/2011</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Emergency</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Self-Pay [4]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CHAO, WILLIAM (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>11513835</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AYALA,ARACELI M</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018289669">22018289669</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40861">11/14/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40862">11/15/2011</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Emergency</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Medicaid [3]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">GUIRGUIS, MICHAEL SABRY (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>9189529</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>TAM,IRENE</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22016980725">22016980725</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40857">11/10/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40860">11/13/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Kaiser Home Region [113]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">BAHADOSINGH, FELICIA BETH (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>19279524</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>TORRES,GLORIA</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018159764">22018159764</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40854">11/7/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40859">11/12/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Self-Funded Medical [114]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">MAHLER, DAVID SCOTT (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3739665</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ELLITHY,HEBA A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22017812692">22017812692</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40858">11/11/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40860">11/13/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Self-Funded Medical [114]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FELDMAN, REVITAL KLARA (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>16073931</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ROMERO,JUANA P</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018242971">22018242971</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40855">11/8/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40856">11/9/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Medicaid [3]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">RAMOS, VERA B (D.O.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>18655621</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PEREZ,JOSE A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018253767">22018253767</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40856">11/9/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40858">11/11/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Medicare [2]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">BRODER, BENJAMIN ISAAC (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>17319929</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SOK,KHIM</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22017079133">22017079133</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40858">11/11/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40860">11/13/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Kaiser Home Region [113]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">BATTISTA, LEAH RUTH (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>15904618</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>KUTTAPPY,SUJA</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22016988409">22016988409</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40858">11/11/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40860">11/13/2011</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Inpatient</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Self-Funded Medical [114]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">DAOUD, NELDA ALPHONSE (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>19270221</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>WEI,HENRY K</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018285773">22018285773</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40861">11/14/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40862">11/15/2011</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Emergency</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Self-Funded Medical [114]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CHUNG, JOHN Y (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>19948336</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>GOMEZ,OSCAR U</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018285958">22018285958</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40861">11/14/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40862">11/15/2011</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Emergency</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Self-Pay [4]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">RAMOS, VERA B (D.O.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>20188504</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>JESSIE,VANESSA R</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="22018289949">22018289949</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40861">11/14/2011</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40862">11/15/2011</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Emergency</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Tricare [6]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">WHITE, SOMMER ELGIN (M.D.)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>19500863</TD></TR></TBODY></TABLE></o:p>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 coordsize="21600,21600" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f" filled="f" o:preferrelative="t"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f"></v:path><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype><o:p></o:p>

Note: Range(“I1”) = Total of Accts<o:p></o:p>
Range(“J1”) = Total of “Inpatient” Accts<o:p></o:p>
Range(“K1”) = Int(I1/J1)
<o:p></o:p>
PHP: 
Sub Distribute_Inpatient_Accts()
' ************************************
' Distribute "Inpatient" Accts Evenly
' ************************************
    Windows("2118.CSV").Activate
    Range("I1").FormulaR1C1 = "=COUNT(C[-1])+1"
    Range("J1").FormulaR1C1 = "=COUNTIF(C[-5],""Inpatient"")"
    Range("K1").FormulaR1C1 = "=INT(RC[-2]/RC[-1])"
Sheets("2118").Select
Range("I1:K1").Copy
Range("I1:K1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
lastrow = Range("B" & Rows.Count).End(xlUp).Row
For n = 1 To Range("J1").Value
For E = lastrow To 2 Step -1
On Error Resume Next
If Cells(E, "E") = "Inpatient" Then Cells(E, "E").Font.ColorIndex = 5
If Cells(E, "E") = "Inpatient" Then Rows(E).Cut: Rows(Range("K1").Value + 1 * n).Insert Shift:=xlDown
Next E
Next n
Range("A1").Select
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Here is an alternate method:

Code: 
Option Explicit
 
Sub DistributeInpatientAccountsEvenly()
    Dim lECount As Long
    Dim lICount As Long
    Dim lIInitialCount As Long
    Dim lLastDataRow As Long
    Dim lX As Long
    Dim lInterval As Long
    Dim aryI() As Variant
    Dim aryE() As Variant
    Dim lSortColumn As Long
 
    '// The # of the column that contains the class of patient (Inpatient or Other)
    Const lClassColumn As Long = 5
 
    'The blank column at the end of the data field will be used as a helper column
    'it will hold the sort data
    lSortColumn = Cells(1, Columns.Count).End(xlToLeft).Column
 
    lLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
    For lX = 2 To lLastDataRow
        If Cells(lX, lClassColumn).Value = "Inpatient" Then
            lICount = lICount + 1
        Else
            lECount = lECount + 1
        End If
    Next
    lInterval = ((lICount + lECount) \ lICount)
    lIInitialCount = lICount
 
    lICount = 1
    lECount = 1
    For lX = 1 To lLastDataRow - 1
        If lX Mod lInterval = 0 Then
            If lICount <= lIInitialCount Then
                ReDim Preserve aryI(1 To lICount)
                aryI(lICount) = lX
                lICount = lICount + 1
            Else
                ReDim Preserve aryE(1 To lECount)
                aryE(lECount) = lX
                lECount = lECount + 1
            End If
        Else
            ReDim Preserve aryE(1 To lECount)
            aryE(lECount) = lX
            lECount = lECount + 1
        End If
    Next
 
    lICount = 1
    lECount = 1
    For lX = 1 To lLastDataRow - 1
        If Cells(lX + 1, lClassColumn).Value = "Inpatient" Then
            Cells(lX + 1, lSortColumn).Value = aryI(lICount)
            lICount = lICount + 1
        Else
            Cells(lX + 1, lSortColumn).Value = aryE(lECount)
            lECount = lECount + 1
        End If
    Next
 
    Cells(1, lSortColumn).Value = "Sort"
 
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add _
        Key:=Range(Cells(2, lSortColumn), Cells(lLastDataRow, lSortColumn)), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(1, 1), Cells(lLastDataRow, lSortColumn))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0
Thanks Pbornemeier.

I tried using your code, unfortunately, the macro breaks at:
PHP: 
  ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add _
        Key:=Range(Cells(2, lSortColumn), Cells(lLastDataRow, lSortColumn)), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Any idea why?

Thanks,
FP
 
Upvote 0
I'm not sure what the code is supposed to next - Sort column "H" to a different sheet?

PHP: 
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add _
        Key:=Range(Cells(2, lSortColumn), Cells(lLastDataRow, lSortColumn)), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(1, 1), Cells(lLastDataRow, lSortColumn))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
Upvote 0
I figured it out, Pbornemeier. Thank you.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
When it’s sorted, it aligns the row with “Inpatient accts” evenly.<o:p></o:p>
Your method works great.<o:p></o:p>
<o:p> </o:p>
Thanks.<o:p></o:p>
FP
 
Upvote 0
If you are using Excel 2003 or earlier, Delete <code style="white-space: nowrap;"><code>SortOn:=xlSortOnValues,from the sort method.

I should have changed </code></code><code style="white-space: nowrap;"><code>
ActiveWorkbook.Worksheets("Sheet1").Sort to </code></code><code style="white-space: nowrap;"><code></code></code><code style="white-space: nowrap;"><code>
ActiveWorkbook.Activesheet.Sort

Although to prevent potential problems, the specific worksheet name should be used wherever required.

Glad it worked for you.
 </code></code>
 
Upvote 0
Hi Pbornemeier,

I have a question regarding the code that you provided for me awhile back ago. Now I need to distribute the rows evenly for 3 Account Classes as opposed to one ("Inpatient" class).
Now if the Acct class is "Inpatient", "Emergency" and "Outpatient, then that get grouped as one.

My ultimate goal is distribute the accts evenly among the staff.
Let say, I have a list of 50 accts and 7 accts are “Inpatient”, 10 accts are “Emergency” and 2 accts are “Outpatient”. And I distribute the work to 3 employees; the breakdown should be as follows:

Employee1: Gets 16 accts (2 accts are “Inpatients”, 3 accts are “Emergency”, 0 acct is “Outpatient” and 11 “other” accts)
Employee2: Gets 17 accts (2 accts are “Inpatients”, 3 accts are “Emergency”, 1 acct is “Outpatient” and 10 “other” accts)
Employee3: Gets 17 accts (3 accts are “Inpatients”, 4 accts are “Emergency”, 1 acct is “Outpatient” and 9 “other” accts)

How can I modify your code (below) to reflect this change?
The code works fine when it was only the “Inpatient” Class to be distributed evenly

Thank you,
Sub Sorting_Accts_Evenly()
'
' *********************************************************************
' Distribute the "Inpatient", "Emergency" & "Outpatient" accts evenly
' **********************************************************************

Workbooks.Open Filename:="S:\Revenue Cycle Operations\CCE\DEMO\2118.CSV"
Sheets("2118").Select


Dim lECount As Long
Dim lICount As Long
Dim lIInitialCount As Long
Dim lLastDataRow As Long
Dim lX As Long
Dim lInterval As Long
Dim aryI() As Variant
Dim aryE() As Variant
Dim lSortColumn As Long

'// The # of the column that contains the class of patient (Inpatient or Other)
Const lClassColumn As Long = 4

'The blank column at the end of the data field will be used as a helper column
'it will hold the sort data
lSortColumn = Cells(1, Columns.Count).End(xlToLeft).Column

lLastDataRow = Cells(Rows.Count, 2).End(xlUp).Row
For lX = 2 To lLastDataRow
If Cells(lX, lClassColumn).Value = "Inpatient" Then
lICount = lICount + 1

Else
lECount = lECount + 1
End If

Next
lInterval = ((lICount + lECount) \ (lICount))
lIInitialCount = lICount
lICount = 1
lECount = 1
For lX = 1 To lLastDataRow - 1
If lX Mod lInterval = 0 Then
If lICount <= lIInitialCount Then
ReDim Preserve aryI(1 To lICount)
aryI(lICount) = lX
lICount = lICount + 1
Else
ReDim Preserve aryE(1 To lECount)
aryE(lECount) = lX
lECount = lECount + 1
End If
Else
ReDim Preserve aryE(1 To lECount)
aryE(lECount) = lX
lECount = lECount + 1
End If
Next

lICount = 1
lECount = 1
For lX = 1 To lLastDataRow - 1
If Cells(lX, lClassColumn).Value = "Inpatient" Then
Cells(lX + 1, lSortColumn + 1).Value = aryI(lICount)
lICount = lICount + 1

Else
Cells(lX + 1, lSortColumn + 1).Value = aryE(lECount)
lECount = lECount + 1
End If
Next

Cells(1, lSortColumn + 1).Value = "Sort"


Cells.Select
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Columns("U:U").Delete

End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
817
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Adding a custom message.
Replies
1
Views
422
Micron
M
V
  • Question
Issue with scraping with VBA
Replies
1
Views
491
John_w
J
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

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