Dear Sir
We have columns in an excel spreadsheet whereby some cells have numbers while also empty cells.
We want the formula to trace back to the last time when the numbers in a column were at most one cell apart.
There are two possible scenarios for this:
1) there are numbers in consecutive cells; or
2) the numbers are exactly 1 cell apart
Please refer to the enclosed excel spreadsheet for illustration.
Can you help us with suggesting a formula ?
Thank you very much!
Please update your forum profile with the correct OFFICE version you are using (NOT Windows).
I have looked at your attachment: it is not at all clear what you are trying to do. Where will I find your expected results, manually mocked up?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The expected answers are 1700 rows down!! They still don't make much sense to me, though. Why we needed to see 1700 rows when 10 or 20 would have been plenty is also a bit of a mystery.
Lamlam... please explain exactly why 9 is your expected answer in C1760.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Yes, a smaller sample set would make this easier to follow.
Hi
Please refer to the enclosed file as revised. Hope this is understandable.
I checked this can be opened in a Window 10 computer.
The expected result is in Row 43 in this revised file.
Lamlam - please see post #2 where I asked you to do this:
We are not interested in your version of Windows - it is the version of Office you need to show. What is that?Please update your forum profile with the correct OFFICE version you are using (NOT Windows).
ARRAY formula in C43 then copied other cells
How ARRAY formula is entered
Please Login or Register to view this content.
Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
AliGW, sorry I mis-understood you. Update of my profile made.
kvsrinivasamurthy, the formula is brilliant and seems to work well. Thank you very much!
Thanks for the update.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Thanks for feed back.
Last edited by kvsrinivasamurthy; 07-15-2019 at 05:53 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks