Filtered data - copy and paste in the same worksheet

  • Filtered data - copy and paste in the same worksheet

    In my Excel sheet, I have column R data which I filter.

    After filtering, I wish to copy the visible data into the equivalent row in column AF.

    Example:

    After filtering, rows 29 and 263 are visible.

    How do I copy data from cells R29 and R263 to AF29 and AF263 respectively without pasting anything in the non-visible AF cells.

    The only way that works so far is to copy and paste each row separately.

    This is not practical - there are too many rows.

    Is there a solution without using VBA?

  • So do I assume that there is no simple solution to this challenge?


    The best I have come up with so far does not start with filtering......

    I copy the unfiltered data from Column R to Column AF.

    Then I use Home/Find & Select/Replace to gradually remove the names in Column AF that are NOT of interest.

    Eventually, this leaves the name[s] of interest.

    This triggers the correct results in Column AO which I can filter for "Yes".

    Now I can see a presentable report in Columns K to T.

  • The sample workbook I sent you is only a small part of the actual Dbase of the Charity.

    I am a volunteer looking after this database


    The complete database has over 700 rows [[but the same columns].

    I inherited columns J to AD.

    Columns J to Q are mostly headings which must appear in reports if appropriate to the people selected.


    I have used the sample sent to you again to demonstrate how to display just the tasks which involve Jasmine.

    Copy all the data from column R to column AF

    Delete all entries that do not involve Jasmine [that leaves rows 11 and 12.

    [the formulae in columns AG to AO determine what rows need to be displayed - including all rows displaying relevant headings

    Filter column AO for "Yes" to create the final report


    A copy of the final result can be seen in the revised sample which is a attached.

    20210617_2021OpPlan_OzgridSample.xlsx

  • That doesn't make sense. Deleting all the rows. You simply need to filter for any rows containing Jasmine.


    It's a stretch to call it a database because it's not designed properly to work as a database.


    I've added code to filter it when a member is selected in P2

  • I apologise for my imprecise wording in #7.

    I used the term "Delete all entries that do not involve Jasmine [that leaves rows 11 and 12]."


    More precisely ...

    "Clear the entries in cells in column AF that do not involve Jasmine"


    The formulae in columns AG to AO will automatically determine what rows need to be displayed in the report - including relevant headings.

    Filter column AO for "Yes" to create the final report


    Example - refer to the worksheet attached to #7.


    In #1 I asked....

    Is there a solution without using VBA?

    The volunteers in the non-profit organisations that use my Excel contributions have only basic to medium competency in Excel.

    If I use VBA and MS ceases to support a VBA command [that has happened a few times] and I am no longer around to help, there is a problem.

    There are not a lot of VBA-experienced volunteers around to fix it.