Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Formula / data mining issue

Posted on 2011-03-23
8
Medium Priority
?
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

604 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