Solved

Excel Formula / data mining issue

Posted on 2011-03-23
8
210 Views
Last Modified: 2012-05-11
Hi Guys,

Here's the situation. I pull data off a switch twice a day, i need to figure out which ports are dynamically assigned to which vlans on a regular basis.

so my data is port number: which is 1/1 format and vlan: 1505 or 1501 etc..

So if it is in an excel sheet it will look like below, I need to match columns B/D/F ....etc,, if they match then i need to write the vlan info from C/E/G into column A...

Here's the catch, In column A i want the most reoccurring value from C/E/G  and to add to the problems sometimes the columns dont match up... so if it could be some kind of search function it would be fantastic, otherwise i have to manually go through and line them up. like in the bottom example.

   A      B         C                           D       E                                      F               G
1      1/1      1501 (green)            1/1      1501 (green)
2      1/3      1501 (green)            1/3      1501 (green)
3      1/5      1501 (green)            1/5      1501 (green)
4      1/6      1501 (green)            1/6      1501 (green)


If the rows dont match for the port numbers..........

  A      B         C                           D       E                                      F               G
1      1/1      1501 (green)            1/1      1501 (green)
2      1/3      1501 (green)            1/3      1501 (green)
3      1/5      1505 (green)            1/4      1504 (green)
4      1/4      1504 (green)            1/5      1505 (green)
0
Comment
Question by:-P-Henderson
  • 4
  • 4
8 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35197866
Dear P,

please try the macro in this file...


Kind regards
datamining.xls
0
 

Author Comment

by:-P-Henderson
ID: 35198074
Hi

I appear to be getting the same output in all of column A... regardless of the C/E column data

0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35198121
I tried it, you can see my data, if it fiends something appearing more times it will show this, in row A I tried it with 3 times 1502 and it showed 1502?

Was this not what you had in mind?
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:-P-Henderson
ID: 35198562
Not sure if i'm explaining it to well, or if indeed it can be accomplished with Excel...

I have attached a copy of what i'm doing,,,, so on every day 22nd 23rd 24th etc....i will paste in the details taken from the switch "port number and Vlan"

i want to some how verify the port numbers match the previous day or the first day or even just search for the port number and then write the vlan result somewhere... maybe there is a better way of doing it.. like writing the results to a seperate sheet or something.

the ultimate goal is to get a list of port numbers with the vlan most used on it over a period of a week.

Thanks,


NSNA-Master.xls
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 500 total points
ID: 35198779
Dear P,

I did see where it went wrong, so I changed a bit the macro and now it should be working fine.
You did not say however if it finds just one item if it should kist that item also, it now does...
datamining.xls
0
 

Author Comment

by:-P-Henderson
ID: 35220876
Thats working great, how do i get it to keep calculating futher down the rows. At the minute it stops at Row 76

Thanks,
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35229440
Dear P,

Strange, it should (and if I try it it does) keep on going until 65xxx.. or until it sees no more entry's
The macro checks for the entry's by looking in  column B with following statement:   While Cells(i, 2) > ""I Marked this statement with the text loop thru rows
So if you have a empty cell B76 or B77 it stops just there..


Kind regards

Eric
0
 

Author Closing Comment

by:-P-Henderson
ID: 35321034
Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question