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
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
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.
Pitty you've not attached sample file.
In N2 in second sheet try such array formula:
and copy it down and left and right as needed. Note that for empty cells in sheet1 you'll get 0's in sheet 2Formula:Please Login or Register to view this content.
limiting rows number (not whole columns)
will speed up the formula.Formula:Please Login or Register to view this content.
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.
Best Regards,
Kaper
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks