 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2004Cell extraction from Multiple worksheets
Hi group, I am new to this newsgroup and am hoping that you can help me out. I took a software/hardware inventory of all the computers in my work's network and imported them all into excel, in their own worksheet. So I have one file, with 128 different worksheets, all containing similar information. What I would like to do is create a new sheet, a summary page, where I can pull the value of the same cell from each sheet and list them. I need to have a list of each computer, with computer name, cpu speed, video card, serial number, ect. If the values that I am searching for are in the same cell in each worksheet, is there a way that I can extract it all automatically? The manual way of (i.e =sheetname!G6) takes way too long to do. What I am hoping for is to create a formula that when I drag it down the page, it lists the value from each worksheet. I hope that I am not confusing anyone. I also know that a database would be much better for this, but the way that the original data is organized, a database wouldnt work easily. Thanks in advance for any help you can provide! Andrew Hi Andrew, What you want to do is certainly possible but how to do it depends on how the 128 worksheets are named. If the worksheet names are consistent like Sheet1, Sheet2, Sheet3... etc, the summary sheet may be created with worksheet functions, otherwise you need VBA code. More info is also needed on how the data in the 128 sheets is organized. However, as I understand your question I would rethink the strategy unless there is a specific reason to have one worksheet for each computer. Instead of having a summary sheet for 128 other sheets, make a master (database) sheet with all the original data with columns for computer name, cpu speed, video card, serial number, etc. That way you have all the data in on place which makes the setup more robust. Then it's quite easy to create a report sheet to which you can extract any information about any computer from the Excel database. In any case, more details about the layout of the 128 sheets is required to give a more detailed answer. Regards Anders Silven > Hi group, > I am new to this newsgroup and am hoping that you can help me out. I took a [quoted text clipped - 18 lines] > > Andrew I thank you both for your thoughts and suggestions. My main problems stems from the source, the original data. I used a free network auditing tool called Aida32 (www.aida32.hu). During this phase, there was a script wrote to collect all the audit information from each computer, and save it as a .csv file. Each file saved the same information about the computer, but since all the computers are different, one line might be different than another, comparing file to file and line to line. I will paste in a small section of the csv below, and as you should see, the information isnt formatted properly to make database conversion easy. I have thought of many ways to not have to perform this manually, actually spending double or triple the time than what it would have been, had I just copied and pasted each cell. I will most likely abandon the automatic approach shortly, as the deadline is rapidly approaching and its been a few years since programming with VB. Here are a few lines from the csv files that Aida32 spits out: Page,Device,Group,ItemID,Item,Value Report,,,257,Version,AIDA32 v3.93 Report,,,258,Author,tamas.miklos@aida32.hu Report,,,259,Homepage,http://www.aida32.hu Report,,,260,Report Type,Command-line Summary,,Motherboard,518,Motherboard Name,Dell Computer Corporation Inspiron 8200 Summary,,Motherboard,519,Motherboard Chipset,Intel Brookdale i845MP Summary,,Motherboard,520,System Memory,256 MB (DDR SDRAM) Summary,,Motherboard,521,BIOS Type,Phoenix (10/28/02) ** As you can see, there are 6 data types at the top, but these files are generated for each computer, making a primary key hard to create and making one table nearly impossible in access. Atleast, that is what I think. Also, some of the files show different data on different lines based on machine configuration. Here is what I would like things to look like (incase you were wondering): Computer name,Username,CPU speed,Memory,Video Card,Sound Card,Windows Version, etc... Computer1,Drewski,2400,512,Geforce FX5200,SBLive 5.1,XP 5.1,etc... I understand if this task seems impossible, and I am sure that I wont get away with doing this all automatically. I just thought that if I had each .csv file as an individual worksheet in one huge workbook, that I could write a function that would go through each sheet (almost like an array of sheets), find what was at a specific cell, and then show it on a summary page. This wouldnt work for all the fields as they are sometimes different, but I thought it might take 50%-75% of the manual copy and paste out of it. Thanks for your help! Andrew I don't see a key value for each field you want to bring back. If you had a value that was always in the same relative location (maybe in the cell directly to the left), you could use Find (just like Edit|Find in a worksheet), and pick off that adjacent value. But you'd need some consistent key. It doesn't have to be in the same row, but it does have to be in the same relative position. > I thank you both for your thoughts and suggestions. My main problems stems > from the source, the original data. I used a free network auditing tool [quoted text clipped - 43 lines] > > Andrew  SignatureDave Peterson ec35720@msn.com > I don't see a key value for each field you want to bring back. Huh? ItemID is consistently the 4th column for csv format: Page,Device,Group,ItemID,Item,Value Report,,,257,Version,AIDA32 v3.93 Report,,,258,Author,tamas.miklos@aida32.hu Report,,,259,Homepage,http://www.aida32.hu Report,,,260,Report Type,Command-line Summary,,Motherboard,518,Motherboard Name,Dell Computer Corporation Inspiron 8200 Summary,,Motherboard,519,Motherboard Chipset,Intel Brookdale i845MP Summary,,Motherboard,520,System Memory,256 MB (DDR SDRAM) Summary,,Motherboard,521,BIOS Type,Phoenix (10/28/02) -- He said it wasn't consistent. The 6th field might be it for the data posted--but it ain't my data--so maybe it was only that way in that snippet. > > I don't see a key value for each field you want to bring back. > [quoted text clipped - 12 lines] > > -- SignatureDave Peterson ec35720@msn.com Greetings, Have you considered combining all the reports into a single file then using Excel to import the resultant file? In DOS you can combine the contents of several files into one file using something like: TYPE Filename.* >> Masterfile.txt. Don R > I thank you both for your thoughts and suggestions. My main problems stems > from the source, the original data. I used a free network auditing tool [quoted text clipped - 43 lines] > > Andrew Hi again, Drew, Assuming the output from Aida32 is (pretty) consistent, it should be possible to create the summary more or less automatically, but I need to see more details. Is it possible that you mail me the workbook? I understand if you can't send it if the information is confidential, but I just love to wrestle with data. If it's OK, send it to temp1blaha@silvenblaha.se (remove blaha twice) Best regards, Anders Silven I like Anders suggestion about keeping the data on one sheet. Maybe you could use a macro like this to start that process: Option Explicit Sub testme01() Dim wks As Worksheet Dim rptWks As Worksheet Dim oRow As Long Set rptWks = Worksheets.Add(before:=Worksheets(1)) rptWks.Range("a1").Resize(1, 4).Value _ = Array("Worksheet Name", "Val1", "Val2", "Val3") oRow = 1 For Each wks In ActiveWorkbook.Worksheets With wks If .Name = rptWks.Name Then 'do nothing Else oRow = oRow + 1 rptWks.Cells(oRow, 1).Value = "'" & .Name rptWks.Cells(oRow, 2).Value = .Range("a1").Value rptWks.Cells(oRow, 3).Value = .Range("c3").Value rptWks.Cells(oRow, 4).Value = .Range("d9").Value End If End With Next wks rptWks.UsedRange.Columns.AutoFit End Sub > Hi group, > I am new to this newsgroup and am hoping that you can help me out. I took a [quoted text clipped - 18 lines] > > Andrew SignatureDave Peterson ec35720@msn.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.