Thanks in advance for time and effort-
sheet 1 has two columns that need certain data copied to another cells.
The result is in 'sheet 2' column B2:B7. Basically i need to read column 'A' for populated cells and copy its status to another cell.
A2 has a status of YES. the value 'YES' would be copied to B2 sheet 2. and so on for each row. but empty rows will not be carried over-I dont want to see empty rows in sheet 2.
is it possible? what formula to use?
Thanks.
Look into pivot tables, and filter out the "blank" status.
If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
Hi tlafferty-
i wish i can; unfortunetly poivot table (albeit a great tool) doesnt lend itself too well to my spreadsheet which has over 50 columns and 500 rows and growing. plus i need to do this procedure for 50 sets of newly created data. the sample file leaves out many details you wouldnt be interested in; only enough for the situation. Thanks for the suggest though.
Why not delete the empty row in sheet1?
It can be done with a macro.
I disagree you can't work with a pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I can not revise the source raw data.Why not delete the empty row in sheet1?
you would have to see the whole file to agree but I cant do that either and the Pivot table will solve one thing and create another-not the best option.I disagree you can't work with a pivot table.
Thanks for trying.
and in the second sheet the empty row can be deleted with a macro?
not in this case.Originally Posted by oeldere
a formula to drag down would be nice. I know in advance how many rows i will need and the excact data (six rows for the attached sample file) So I inserted six empty rows and now all i need is a formula to drag down these six rows. basically i am asking the formula to collect scattered data into one specific place. I already know what the data is-i just need to copy it over to a different place. and i dont want to copy and paste it manualy because the pointer to data can move. I am not asking on the best way to do this(pivot table, delete rows, etc) i just need a formula that can do what is in the sample file. Sorry if i am creating any confusions.
Last edited by Red fuji; 06-25-2012 at 02:27 PM. Reason: more detail
Maybe with index / match?
Just uncheck the zero in filter option, then, you could'nt see empty rows in sheet 2
It is getting close. Please delete the A2:A7 values in sheet two- these are text input manually; i want to formula to create these. what would the formula look like then? that same formula will be used to populate sheet 2 column A and B. I dont want the formula to look for specific values called out in sheet 2 because there wont be any manually input values. Please delete the values in column A sheet 2 for this sample. I guess i wasnt clear on that- if i am not mistaken, there will be a Subtotal in the formula somewhere.
Last edited by Red fuji; 06-25-2012 at 03:35 PM.
I would use this one.
Click on the button.
oeldere
I appreciate you trying to help but it is not what i am looking for; i can not have any Macros in the file plus it is not giving me the result i need. I am not sure what to do now-it is starting to get a bit frustrating. Maybe I should start a new thread on this issue for others to have a fresh look.
Will you explain why
1) you don't want to use a pivot table.
2) you don't want to use VBA
I bet you have a good reason for that.
It is not that i dont want to use them; it is just that i have not seen them give give me the results i need. and I have little leeway to work with on this file plus i am not an experienced user.
Last edited by Red fuji; 06-25-2012 at 06:31 PM.
Did you tried the given options?
e.g. the macro
You only have to push the button.
If it isn't working you get an error.
You can post them here (altough i'm not very good at VBA).
Assuming column A in Sheet1 wouldn't have duplicate entries, always be unique entries.
In Sheet2 A2, with CTRL+SHIFT+ENTER
=IFERROR(INDEX(sheet1!A:A,SMALL(IF(TRIM(sheet1!A$2:A$1000)<>"",ROW(sheet1!A$2:A$1000)),ROWS(A$2:A2))),"")
In B2,
=IF(A2="","",TEXT(VLOOKUP(A2,sheet1!A$1:B$1000,2,0),"0;-0;\N\/A;@"))
Then select A2:B2 and copy down as needed.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
here's another approach
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks