+ Reply to Thread
Results 1 to 14 of 14

Generate List of Matching Rows from multiple tabs

  1. #1
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Generate List of Matching Rows from multiple tabs

    I pull physical stats of network devices daily. Each time I pull data, it goes into a new Tab. All tabs are formatted the same, though there may be differing numbers of rows, and corresponding device data may be on a different row in each tab.

    Data is formatted by row, with an authentic MAC address in the A column, and different stats through the columns. A device will only appear once per tab, and may not be on all tabs

    Example of data format on each tab:
    MAC Address____XXXX__Pwr1_Pwr2__XXXX__Pwr3._Pwr 4
    1234.5678.0001....xxxx....52......26......xxxx......3.......34
    1234.5678.0002....xxxx....47......27......xxxx.....-10.....35


    My end goal is to generate a trend graph for a single device by simply entering its MAC address into a specific cell and maybe clicking a macro button. This would generate a table by searching each tab for a match of that MAC in the A column.

    A match would result in 5 cells from the matched row to be entered into the report table. On and on through each tab until I have a trend table.


    Something like the format below:

    Date/Time___Pwr 1_Pwr 2_ Pwr 3_Pwr 4
    02-10 0900___54____25_____0_____35
    02-10 1600___48____25_____-1____36
    02-11 0930___35____26_____-9____35
    02-13 1000___52____27_____7_____34
    02-13 1200___42____25_____10____37
    02-13 1900___49____25_____12____38


    Since the number of tabs changes daily, I dont want to be required to update a formula or macro each time I add a tab.

    I've worked a little with loops and lookups, but this is just a bit too complex for me to take all the smaller ideas and link them together. If I can get the table generated, I can handle the chart.

    Thanks for Any Help

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Generate List of Matching Rows from multiple tabs

    Hello xenocide8d,

    Welcome to the Forum!

    This macro can be attached to a button on the report worksheet. When the macro runs, it will prompt you for the MAC address. This can be changed to look for the address in a given cell later on. I wasn't sure whether the "XXXX" entries were the same or if one was the date and the other time. So, I used only the first instance in column "B" and disregarded the one in column "E". Again, this can be changed later. The starting cell for all worksheets is cell "A2". This assumes row 1 contains the column headers.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Generate List of Matching Rows from multiple tabs

    First of all, Thank you. Its a great start on this little task. The code works just like I want it to. I just need to change the cells it outputs (Which I didnt provide...).

    The sample chart I provided didnt have things in the right columns... it was more a visual sample. The 'XXXX' were just to show that the return data was not in consecutive columns.
    D,E,F,G,J are the cells in the matched row that I need returned.

    And yes, there is a header row.


    Thanks again, my boss already loves the data I pulled and organized. He'll flip when I show him a graph of it

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Generate List of Matching Rows from multiple tabs

    Hello xenocide8d,

    The variable Data holds the value for the MAC address in column "A". The Offset statements point to the cell that is to the right of "A" on the same row. Just make the change below to the macro...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Generate List of Matching Rows from multiple tabs

    OK, I got that in there. I removed the '.value' since I didnt need that there to generate a graph.

    What changes should I make so that I can have A1 be the cell where I enter the MAC? I tried replacing 'InputBox("Please enter the MAC address below.")' with just 'A1' but that didnt seem to cut it.

    Is it possible to have the report data start a few rows further down? Im planning to have the line graph start in Row B, and just hiding the cells where the report data goes. That way I'll just have an 'entry box', a 'generate graph' button, and the graph below.

    Thanks again for your help

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Generate List of Matching Rows from multiple tabs

    Hello xenocide8d,

    Which worksheet will the MAC address be located on? Which row in column "B" do want to start at?

  7. #7
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Generate List of Matching Rows from multiple tabs

    The Mac address will be entered in the "Report" tab. I'm thinking "A1" is where I enter the Mac, and "B1" is where I'll put the Macro Button.

    1 Row for the entry cell and button. 1 row for the graph. 2 rows in case i decide to add anything else. So the data from the report can start in row "E".

    I'm a little confused when you say "Which row in column "B" do want to start at?". Right now the data generates staring at "A2". I suppose it doesn't matter to me which column it starts in, I can point the graph to wherever it is.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Generate List of Matching Rows from multiple tabs

    Hello xenocide8d,

    Sorry I wasn't clear. I was referring to the Report worksheet when I mentioned column "B". Do you want to start at "E1" or "E2" then?.

  9. #9
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Generate List of Matching Rows from multiple tabs

    E2 is good

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Generate List of Matching Rows from multiple tabs

    Hello xenocide8d,

    I made the changes. The MAC address is now in "A1" on the Report worksheet and the data will be entered starting at "E2" on the Report worksheet.
    Please Login or Register  to view this content.

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Generate List of Matching Rows from multiple tabs

    Leith, your original macro had a "double check" for the mac address, probably a good idea to leave it in the new one, somehow, too. My 2 cents.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  12. #12
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Generate List of Matching Rows from multiple tabs

    That did it for me. Though my lack of sleep apparently caused me to mix up my Rows and Columns. I meant B5 rather than E2, but that was simple enough to correct. I imagine that confused things a bit. I need to stop looking at spreadsheets

    I can't thank you enough for your help. This is one I certainly wouldn't have come up with on my own.

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Generate List of Matching Rows from multiple tabs

    Hello xenocide8d,

    JB made a good point about the double check. I did not check if "A1" has valid data in it when the macro runs. I can add some code to be sure the value of "A1" is a valid MAC address, if you like.

  14. #14
    Registered User
    Join Date
    03-01-2010
    Location
    Colorado, US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Generate List of Matching Rows from multiple tabs

    Im not noticing any issues when I enter an incorrect mac. It just doesn't do anything.

    One question though. I notice when I enter another mac without clearing the report data, it adds to the end of it. Is there an easy way to have it clear the data from B5:F5 on down before it generates the data so I dont have to manually clear it?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1