Excel challenge, please save a doctor some time!

[ari.ballonoff]

I need to convert cells in one worksheet into 1's and 0's and
correlate it with a value.

For example, if this was my starting worksheet (which I'm provided
with):

RowA RowB=1 RowC=0
6 3 2
12 5 4
18 1 2

I want to convert the above worksheet into a worksheet that had 2
rows, one being the value of RowA above, and the other being either a
1 or a 0, depending on whether it was rowB or rowC, and the number of
rows equals the number in rowB or rowC above. So, this example would
convert to a new worksheet of:

RowA RowB
6 1
6 1
6 1
6 0
6 0
12 1
12 1
12 1
12 1
12 1
12 0
12 0
12 0
12 0
18 1
18 0
18 0

Does anyone know how to do this without having to enter each value
separately?

Thanks,
Frustrated Doctor

 

[Guest]

Try this:

Sub DrMacro()
Dim myRange As Range
Dim r As Range
Dim lRow As Long
Dim bRow As Long
Dim newSht As Worksheet

bRow = 2 '<~~~change as needed to identify where the first row of data is
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Cells(1, 1)
Set myRange = myRange.Offset(bRow - 1, 0).Resize(lRow - bRow + 1, 1)

Set newSht = Sheets("Sheet2") '<~~~~change as needed
lrow1 = newSht.Cells(Rows.Count, 1).End(xlUp).Row
For Each r In myRange
If r.Offset(0, 1).Value > 0 Then
For i = 1 To r.Offset(0, 1).Value
newSht.Cells(lrow1, 1).Value = r.Value
newSht.Cells(lrow1, 2).Value = 1
lrow1 = lrow1 + 1
Next i
End If
If r.Offset(0, 2).Value > 0 Then
For i = 1 To r.Offset(0, 2).Value
newSht.Cells(lrow1, 1).Value = r.Value
newSht.Cells(lrow1, 2).Value = 0
lrow1 = lrow1 + 1
Next i
End If
Next r

End Sub

HTH,
Barb Reinhardt