I hate Execel. All of it.

[deanwebb]

 

I have 375 wireless controllers and autonomous APs, and I need to see which ones are the biggest offenders in not providing me with IP address information for their connected clients. In my NAC solution, I can export a table that shows every device without an IP address and the IP of the offending WLC/AP.

Thank, you, NAC. You did your job. 

OK, Mr. Excel... your turn... of the 3929 rows, please tell me how many times an IP appears in that column so that I know which WLCs are the biggest offenders and I can get the biggest benefit in fixing first.



Apparently, getting Excel to do something as simple as COUNT how many times a value shows up in a column is a feature the programmers never thought was necessary to include or, if they did include that feature, it is in the most inaccessible place. I am out of my mind, trying to find something that works.

Worst was the "Quick analyis" tool. Running it one way told me that, yes, I have 3929 rows. Running it the other way told me that, yes, each value appears once per row.

[SimonV]

Try this, dean:

https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34

You might have missed it as it was the second hit on Google

[icecream-guy]

Sort and group by autonomous ap ip address, then use subtotals?  you should get a subtotal after every group (autonomous IP) and a total on the bottom

[deanwebb]

Actually, what the MSFT article failed to mention was that, in order to really make the COUNTIF work, is to make a second column of the data, remove duplicates ONLY in that column, and then use those as indexes, so i got

=COUNTIF(A2:A3929,B2)

in the first line (data has headers) and then do a CTRL+D all the way down the index values and it counts how many times that value shows up in the main list (column A).

Now I know which WLCs to take a whack at first. 172.31.170.132, it's ON, MOTHERTRUCKER!!! (IP address changed so as not to provide recon info on my network.)

[deanwebb]

PS: I called my dad, who's an Excel programmer, for the solution. Thanks, dad.

PPS: I'm still pissed off that there isn't just a "COUNT" button to click and then say how many damn times a damn value shows up in a damn column.

[Otanx]

Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.

sort wlc.csv | uniq -c

-Otanx

[deanwebb]

Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.

sort wlc.csv | uniq -c

-Otanx

Honestly, that would have taken less time than what I put into solving this problem.

[ggnfs000]

There is not much latest app, O/S, web pages that I dont hate. Apps crashing, O/S crashing, web pages unresponsive, everything fial.

[wintermute000]

Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.

sort wlc.csv | uniq -c

-Otanx

And you learn something new every day. I would have spent an hour writing a hacky python script LOL
for the interests of keeping my scripting chops up, do you mind sending the data through for me to have a whack at it via python? I presume there's noething sensitive in there?

[icecream-guy]

Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.

sort wlc.csv | uniq -c

-Otanx

And you learn something new every day. I would have spent an hour writing a hacky python script LOL
for the interests of keeping my scripting chops up, do you mind sending the data through for me to have a whack at it via python? I presume there's noething sensitive in there?

mmmm.. attempted hack via social engineering.

[deanwebb]

It's just all the IP addresses of our wireless controllers and autonomous APs... Sure, let me post it here in a public forum...

Gmail sent because it was too much for the PM.