excel counting help

[frazil]

visual basic (.NET)

 

[DVINNY]

If you were looking for identical values, why not SORT them? I can scroll thru 15,000 sorted numbers in like.... .... .... 3 days or so. What's wrong with that?

 

[MA_PE]

If you were looking for identical values, why not SORT them? I can scroll thru 15,000 sorted numbers in like.... .... .... 3 days or so. What's wrong with that?

Nothing, as long as you get paid by the hour and not by the job.

 

[TouchDown]

I didn't read through all of them, but XL has a "COUNTIF(...) function.

So,

COUTIF ("A2:B22", "D2")

Will return how many times D2 occurs in that range.

It's been a while since I used it, so you'll have to check syntax on the range, but it works fine.

I have XL-2007 version at home - just installed it and saw where it also has a new function called "AVERAGEIF", which would do the same as if you used (SUMIF / COUNTIF)...

 

[MA_PE]

I didn't read through all of them, but XL has a "COUNTIF(...) function.
So,

COUTIF ("A2:B22", "D2")

Will return how many times D2 occurs in that range.

It's been a while since I used it, so you'll have to check syntax on the range, but it works fine.

I have XL-2007 version at home - just installed it and saw where it also has a new function called "AVERAGEIF", which would do the same as if you used (SUMIF / COUNTIF)...

hey TD: that's cool. For Fraz's application, as I see it, it would eliminate the interim step of creating 4600 columns and then summing.

You'd need to create 4600 COUNTIF cells (1 for each unique location).

With the proper cell references to permit copying, I think it might be an alternate.

However, (Assuming you know how to program) the program structure listed above is by far the most efficient JMO.

Thanks for the info, I'll probably use that in the future,

 

[RIP - VTEnviro]

I :laugh: Excel. It's one of things where everytime you have a conversation about it, you learn some new function that's buried in it and new ways of doing something you already knew how to do. And it's generally because you had to do some freakishly obscure task like this and learned a new formula.

 

[ktulu]

Yeah, but now we use Vista, and Excel is pissing me off. They have moved a bunch of function buttons around, and I cannot find anything. Took me 15 minutes to find the "Merge and Centered" button...

 

[Guest]

I :wub: Excel. It's one of things where everytime you have a conversation about it, you learn some new function that's buried in it and new ways of doing something you already knew how to do. And it's generally because you had to do some freakishly obscure task like this and learned a new formula.

I smell a --**-- Excel Clinic --**--, Official Excel Nerd-Talk thread coming to a topic near you.

:Locolaugh: :Locolaugh:

JR

 

[frazil]

I didn't read through all of them, but XL has a "COUNTIF(...) function.
So,

COUTIF ("A2:B22", "D2")

Will return how many times D2 occurs in that range.

It's been a while since I used it, so you'll have to check syntax on the range, but it works fine.

I have XL-2007 version at home - just installed it and saw where it also has a new function called "AVERAGEIF", which would do the same as if you used (SUMIF / COUNTIF)...

Excel has an example that looks exactly like what I want to do, which seems similar to what you're suggesting too. That's why I thought this would work:

=sum(if((A2:A22=C2)*(B2:B22=D2),1,0))

which I think is the same as:

=sum(if(AND((A2:A22=C2),(B2:B22=D2)),1,0))

But it didn't. It gave me "1" for the first 2 rows and "0" for everything after that. I'm sure there's a way in excel, but I couldn't figure it out.

 

[MA_PE]

Excel has an example that looks exactly like what I want to do, which seems similar to what you're suggesting too. That's why I thought this would work: =sum(if((A2:A22=C2)*(B2:B22=D2),1,0))

which I think is the same as:

=sum(if(AND((A2:A22=C2),(B2:B22=D2)),1,0))

But it didn't. It gave me "1" for the first 2 rows and "0" for everything after that. I'm sure there's a way in excel, but I couldn't figure it out.

of course if you embed a VB macro within Excel it will probably do it, but the quick and dirty qbasic program listed above from ~20 years ago is both faster and much more efficient for this task.

Fraz: confess, how long did it take you to write the program (and calculate the distances between the points)? comapre that to the amount of time required to "trick" the excel function into doing what you want it to. Excel is not the right tool for this job. JMHO.

 

[TouchDown]

I needed to read a little closer - you want to compare A to B and count how many times they are unique and the same? That's more of a challenge. If they are sorted in order, you could easily do a VBA routine that could test it, have rotating counters and count until it changes, doing comparison if's... then just record results in a new column with the "matching" list.

Is it sensitive information? Could you post it, I'd take a look and see what I could come up with.

 

[RIP - VTEnviro]

I smell a --**-- Excel Clinic --**--, Official Excel Nerd-Talk thread coming to a topic near you.

I had to inform my boss last week that Excel has log-log graphing capabilities after the poor guy graphed something out by hand and did all sorts of complicated extrapolating and stuff to figure out what he was looking for.

 

[MA_PE]

I had to inform my boss last week that Excel has log-log graphing capabilities after the poor guy graphed something out by hand and did all sorts of complicated extrapolating and stuff to figure out what he was looking for.

or he could plot the log values on a linear plot.

 

[frazil]

I needed to read a little closer - you want to compare A to B and count how many times they are unique and the same? That's more of a challenge. If they are sorted in order, you could easily do a VBA routine that could test it, have rotating counters and count until it changes, doing comparison if's... then just record results in a new column with the "matching" list.
Is it sensitive information? Could you post it, I'd take a look and see what I could come up with.

TD - I've already written a program and got the data I need, but if you figure out a way to do this in excel I'd be interested -- this isn't the first time I've had to do something like this. Here's an example of some of the data. There are several repeats in the list (i.e. where the latitude AND longitude repeat). For each unique location I want to know how many times it shows up in the list.

Latitude , Longitude

42.0058 , -106.5006

42.71 , -108.6381

42.71 , -108.6381

44.404 , -108.1818

44.404 , -108.1818

42.0876 , -110.4075

43.2133 , -108.215

43.3008 , -108.7033

44.48 , -106.159

44.7551 , -108.1608

42.0061 , -109.5675

43.3503 , -107.5553

44.0325 , -108.9681

43.0063 , -108.3638

44.78 , -108.5116

43.6815 , -108.308

44.008 , -107.4188

42.0058 , -106.5006

42.832 , -105.4891

41.05 , -110.055

43.2133 , -108.215

44.7551 , -108.1608

43.3503 , -107.5553

44.65 , -106.1171

43.6815 , -108.308

44.008 , -107.4188

42.9006 , -108.5885

43.9136 , -108.0556

43.2133 , -108.215

 

[TouchDown]

Frazil - to make sure you've got Lat AND Long that are "identical for each instance"... I added a column # 3 with the following formula:

=A2 & "," & B2

That will give you for the first row... 41.05,-110.055 (as the result)... copy it down.

Now, select all of the data in that column top to bottom. Make a pivot table, with that column as the "row fields" as well as dropping it into the "data" area.

If it doesn't default to "count of", then you can right click on the data area and change field settings to count.

here's the result I got: (**edit - first time I pasted, the font style must have changed, it was difficult to read...)

Count of Lat/Long

Lat/Long Total

41.05,-110.055 1

42.0058,-106.5006 2

42.0061,-109.5675 1

42.0876,-110.4075 1

42.71,-108.6381 2

42.832,-105.4891 1

42.9006,-108.5885 1

43.0063,-108.3638 1

43.2133,-108.215 3

43.3008,-108.7033 1

43.3503,-107.5553 2

43.6815,-108.308 2

43.9136,-108.0556 1

44.008,-107.4188 2

44.0325,-108.9681 1

44.404,-108.1818 2

44.48,-106.159 1

44.65,-106.1171 1

44.7551,-108.1608 2

44.78,-108.5116 1

Grand Total 29

 

[MA_PE]

TD:

very nice. thanks for teaching an old dog a new trick. I'll bet I can find a lot of uses for Pivot Tables in the future.

Excel is not the right tool for this job.

I could be wrong. (It won't be the first time).

 

[frazil]

Brilliant! Nice work, TD!!

MA, your idea to write a program was a good one -- now I have a program to calculate the distance between lat/long points.

 

[TouchDown]

Aw, shucks, twernt nothin.

I've gotten pretty dependent upong pivot tables and pivot charts. They make pareto'ing very friendly with a lot of complex data, manipulating the chart for different values and timeframes becomes quite easy.

I'm very comfortable with XL, but I've been doing a lot of work in Access lately. Attempting to build a database right now, with some complex forms and VBA in the background, but it's a lot more difficult for me at least.

Anyone work with Access?

 

[MA_PE]

Anyone work with Access?

as little as possible. arty-smiley-048:

quite a few years ago I got fairly proficient with DBASE then MS had to come along and screw everything up. I know what the program can do and fundamentally how it wants to do it, but the physical programming step are all different. So now I have to have someone else in the office program it.

 

[chaosiscash]

Anyone work with Access?

I've done a fair amount of access work, but not for a couple of years. A couple years back, I did a database setup with an ASP web interface, and did a lot of VBA stuff, but like I said, its been a long time.