How to automatically copy rows to new sheet when matching a value in one column

Dooid

New Member
Joined
Dec 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I've been trying to find a solution online without success. It seems a fairly simple thing that I want to do but finding a way to write a formula that will automatically do it, is another matter.

Screenshot 2021-12-03 at 14.52.44.png


In the spreadsheet above, what I want to do is create a formula that will search the data set with the yellow header (i.e., A1:D19) for a specific value/criteria in column D (e.g., I defined the value/criteria as "1" in cell B21) and then copy the rows that contain that value/criteria into a new sheet. For example, the orange data sets represent the new sheet (I would a sheet for each different value in Column D, so for this data set it would need 4 new sheets). In the example, I set the criteria to "1" so Sheet 1 has all the rows that have "1" in Row D of the master sheet.

At the moment, I just do this manually by filtering the values in Column D and then copy/pasting to a new sheet. If possible, I would like to have a formula I could put in the target cell of each new sheet that would automatically extract the date from a master sheet. I want to make it as simple as possible for the end users. I literally just want them to paste a data set into the master sheet and let the formulas in the other sheets automatically extract the rows.

I hope I explained myself clearly. I would be ever so grateful for your help. Thank you in advance.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
MrExcelPlayground5.xlsx
ABCD
1NameBandInstrumentValue
2PaulBeatlesBass1
3JohnBeatlesVocals2
4GeorgeBeatlesGuitar3
5RingoBeatlesDrums4
6MickStonesVocals1
7KeithStonesGuitar2
8ChuckStonesDrums3
9RonnieStonesBass4
10BrianStonesGuitar1
11JimDoorsVocals2
12RayDoorsPiano3
13JohnDoorsDrums4
14BobDoorsGuitar1
15AxGnRVocals2
16/GnRGuitar3
17DuffGnRBass4
18IzzyGnRGuitar1
19SteveGnRDrums2
20
21Criteria1
22Other Sheet
23PaulBeatlesBass1
24MickStonesVocals1
25BrianStonesGuitar1
26BobDoorsGuitar1
27IzzyGnRGuitar1
Sheet15
Cell Formulas
RangeFormula
A23:D27A23=FILTER(A2:D19,D2:D19=B21)
Dynamic array formulas.
 
Upvote 0
Wow! Thank you. I feel both incredibly grateful and stupid at the same time. Thanks a million :):)
 
Upvote 0
365 is filled with delightful new features. Have a look at all it has to offer.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top