If data matches pull to a new sheet

Please can you help?

I would like to be able to pull across a whole line of data from a separate table to a new sheet if the data matches.

I.e. I want it to automatically pull anything with clinic to a new sheet so I can sort by grade order. Is this possible

If this is my data table
data.jpg

I want to automatically pull the CLINIC department across to a new sheet to create a table like this (where it will pull the data and I can add to it, but if the data in the original sheet changes it will automaticaly change my second sheet)

new table.jpg


Thanks

It would be better if you were to enter the supplementary data into the main table, rather than in the subsidiary table if your main data may change. Suppose you had extracted the data as shown, and then row 4 of the main table were to change to Clinic - that record would then appear on row 3 of the subsidiary table and all other records would be moved down to accommodate it. But, any data that you had entered into columns H onwards would NOT be moved down, so it would not be in synch.

Hope this helps.

Pete

Thanks Pete - my main issue is my original data source is a huge table with many columns and data I dont really need as is shared with many other departments, but as this is the only one that is accurately updated I was looking to see if there was a way to pull across the specific data I needed so that I could add my own personal columns to that one. Does that make sense? I.e. I only need the clinic departments rather than the others and with that I wanted to then add my own comments.

Thanks for your help

Yes, it is something that I have encountered many times on this forum. As long as you realise the problems and accept that your data is a snapshot at a particular time, then it should be okay.

Are you allowed to add another column to the main data which would help to identify the records that you want to extract?

It would also help if you attached a sample Excel workbook, as detailed in the yellow banner at the top of the screen.

Pete

Thanks Pete. Please find attached. Not going to be easy to edit the main document, just need to find the best way to pull the data from what is there already and then will need to do all the editing on the next sheet,

But you are allowed to create a new sheet? (Which can then be dragged away to make a new file).

Pete

Yep thats fine, just cant really change the data sheet 1

Okay, I'm just about to go out now, but I'll pick this up again this evening.

Pete

In the attached file I have created a new sheet by clicking on the + symbol next to the tab names, and renamed it Extract. Everything now happens in this new sheet, so the original is unchanged.

I've used column H as a helper column, with this formula in H2:

=IF(Sheet1!A2="Clinic",MAX(H$1:H1)+1,"-")

This should be copied down for as many rows as you have in your main sheet. In this example I have copied down to row 20 - the hyphens help to show where the formula is active. The formula identifies those records which have "Clinic" in column A of the main data sheet, and allocates a unique sequential number to each one.

I have copied the headings onto row 1, and then used this formula in cell A2:

=IF(ROWS($1:1)>MAX($H:$H),"",INDEX(Sheet1!A:A,MATCH(ROWS($1:1),$H:$H,0)))

This can then be copied across into B2:F2, and then those formulae can be copied down until you start to get blanks (the largest number in column H indicates how many rows you need to copy the formulae down for). I've copied down to row 10 in the attached file.

As you just want a snapshot of the data, you can then fix the values of the formulae in columns A to F, by selecting all the cells with the formulae in, then click on Copy | Paste Special | Values | OK then press the Esc key. You can then delete column H, and insert a new column E with the heading Budgeted WTE, and then start adding your new data from column H onwards.

You can also drag the sheet tab to a new area and use File | Save As ... to create a new file independent of the main data.

Hope this helps.

Pete

Pete that's great and highly impressive - I would never have been able to come up with that.

I have however, tried to transfer to my data sheet and I must have done something wrong as cant seem to get it to work at all. Sorry to be a pain, hoping its a small error but any chance you can have a look. Whereby trying to pick up "CC" rather than clinic but otherwise the same...

There are a couple of things to correct.

Firstly, you put the helper formula in cell AI1, whereas it should be in cell AI2:

=IF(Sheet1!H2="CC",MAX(AI$1:AI1)+1,"-")

Secondly, as your have records down to row 20 in your main sheet, you should copy this formula at least down to row 20 (though it does not matter if you copy it further).

Make those changes and your data will automatically appear on the left-hand side.

Hope this helps.

Pete

Pete, you're a genius. thanks so much.

Glad to help, and thanks for the rep.

Pete