+ Reply to Thread
Results 1 to 14 of 14

storing multiple value in a single cell (through arrays or without arrays)?

  1. #1
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Post storing multiple value in a single cell (through arrays or without arrays)?

    hello experts
    i m new to excel programming, and know very little of it so plz any body help me in solving the problem,details of my data/table is as under:-

    veh start_time end_time Time_range
    1 08:24 08:50 08:24 to 10:50
    2 09:30 10:00 09:30 to 10:00
    3 12:00 13:00 12:00 to 13:00

    third column is time_range,i want to save the values from start_time to end_time (like 08:24,08:25,08:26..........09:00....10:00...10:50) in a single cell like array (i think so ) so that at later stages i compare a value from this range 08:24 to 10:50........can anybody help me, by any mean,formula, macro coding whatever

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    You could store the values like that but then you wouldn't be able to use them for comparisons.

    Why not just leave them as they are?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    thank u norie for ur timely respone, actually i m stilnot able to store values like that as i earlier mentioned........ actually i have to set a check on time let say a packet of 50 vehicles reach at some place A at 08:00 (which is start time) and it takes 30 min to reach complete vehicle packet which give me end time (08:00 + 0:30 = 08:30),now another packet of vehicles arrive at point A let say 08:25 which lies in the above veh time range, i want to print congestion on road..........thats y i want to store time range in third column....u have any better idea to do that

  4. #4
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    thank u norie for ur timely respone, actually i m stilnot able to store values like that as i earlier mentioned........ actually i have to set a check on time let say a packet of 50 vehicles reach at some place A at 08:00 (which is start time) and it takes 30 min to reach complete vehicle packet which give me end time (08:00 + 0:30 = 08:30),now another packet of vehicles arrive at point A let say 08:25 which lies in the above veh time range, i want to print congestion on road..........thats y i want to store time range in third column....u have any better idea to do that

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    Why can't you use both cells for the range/comparison?

  6. #6
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    how, i didn;t get you .............i am not able to understand how to give time range in excel......if there is any way to give range of time/value do let me know , i cannot store the each time instance in new cell as it well mess the workboot as there are so many other calculation going own

  7. #7
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    is it possible that we can talk live may be we have a better understanding of the problem there

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    this is not a live help desk!
    "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

  9. #9
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    sir i didnt said that its a live help desk, stillur comment helped alot :p

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    If you combine the 2 cells into one you won't be able to do anything with them.

    Can you explain what you are trying to calculate/compare?

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  11. #11
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    plz find the attachment,.....details are as under :-

    I want to make a calculator which calculates the congestion on road on time basis,emphazing on end time. Take row 3 & row 6 of workbook as an example, there road status is "congestion on road because end time of row 6 lies between the start time & end time of row 3, so at that time they will collide or make congestion. this is what i want to do.......... seriously expert i dont have any clue what to do.
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    Is the data table fixed as is, or are you allowed to manipulate it? My first thought is that this would be easiest if you can sort the data table on either start time or end time, then apply a relatively simple test, such as "is the end time of this group greater than or equal to the start time of the next group".

    Does that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  13. #13
    Registered User
    Join Date
    12-23-2014
    Location
    Pakistan
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    table is not fixed sir in a sense that vehcle group will add subsequently, if we put a check on end time, what it would be....we have to compare the end time with whole table??

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: storing multiple value in a single cell (through arrays or without arrays)?

    if we put a check on end time, what it would be....we have to compare the end time with whole table??
    The advantage that I see in sorting the table is that you should eliminate the need to test against the entire table. Based on the small example data set you have provided, it is enough to test immediate neighbors in the sorted table.

    After sorting the table, a formula like =if(or(c3>b4,b3<c2),"congested","clear") seems to give the same results as your sample results.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Defining multiple dynamic arrays imported into a single worksheet
    By jordlong in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-06-2014, 11:04 AM
  2. Declaring multiple multi-dimensional arrays (jagged arrays) - compile error?
    By dfribush in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2013, 05:06 PM
  3. [SOLVED] How would one create a single list of #'s (array) from multiple lists (arrays)?
    By Rstrand in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2013, 07:32 PM
  4. Creating a single vertical array from multiple column arrays
    By Bryan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2005, 03:15 PM
  5. Storing values to arrays for subsequent use
    By Peter Rooney in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1