• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting Rows based on empty values on the left

N

NJ786

New Member
Hi There


File uploaded here,


https://docs.google.com/open?id=0B5jCQSsK1WJ9RHJmMkJrMTVTSE81c1pGY0tqN3Uydw


Any can help on the issue as i want the data from these rows to be transferred to another sheet and look like the one attached below,


https://docs.google.com/open?id=0B5jCQSsK1WJ9NVU3Wk43aDJSN3FTeExFTTAzUVdRQQ


Thanks


NJ
 
Hi NJ786


Kindly checkout this file:

http://dl.dropbox.com/u/60644346/NJ786_ExtraRows_Workedout.xls


Let me know if something goes wrong.


Regards,

Faseeh
 
Thanks


but i think it is a bit too much work for this thing, can we have a one liner formula where it does all that, pick data from sheet 1 and paste the data in sheet 2...


Appreciated.


NJ
 
Hi NJ786,


Kindly try the link in my last post, I have edited the file accordingly.


Thanks,

Faseeh
 
one question


why am i not able to go past the 200 mark? Origional!$A$1:$A$200 ???


any data available after that mark does not display, although i tried to modify the formula, still the two ".." appear...


any help


NJ
 
You need to update 200 for the No. Of rows. If, lets say you have 400 rows replace 200 with 400. I think this should fix it.
 
tried that but some how it is not accepting and still takes data up to the 200???? can you generate dummy data and try yourself?
 
Hi,


Try out this one!


http://dl.dropbox.com/u/60644346/NJ786_ExtraRows_Workedout_Upto_10000_Rows.xls


Only thing changed is A200 to A10000, This is an array formula use ctrl+shift+enter to execute. hope it works now.


Regards,

Faseeh
 
one more thing


the cells that have value in column C but no value in column A (empty), copy to the second sheet in column D just beside the Column C.


A.......B................C....................................D

5.......6................hello world....................."next chapter looping through database"


Appreciated


NJ
 
Hi NJ,


I have tried to fix the requirement in your last post, kindly check the following file:

http://dl.dropbox.com/u/60644346/NJ786_ExtraRows_Full%20%26%20Final%20%28V.%202003%29.xls


Please note following Points:

- Note for curly braces, these are array formulas and need to be executed pressing ctrl+shift+enter


-Sheet contains two named ranges ColA & List, if your data crosses 1000 rows, you need to go to the Formulas>Name> and increase the no. of rows to lets say 1500. (For both ColA & List you need to do same)


- The formula in the second sheet will only entertain one entry without any no. in the left most Column A, any second entry will not be displayed in Column D of second sheet.


If there is something still unclear let me know.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top