Hi,
I'm trying to get Excel to save me some time filling in years between a start year and an end year.
Basically I have a column of start years, and a column of end years, and I need a column of all the years in-between seperated by pipes.
So, we should have something like:
Cell 1: 1975
Cell 2: 1980
Cell 3: 1975|1976|1977|1978|1979|1980
I have about 700 of these so any help would be much appreciated!
Do you have always the same 5 year difference between the start and the end year? If don't, is there a (theoretical) maximum number of difference?
An example file is never useless!
Tried an example function of mine and got errors?
- Had you found them, replace ; with , and , with .
this is one way with functions max range 1991-2011 ie a 21 year span
"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
Assuming the start year(s) are in column A and the end year(s) are in column B, one way:
Please Login or Register to view this content.
The output will be in column C
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Yeah I was also working with a little macro with accessory cols. It has a 20 year limit as well.
Edit: never mind. It has a bug :|
Last edited by KiPA; 11-24-2010 at 01:35 PM. Reason: Fail
Should be better.
There's no limit with the code I provided. No helper columns either.
Regards
ok a udf use as =unconcat(a1,b1)
Please Login or Register to view this content.
Thank you so much guys. There are so many great solutions, I don't know which one to use first!
You're welcome. Thanks for the feedback.
Glad to help
---
I have ; separator with functions but I'm not sure how I have to use them when dealing with custom functions. I cannot get this working and I've tried all four considerable alternatives: a) , at cell and at code, b) ; at cell and at code, c) ; at cell and , at code, d) , at cell and ; at code.Originally Posted by martindwilson
Any advices?
change
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Yeah that didn't help. I strongly believe that the solution is related to that first code row. See, if I change the code so that the separator would be ; the editor reminds me: "Expected: list separator or )" so the separator must be comma (,). When I look the udf cell it yells "#NAME?"
So if I change the udf from =unconcat(A1;B1) to =unconcat(A1,B1) I got "The formula you typed contains an error." which usually in simple cases like this is because I used , separator instead of expected ;
But! Thanks anyway! I'll try to find (typical) udf separator problems with search engines.
Can someone please help me with this... I need this bit urgent.. Sorry if i disturbed anyone.. I am new.
http://www.excelforum.com/excel-new-...-attached.html
No need to be sorry. Though if you have not received (understandable) answers in your thread you should put a new post there to bring it up in the list again. Also you might want to check "Want to get your question answered quickly?" from rules
better still follow the rules ands start your own thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks