# excel counting help



## frazil (May 17, 2007)

I have a long list (15000) of 2 numbers (column A and column B ). I've filtered the long list into a list of unique values (where column A AND column B are the same), which is in column C and D. Now I want to count the number of instances for each unique value. For example: how many times does C2 AND D2 occur in the same row in columns A and B?

A	B	C	D	Result

1	2	3	4 2

3	4

4	3

3	4

2	1

Does that make sense? There seem to be a million sorting/counting functions in excel, but I can't find one that works.


----------



## MA_PE (May 17, 2007)

frazil said:


> I have a long list (15000) of 2 numbers (column A and column :bio: . I've filtered the long list into a list of unique values (where column A AND column B are the same), which is in column C and D. Now I want to count the number of instances for each unique value. For example: how many times does C2 AND D2 occur in the same row in columns A and B?
> A	B	C	D	Result
> 
> 1	2	3	4 2
> ...


Fraz:

I don't follow your example of what you are counting.

"Now I want to count the number of instances for each unique value."

This is done with the histogram function in the Data Analysis add-on. This will take a column of values and return the number of occurances within user defined bins. IF values range from 1 to 10 then define these as you bins and the do a histogram of the complete data set.

"how many times does C2 AND D2 occur in the same row in columns A and B?"

if your looking for idential values in the same row, then

create a dummy column where you subtract the two values. Use an IF statement to assign a cell value of 1 if the statement is true (i.e. subtraction will equal zero) and and 0 if the vaule is False (subtraction is not equal "&lt;&gt;" to zero)

Then simply sum the column (add the 1's) for the total rows where col A and col B are the same.

Does this help?


----------



## Guest (May 17, 2007)

Dang .. we are going to have to re-name MA_PE Boolean Algebra Guy !!!

JR


----------



## MA_PE (May 17, 2007)

jregieng said:


> Dang .. we are going to have to re-name MA_PE Boolean Algebra Guy !!!
> 
> JR



IF(fraz_problem="solved", "shoe fits", "try again")

if the shoe fits I'll wear it!! :laugh:


----------



## frazil (May 17, 2007)

This is a tough one to explain, but maybe it would help if I tell you what I'm doing:

column A is latitude, column B is longitude. There are 15000 points, but some may be the same location (latitude AND longitude are the same). I've filtered the list so column C and column D is a list of only the unique locations. Now I want to count how many times each location appears in the original list.

I can't use a histogram because I have 2 columns rather than 1 list of bin ranges. I didn't mean column C is the same as column D -- I meant where column C = column A AND column D = column B.


----------



## MA_PE (May 17, 2007)

frazil said:


> This is a tough one to explain, but maybe it would help if I tell you what I'm doing:
> column A is latitude, column B is longitude. There are 15000 points, but some may be the same location (latitude AND longitude are the same). I've filtered the list so column C and column D is a list of only the unique locations. Now I want to count how many times each location appears in the original list.
> 
> I can't use a histogram because I have 2 columns rather than 1 list of bin ranges. I didn't mean column C is the same as column D -- I meant where column C = column A AND column D = column B.


How many unique locations?

Can you create a column for each?

where

new column checking for first unique location

=IF(AND(A1=$lat1,B1=$long1),1,0)

This will produce 1 if the condition is TRUE and 0 if it is FALSE

repeat for reference to lat2, long2. Using "$" to define absolutes you can copy cells pretty easily.

The only other way I would do it is to create a program (I'm old enough to use qbasic) which would read each unique location and cycle through the raw data and count occurances, write the total and grab the next unique loaction.

Hope this helps.

I'm sure visual basic will do it, just dont know how to program that.


----------



## Bigwolf (May 17, 2007)

frazil said:


> This is a tough one to explain, but maybe it would help if I tell you what I'm doing:
> column A is latitude, column B is longitude. There are 15000 points, but some may be the same location (latitude AND longitude are the same). I've filtered the list so column C and column D is a list of only the unique locations. Now I want to count how many times each location appears in the original list.
> 
> I can't use a histogram because I have 2 columns rather than 1 list of bin ranges. I didn't mean column C is the same as column D -- I meant where column C = column A AND column D = column B.



Frazil--

Are you creating some Points of Interest (POI's) for a GPS unit?


----------



## McEngr (May 17, 2007)

Frazil,

Create a new column respective for each recurring value that you are searching for. Here's how it goes...

=IF(C10="recurring value",1,0)

Then, at the end of your 15,000 numbers, use the SUM command to add them all up. This is the cumbersome way, but it's effective. If you want to add this while making your spreadsheet "pretty", do it in a hidden format (like with a WHITE font on random cell locations or on a separate sheet).

Hope this helps...


----------



## frazil (May 17, 2007)

Thanks for the replies. There are over 4600 unique locations, so I don't think I can make a column for each. I thought this would work

=sum(if((A2:A15000 = C2)*(B2:B15000 = D2)),1,0))

but it doesn't :laugh:


----------



## MA_PE (May 17, 2007)

Fraz:

As I said above, this is a fairly easy programming exercise if you or someone in your office know how to program.

I'd start with two text files of two columns of numbers:

File1 has 15,000 entries of x,y coordinate data (with repeats)

File2 has 4,600 entries of unique x,y data

Program says:

For i = 1 to 4600

count = 0

Open File2

read (X1)

read (Y1)

Open File1

for j = 1 to 15000

read (X2)

read (Y2)

IF X1=X2 AND Y1 = Y2

count = count +1

endif

next j

close file1

open summary for output

print to summary, X1, Y1, count

next i

close File2

End

See if someone can do this for you.

Good luck


----------



## jroyce (May 17, 2007)

you could try and IF/AND statement. Maybe something like this:

=IF(AND(C$2=A2,D$2=B2),1,0)

That would output a 1 if the condition is true and then you can count the 1s.

or you could write a macro (as MA_PE has suggested) if you are comfortable using basic.


----------



## frazil (May 17, 2007)

alright, fine, I'll write a program. I do quite a bit of programming for my work, I just thought this would be a simple thing in excel.

You guys helped me realize something else. Of those 4600 'unique' locations there are many that are actually the same place but someone wasn't as exact when they entered the coordinates. How do you calculate the distance between 2 points given lat/long? As long as I'm writing a program I might as well figure out which points are actually the same location given some threshold difference.


----------



## MA_PE (May 17, 2007)

frazil said:


> alright, fine, I'll write a program. I do quite a bit of programming for my work, I just thought this would be a simple thing in excel.
> You guys helped me realize something else. Of those 4600 'unique' locations there are many that are actually the same place but someone wasn't as exact when they entered the coordinates. How do you calculate the distance between 2 points given lat/long? As long as I'm writing a program I might as well figure out which points are actually the same location given some threshold difference.



lat/long distance calc and code

Ain't the internet AMAZING.

Fine don't write a program and shovel your own walk/driveway. What is this aversion that you have to labor-saving devices?

(I'm just giving you some guff, because I know you can take it.)

Have fun


----------



## frazil (May 17, 2007)

MA_PE said:


> lat/long distance calc and code
> Ain't the internet AMAZING.
> 
> Fine don't write a program and shovel your own walk/driveway. What is this aversion that you have to labor-saving devices?
> ...


LOL...that's a good question...probably a whole other thread! Thanks for the link!


----------



## grover (May 17, 2007)

Sort by lag/long.

Create 3 new columns, C, D and E, respectfully: =A2-a1, =B2-B1, IF(C2^2&lt;.01 AND D2^2&lt;.01, 0, 1) Where .01 would be the square of your degree of error to call them one location. (gotta either square the distance or add an extra boolean string, your call. boolean would probably be less processor intensive, but eh, it should work!)

Sum colum E, win! Or, autofilter for all 0s in column E, and batch delete them. This will leave you with a database consisting entirely of unique numbers.


----------



## frazil (May 17, 2007)

Bigwolf said:


> Frazil--
> Are you creating some Points of Interest (POI's) for a GPS unit?


No, these are points already entered in a database. I just want to know how many points are in each location. Probably 75% of the points occur only once, but some locations have several points.

I'm working on my program now -- I should have just done this in the first place.


----------



## MA_PE (May 18, 2007)

Fraz:

how'd you make out? Wanna e-mail me your data files and I'll do it? It's gotta be more fun that the c$%p I'm working on.


----------



## frazil (May 18, 2007)

THanks for the offer! My program worked like a charm -- very slick! :laugh: I've got some other stuff you can do though if you're looking for work! 

if anyone wants a little executable program or the source code, let me know.


----------



## MA_PE (May 18, 2007)

what did you write it in?


----------



## Guest (May 18, 2007)

frazil said:


> if anyone wants a little executable program or the source code, let me know.


The last time I tried to use that as a pick-up line, it .. um .. didn't produce the desired result. 

On a more serious note, EB'ers rock with MS Excel !! :th_rockon: :laugh:

JR


----------



## frazil (May 18, 2007)

visual basic (.NET)


----------



## DVINNY (May 18, 2007)

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 (May 18, 2007)

DVINNY said:


> 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 (May 18, 2007)

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 (May 18, 2007)

TouchDown said:


> I didn't read through all of them, but XL has a "COUNTIF(...) function.
> So,
> 
> COUTIF ("A2:B22", "D2")
> ...


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 (May 19, 2007)

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 (May 19, 2007)

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 (May 19, 2007)

VTEnviro said:


> 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 (May 19, 2007)

TouchDown said:


> I didn't read through all of them, but XL has a "COUNTIF(...) function.
> So,
> 
> COUTIF ("A2:B22", "D2")
> ...


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 (May 19, 2007)

frazil said:


> 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:
> 
> ...


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 (May 20, 2007)

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 (May 20, 2007)

> 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 (May 20, 2007)

VTEnviro said:


> 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 (May 20, 2007)

TouchDown said:


> 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 (May 21, 2007)

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 &amp; "," &amp; 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 (May 21, 2007)

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 (May 21, 2007)

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 (May 22, 2007)

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 (May 22, 2007)

> 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.


----------

