Solved

Excel Formula / data mining issue

Posted on 2011-03-23
8
212 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

726 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