Solved

Excel Formula / data mining issue

Posted on 2011-03-23
8
209 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

943 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now