Solved

Excel Formula / data mining issue

Posted on 2011-03-23
8
211 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
stuck in average-1 12 65
How to change the format as number 18 30
Problem with a moving column in Excel 6 38
Testing for the presence of text in a range of cells 3 12
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

839 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