Have been pondering over the following for the past few days, and as a result, developed a bald spot and permanent frown. Can you please help!
In one Excel tab I have a list of details - 1 record per row.
(e.g. A1:E1 = First Name, Last Name, Phone, DOB, Size)
Hypothetically I have 50 records. (i.e. database range = A1:E50)
What I want is for any records (i.e. rows) on this initial tab, where the criteria matches B="Smith", D="01/01/70" and E="14", I want the entire row contents to be duplicated on a second tab within the worksheet.
This second tab will be a simple report of my search results matching my criteria, and will display the entire 'record' - which includes information in the relative columns. i.e. the criteria only applies to columns B, D & E, however I want my second tab to report cells A, B, C, D & E.
Furthermore, in my hypothetical records & criteria, 6 records match and need to be reported.
I hope this makes sense, and would really appreciate an answer ASAP!
I have 2 programs where I do this exact same thing.
On your first Sheet is your list. Copy the titles of each row (just the titles).
Go to Sheet 2 and paste onto cell A2. Also, paste those same titles in cell A4.
You should have something that looks like: NAMEPHONECORDLESSBEEPERADDRESSSS#NAMEPHONECORDLESSBEEPERADDRESSSS#
Now you are ready to go. Go to Data... Filter... Advanced Filter.
Under action press the radio button... Copy to another location.
Under list range set the range for your list on Sheet1. Many times I give the range of cells a name like "Addresses". Then I just type the name.
Under Criteria Range set it to A2:F4 (in the example above)
Under Copy To set the range to A4:F30
When you hit OK button your results will transfer.