copy rows based on a value in a cell

my data set references referrals to a provider, so it may say Referral Provider referred 5 patients during January. I'd like to reference the 5 to create 5 total row entries instead of a single row entry

see row 4 in attached. Instead of a single row that shows Dr Cato referred 5 patients(Col F) I'd like 5 rows, each representing 1 referral from Dr Cato

excel test.xlsx

Put zero in cell G1 and this formula in G2:

=IF(F2="","",F2+G1)

Copy this down to the bottom of your data (or beyond, to accommodate new data being added - I've copied to row 20 in the attached file). This formula just gives a cumulative count of the number of patients, so you can see that the last number (51) is the number of rows that you will need to have to record all patients individually.

You can then use this formula in cell I2:

=IF(ROWS($1:1)>MAX(G:G),"",MATCH(ROWS($1:1)-1,G:G)+1)

and this will return the row where we want to get the data from. The following formula in J2:

=IF($I2="","",INDEX(B:B,$I2))

will get the data from column B on the row given by I2, and this formula can be copied across into K2:M2 to get data from the other three columns, C to E.

Finally, you can copy the formulae in I2:M2 down as far as needed (given by the highest number in column G) - I've copied to row 55 in the example file, just to ensure that I pick up all the data.

Hope this helps.

Pete