excel counting help

[frazil]

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]

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

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.

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

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

JR

 

[MA_PE]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

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

 

[grover]

Sort by lag/long.

Create 3 new columns, C, D and E, respectfully: =A2-a1, =B2-B1, IF(C2^2<.01 AND D2^2<.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]

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]

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]

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]

what did you write it in?

 

[Guest]

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