Hi all,
I am looking for an ‘array-type’ formula (…I think) that will search a range of cells and grab the first value and copy it to the first cell, then continue on and look for the next cell in the range with a value, then copy that value into the next cell etc…sorry, kind of complicated, but I’m not sure how to explain it any better. I have attached a spreadsheet for clarification (See columns R and S)
Thanks in advance -
Last edited by Greed; 10-28-2011 at 11:12 AM. Reason: Solved
Why in R4 would you want N4 when N4 is blank?
Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
Sorry for the confusion - I had deleted a column after writing the instructions...workbook has been updated
Try:
confirmed with CTRL+SHIFT+ENTER and copied across (and down, if needed)...=INDEX($N4:$Q4,SMALL(IF($N4:$Q4<>"",COLUMN($N4:$Q4)-COLUMN($N4)+1),COLUMNS($N4:N4)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That seems to work well - texcept for the error when there are no more values to show...is there a bit you can add to avoid getting #NUM! when the last value has been shown?
Thanks again
Try:
CSE confirmed.=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($N4:$Q4,SMALL(IF($N4:$Q4<>"",COLUMN($N4:$Q4)-COLUMN($N4)+1),COLUMNS($N4:N4))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Super - I am in awe of your vast knowledgethanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks