+ Reply to Thread
Results 1 to 3 of 3

Duplicate Sheet but with Condition

  1. #1
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Duplicate Sheet but with Condition

    In my Sheet1 , I have a column with numbers N
    How can I write a formula in Sheet2 so in Sheet2 I will have ONLY rows from Sheet1 but with N>10 ?
    Right now I have in Sheet2 a duplication of Sheet1 but I would like to eliminate all rows with N<10

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Duplicate Sheet but with Condition

    Why not just copy and paste values and just delete the first 10 rows?

    Or if you want a formula solution

    You havent specifed what column N is in nor how far each row extends across columns or how many rows we should be copying, so I 'll have to guess all that.
    Im assuming Sheet1 Column A and each row goes no further than Column ZZ and there are no more than 10000 rows. Adjust the formula accordingly.

    in Sheet2!A1
    =IFERROR(INDEX(Sheet1!A$1:ZZ$10000,AGGREGATE(15,6,ROW(A$1:A$10000)/(Sheet1!A$1:A$10000>10),ROWS(A$1:A10000))-(1-1),COLUMN()-(COLUMN()-1),COLUMN()),"")

    Copy across as far as column ZZ and as far down as row 10000.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Duplicate Sheet but with Condition

    Pitty you've not attached sample file.

    In N2 in second sheet try such array formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and copy it down and left and right as needed. Note that for empty cells in sheet1 you'll get 0's in sheet 2
    limiting rows number (not whole columns)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will speed up the formula.

    PS. As for copy-paste solutions (I do not think it's acceptable option, but may be?) try filtering out unwanted values then select all visible, copy and paste to second sheet.
    Attached Files Attached Files
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup with duplicate values & AND Condition
    By Vivek2705 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2018, 06:47 AM
  2. [SOLVED] If condition for sum duplicate number
    By rchure in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-12-2016, 04:29 PM
  3. Add a condition if there are any non-duplicate data
    By salmasaied in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-06-2016, 09:29 PM
  4. Filter duplicate entries on condition
    By Mcell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2013, 05:54 AM
  5. Search column a for duplicate data and copy entire row to duplicate sheet
    By crazyAMP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2012, 08:21 PM
  6. Replies: 2
    Last Post: 09-28-2011, 09:12 AM
  7. Replies: 7
    Last Post: 04-26-2010, 06:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1