Solved Spreadsheet data Copy and Paste with loop

January 26, 2013 at 03:42:10
Specs: pc
Hi,

I have a spreadsheet with patient id and patient name on the first row and service details on the following rows such as dates and provider names.

patient id1 patient name1
serv date1 provider
serv date2 provider
Total for client: 2
Patient id2 patient name2
serv date1 provider
serv date2 provider
serv date3 provider
Total for client: 3
Patient id3 patient name3
serv date1 provider
Total for client: 1
Patient id4 patient name4
serv date1 provider
serv date2 provider
serv date3 provider
Total for client: 3

My question is how do I write a macro to copy each patient id and patient name and paste them right next to each of their serv dates with a loop. Thank you so much in advance. Your help is greatly appreciated.

Thanks,
Tiffany


See More: Spreadsheet data Copy and Paste with loop

Report •

#1
January 26, 2013 at 09:47:43
✔ Best Answer
Hi Tiffany

Here is a rough start that makes a bunch of assumptions like

your data is in columns 1 and 2
the first patient ID and Name is in Row 1
The word "Total" is the key to when a new patient name will start on the next row
there are no blank rows

Let me know if any of these assumptions are false and what the real case is so that we can adjust the macro.

Otherwise, give this a try on a Copy of your spreadsheet.

Sub PatientIDs()

LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Get first set starting on 1st Row assuming this is where the first name is
pID = Cells(1, 1)
pName = Cells(1, 2)

For i = 2 To LastRow
'Look for the word "total" in the previous row to identify the name and ID
    If UCase(Left(Cells(i - 1, 1), 5)) = "TOTAL" Then
        pID = Cells(i, 1)
        pName = Cells(i, 2)
       
'Otherwise place the ID and name in Cols 3 and 4
    Else
        Cells(i, 3) = pID
        Cells(i, 4) = pName
    End If
Next

End Sub

This is how I imagine your data looks like

	Col A	        Col B
Row 1	patient id2	patient name2
Row 2	serv date1	provider
Row 3	serv date2	provider
Row 4	Total for Client	2
Row 5	patient id3	patient name3
Row 6	serv date1	provider
Row 7	serv date2	provider
Row 8	serv date3	provider
Row 9	Total for Client	3
Row 10	patient id4	patient name4
Row 11	serv date1	provider
Row 12	Total for Client	1
Row 13	patient id5	patient name5
Row 14	serv date1	provider
Row 15	serv date2	provider
Row 16	serv date3	provider
Row 17	Total for Client	3

and this would be the result

	Col A	        Col B	         Col C	          Col D
Row 1	patient id2	patient name2		
Row 2	serv date1	provider	patient id2	patient name2
Row 3	serv date2	provider	patient id2	patient name2
Row 4	Total for Client	2	patient id2	patient name2
Row 5	patient id3	patient name3		
Row 6	serv date1	provider	patient id3	patient name3
Row 7	serv date2	provider	patient id3	patient name3
Row 8	serv date3	provider	patient id3	patient name3
Row 9	Total for Client	3	patient id3	patient name3
Row 10	patient id4	patient name4		
Row 11	serv date1	provider	patient id4	patient name4
Row 12	Total for Client	1	patient id4	patient name4
Row 13	patient id5	patient name5		
Row 14	serv date1	provider	patient id5	patient name5
Row 15	serv date2	provider	patient id5	patient name5
Row 16	serv date3	provider	patient id5	patient name5
Row 17	Total for Client	3	patient id5	patient name5


Report •

#2
January 26, 2013 at 12:21:26
It works!!! Thank you so much for your help, AlteK!!!

Thanks again,
Tiffany


Report •
Related Solutions


Ask Question