Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running in to two problems.
Warning: I'm a total Excel newb with no formal Excel education.
Problem 1. I have a cell with the first date in it and another cell with the second date in it. I need to merge the two dates in to one cell. I've tried this code =A2&" - "&A4 but I get some weird numbers. I found that if I convert the A2 and A4 cell to text then it will work. But if they are set to Date then it won't. The issue being is that the A2 and A4 cells are mm/dd and the cell I'm merging to is essentially going to display mm/dd/yy - mm/dd/yy. Pretty much the merged cell ends up being a date range. The original cell, the reason I split it is because it says mm/dd thru mm/dd but in the merged cell I need the thru to be a hyphen. I figured it would be easier to split the cells. Perhaps it would be easier to just = the mm/ dd thru mm/dd and change the thru to a hyphen. I have no idea, which is why I'm asking this. Problem 2. I need to count the unique values in a cell range. I've looked online and I've seen SUMs and FREQUENCYs and whatever. The formulas don't appear to work for my application. I'll try to give an example to help explain my needs. Example I have a list range of CA ticket numbers: 1000 1000 1001 1002 1003 1000 There are 7 entries but the count of unique values is 4. The problem i saw with the formulas I found is that there is another range that they count against (this range says 1000 3 times, this other range has the number 1000, there were 3 that were this number therefore the count is 1). The problem with this is that after this worksheet we'll never be working with the same number again. So basically I need a formula that can just look at this range and figure out that 1000 came up 3 times but it's only going to count that once. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running in to two problems.
#1.
try: =text(a2,"mm/dd/yyyy") & " - " & text(a4,"mm/dd/yyyy") (use the date format you like) Same kind of thing with times and money: =text(a1,"hh:mm:ss") =text(a1,"$#,##0.00") or if you want a certain number of decimals. #2: You can use a formula like: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) to count the number of unique entries in A1:A10. (you can only use the entire column in xl2007) If you're looking for the number of times 1000 appeared in column A: =countif(a:a,1000) David Moore wrote: Warning: I'm a total Excel newb with no formal Excel education. Problem 1. I have a cell with the first date in it and another cell with the second date in it. I need to merge the two dates in to one cell. I've tried this code =A2&" - "&A4 but I get some weird numbers. I found that if I convert the A2 and A4 cell to text then it will work. But if they are set to Date then it won't. The issue being is that the A2 and A4 cells are mm/dd and the cell I'm merging to is essentially going to display mm/dd/yy - mm/dd/yy. Pretty much the merged cell ends up being a date range. The original cell, the reason I split it is because it says mm/dd thru mm/dd but in the merged cell I need the thru to be a hyphen. I figured it would be easier to split the cells. Perhaps it would be easier to just = the mm/ dd thru mm/dd and change the thru to a hyphen. I have no idea, which is why I'm asking this. Problem 2. I need to count the unique values in a cell range. I've looked online and I've seen SUMs and FREQUENCYs and whatever. The formulas don't appear to work for my application. I'll try to give an example to help explain my needs. Example I have a list range of CA ticket numbers: 1000 1000 1001 1002 1003 1000 There are 7 entries but the count of unique values is 4. The problem i saw with the formulas I found is that there is another range that they count against (this range says 1000 3 times, this other range has the number 1000, there were 3 that were this number therefore the count is 1). The problem with this is that after this worksheet we'll never be working with the same number again. So basically I need a formula that can just look at this range and figure out that 1000 came up 3 times but it's only going to count that once. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running in to two problems.
#1
I'll try that. #2 I used this and it worked =SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running in to two problems.
It worked. Thank you so much!!!!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems running Excel under IE | Excel Programming | |||
Problems in running a macro in another workbook | Excel Discussion (Misc queries) | |||
Problems running my app more than once | Excel Programming | |||
Problems running a sub having arguments | Excel Programming | |||
More UDF problems; some won't update, some won't stop running! | Excel Programming |