Automatically replicate the rows into new sheets when conditions are met

[PV001]

Hi There

I need your expert assistance please.

I am currently managing a list of doctors which are contracted to our company. The list contains approximately 700 lines.
I have extracted a few lines and attached as an example.

There are 3 different clinic Types (Traditional, MAP, PC). Each type is split between Medical & Dental.
There are columns which indicate the month which a doctor either started or terminated their contract.

The rows that include a start or termination date now need to be split as follows into a second sheet:
- Traditional_Medical_Started
- Traditional_Medical_Terminated
- Traditional_Dental_Started
- Traditional_Dental_Terminated
- MAP_Medical_Started
- MAP_Medical_Terminated
- Primecure_Medical_Started
- Primecure_Medical_Terminated
- Primecure_Dental_Started
- Primecure_Dental_Terminated

Is there a formula which could be inserted into the “Movement” sheet which would automatically replicate the rows from the “Master List”?

Would sincerely appreciate any assistance/advice!

Thank you
Regards
Patty

[mel0nc0lly]

To truly replicate (copy) data, you would need to use a macro, but we we need more details / a manual example to help more

[Pete_UK]

You could do this with a few formulae, but rather than have 8 different tables on your Movements sheet, each needing to be different sizes (or fixed sizes with many blanks in some of the tables), it might be better to have 3 drop-downs where you can select the clinic type, whether it is Medical or Dental, and whether it is Started or Terminated, and then just have one table which shows the results. You can easily change one of the drop-downs to view another table.

This can also be achieved with just a few formulae.

Let me know if this is an approach that you might be interested in.

Hope this helps.

Pete

[PV001]

Hi there

I have attached and example.

Thank you

[PV001]

Hi Pete

The movement sheet is a report which needs to be updated and distributed monthly.

The full master list should not be circulated.

I'm not familiar with macros at all but any suggestions are appreciated.

Thank you

[Pete_UK]

I went ahead with this on Friday anyway, and was just waiting for you to get back. In the attached file I show how you can do both.

For the composite table I have inserted a new sheet called Extract, and set up three data validation drop-downs in cells C2,D2 and E2 so that you can select which parameters you need. I put this formula in K3 of the Master list sheet:

=IF(AND(A3=Extract!$C$2,C3=Extract!$D$2,IF(Extract!$E$2="start",H3,I3)<>""),MAX(K$2:K2)+1,"-")

I've shown this in blue, and it can be copied down as far as you like in order to accommodate new data being added - the hyphens indicate where the formula is active, and it identifies those records which match the criteria and gives each a unique sequential number. In the Extract sheet I then used this formula in cell B5:

=IFERROR(INDEX('Master List'!A:A,MATCH(ROWS($1:1),'Master List'!$K:$K,0)),"")

which can be copied across to J5. Note that as you had a hidden column in the Master sheet, I also have a hidden column here. These formulae can now be copied down as far as you need them (I've only copied down to row 25, but if your real data is for 700 records, then you might need to copy it further). Then you can just change the drop-down values on row 2 and the table will automatically change.

In order to reproduce your Movement sheet, I have introduced a code to identify each record type, by taking the initial letter of the three parameters. I used this formula in M3 of the Master sheet:

=LEFT(A3)&LEFT(C3)&IF(H3<>"","S",IF(I3<>"","T",""))

and then this formula in N3:

=IF(M3="","-",M3&COUNTIF(M$3:M3,M3))

which adds a number to the end of the 3-letter code to give each record a unique ID. Again, these formulae (coloured green in this case) can be copied down as far as required.

In the Movements sheet I have added the appropriate 3-letter code in column A where each table heading is located, and then this formula in B3:

=IFERROR(INDEX('Master List'!A:A,MATCH(LOOKUP("zzz",$A$2:$A3)&$A3,'Master List'!$N:$N,0)),"")

Again, this can be copied across and down, with a slight adjustment to the ranges in the final 2 columns because of the hidden column in the Master sheet. Note that I've also adjusted the numbers in column A to formulae which will increment if copied down. The block of formulae from the first table can be copied down into each of the lower tables in turn.

I imagine that for your real file these tables will be much larger, so you will need to insert the necessary rows for each one and then copy the formulae down as required.

There is one slight drawback with this approach - if you have both a start date and a finish date for the same record, the finish date will not show in the Terminate table.

Anyway, hope this helps.

Pete

[PV001]

Thank you so much Pete!

This is amazing!

[Pete_UK]

Glad it helped you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete