classify data

[kalos]

hello

I need to place data that belongs in specific dates to a calendar's specific date fields

I was thinking to create a calendar in Excel, where each cell will be a specific date, then type the data I want in a cell, then calculate the dates where the data needs to be written in, then somehow(?) automatically move the data to the correct cells of the calendar (not overwrite any existing data)

any idea how can I do this?

thanks

[kalos]

in case I didnt make it clear, I attach some screenshots of what I have at start and of what I should have at end:
START:

END:

any idea? can a function or a macro or anything else do it?
thanks!

[IainB]

Without understanding your user/business requirements (what you need to achieve for a specific purpose), it is difficult to advise.
Having said that, I think you would be better advised to use a database (e.g., Access) for this.

Hmm. Just thinking of using Excel, which I am rather rusty in...

If the "calculated date n" is used to hold the pointer to the destination cell which is to have the data inserted, then one approach could be to treat that destination cell's contents as a string and append the "my text" string to it, using Excel functions.
But this would only work, I think, where the destination cell's contents (i.e., the existing date and any appended text) were the same format - i.e., text, in this case.
However, your destination cells in the "after" picture have a changed date format, so I guess that could imply a date format(?) and so what I suggest would be impossible.

What you seem to be doing is treating a cell as a database record, with the header index being "Date". A database would seem to be simpler.
I would recommend you don't try this with Excel as it could be attampting to bastardise the thing in an 'orrible way - that's IF you could do, which I suspect you won't be able to very easily.

It might be easier if you listed your user/business requirements (what you need to achieve for a specific purpose), and then we might be able to help more.

[kalos]

thanks for your reply

my requirements are nothing more than what I mentioned in my previous post, I mean, I exactly and only do what I mentioned previously

if you have any specific question, I will be glad to answer

I was thinking to use an "append" function or macro, but I don't know how to start

the target cells appear to have date format in the screenshot, but this is not necessary imo, there will be no problem them being simple text

but the "calculated dates" must be in date format, because they will update according to the date I have set as "today date"

in simple words, the script/macro or whatever, will have to take "my text" and append it to the correct calendar cell

it would be really nice if inside the calendar cells (that will contain the date at top and the appended text at bottom), the date is highlighted, formated or somehow distinguished from the other text below, so that it would be easier to the eye to see which text correspond to which date

or maybe each date cell in the calendar should be splitted into two cells, one containing the date only and the other (below) containing the appended text

last, a shortcut or button preferably should trigger the data migration/binding across the cells (from source "my text" cell, to target cells of the calendar, with calculated dates as pointers)

thanks

[IainB]

@kalos: I think I understand now.
If you intend to do this in Excel, then I'm sorry, but I don't think I can help much with that.
A person who is an Excel wizard might be able to help though - I don't know if there are any on the DC Forum.

[capitalH]

See attached for an attempt (zipped - xlsx attachments not  allowed on DC)

Note that:
1) It is a bit more general than your example (every date can have different text).
2) It does not move the data - it references it (I personally do not like destroying data - and moving the data MUST be done with a macro - this is pure Excel)
3) It has a hidden line 1 and hidden column C - required to calculated the dates properly.
4) You can change the format of the date displayed (change DD-MMM in the formula to the format you want)
5) If you want an empty line between the date and the text, add another ",char(10)" (right before/after the first)
6) Cells MUST be formatted as wrap text, otherwise text will appear on a single line (i.e. linebreaks will be ignored)
7) Created using Excel 2007 - but should work in other versions as well, nothing special done

[capitalH]

the target cells appear to have date format in the screenshot, but this is not necessary imo, there will be no problem them being simple text

Good - that is the way I have done it - but it can be customised

but the "calculated dates" must be in date format, because they will update according to the date I have set as "today date"

As long as it is Excel dates and not "text" (Excel dates are numbers - and are just formatted to look like a date. If you change it to "Number" you will get a number of around 40000. If you calculate these dates in Excel, it is an Excel date and should work perfectly)

it would be really nice if inside the calendar cells (that will contain the date at top and the appended text at bottom), the date is highlighted, formated or somehow distinguished from the other text below, so that it would be easier to the eye to see which text correspond to which date

This I cannot do

or maybe each date cell in the calendar should be splitted into two cells, one containing the date only and the other (below) containing the appended text

This I can do - will upload another version if you are happy with the approach of my first upload

last, a shortcut or button preferably should trigger the data migration/binding across the cells (from source "my text" cell, to target cells of the calendar, with calculated dates as pointers)

This I cannot do. The links are live (references) and cannot be moved - unless you use macros. If the source data changes - the cells will immediately change (unless automatic formula calculations has been turned off). Should you want a copy (that does not update), I suggest you copy the data to another sheet/place.

[capitalH]

Sorry - version 2 was easier than I expected

1) Does bold formatting - but uses conditional formatting of Excel 2007 - may not work properly in Excel 2003 or earlier
2) Puts the text in a new row
3) Less complex than the previous version

[kalos]

Sorry - version 2 was easier than I expected

1) Does bold formatting - but uses conditional formatting of Excel 2007 - may not work properly in Excel 2003 or earlier
2) Puts the text in a new row
3) Less complex than the previous version

can you tell me what to do to reproduce it, because I have EXCEL 2003 and because I need to customize it a bit?

thanks!!

[capitalH]

Replication Steps:
1) In column A - put the dates of your input data (this can be in another location - but then you have to change the formula later)
2) In column B, for each date in column A  - enter the text you want to associate with this date (again can be in another location, but must be next to the dates)
3) In cell C1 (again, this can be another location - however you will need to change the other locations from here relative to this cell - I am going to stop typing location stuff now), type 2011-01-01 (your first date)
4) In cell d1, "=C1+1", drag to I1
5) Copy c1:I1 to c3:I3
6) c3 "=I1+1"
7) Copy c3:I3 to  c5:I5, c7:I7
In cell c2 "=IF(ISNA(VLOOKUP(C1,$A$1:$B$100,2,0)),"",VLOOKUP(C1,$A$1:$B$100,2,0))" (Column A- dates from step 1, B-Text from step 2, 100 was chosen as upper limit - change if you need more (or less) than 100)
9) Copy C2 to D2:I2, C4:I4, C6:I6, C8:I8

Conditional formatting (I cannot remember how to do this in Excel 2003)
Set the date cells to be bold when the row below is non-blank.



The attachment should work (I have removed the 2007 conditional formatting and formulas - but I cannot test it for you).

[kalos]

it is not exactly what I need and I think I cant make it by my own to be as the one I need, so can you help me with this please

the steps the user will follow are these:
1) place a date at "here I place a date: (this will make some dates be calculated below)
2) place your text at "here I place some text" (this will make the text append at the correct date in the calendar at right)

any hint?

[capitalH]

it is not exactly what I need and I think I cant make it by my own to be as the one I need, so can you help me with this please

the steps the user will follow are these:
1) place a date at "here I place a date: (this will make some dates be calculated below)
2) place your text at "here I place some text" (this will make the text append at the correct date in the calendar at right) (see attachment in previous post)any hint?

I am not sure what the last version that I uploaded cannot do in terms of what you are requesting now.

Type your first date in cell A1, and the formula to calculate the second date in A2.
First text in cell B1, second text in b2.
Etc.

[kalos]

it is not exactly what I need and I think I cant make it by my own to be as the one I need, so can you help me with this please

the steps the user will follow are these:
1) place a date at "here I place a date: (this will make some dates be calculated below)
2) place your text at "here I place some text" (this will make the text append at the correct date in the calendar at right) (see attachment in previous post)any hint?

I am not sure what the last version that I uploaded cannot do in terms of what you are requesting now.

Type your first date in cell A1, and the formula to calculate the second date in A2.
First text in cell B1, second text in b2.
Etc.

DC Excel3 gives me this screenshot:

which is not the same as the one I posted above
is this due to 2003 vs 2007 conversion or I miss something?