Running in to two problems.

[David Moore]

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]

#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]

#1

I'll try that.

#2

I used this and it worked =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

 

[David Moore]

It worked. Thank you so much!!!!!