Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract and summarize data
All,
I have a large number of worksheets. Each worksheet is named after a date. For example 'DATE 02.05.09' What I would like to do is for all the dates (Worksheets) in the workbook extract the relevant data from the worksheet and place it into a summary sheet. The specific data is defined by the summary sheet. For example if the code EAS is entered, it would search for all the EAS's in the date work sheets and return the individual values into the summary sheet. As shown below: The summary sheet would look like this. DATE CODE VALUE 01.02.08 EAS 44 02.02.08 EAS 22 02.05.09 LIN 44 If anyone could give me some help it would be much appreciated. Regards Joseph Crabtree |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract and summarize data
This will list all your sheets in you workbook:
Sub ListSheets() 'list of sheet names starting at A1 Dim rng As Range Dim i As Integer Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub HTH, Ryan--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract and summarize data
On Mar 24, 7:57*am, joecrabtree wrote:
All, I have a large number of worksheets. Each worksheet is named after a date. For example 'DATE 02.05.09' What I would like to do is for all the dates (Worksheets) in the workbook extract the relevant data from the worksheet and place it into a summary sheet. The specific data is defined by the summary sheet. For example if the code EAS is entered, it would search for all the EAS's in the date work sheets and return the individual values into the summary sheet. As shown below: The summary sheet would look like this. DATE * * *CODE * VALUE 01.02.08 *EAS * * *44 02.02.08 *EAS * * *22 02.05.09 *LIN * * * *44 If anyone could give me some help it would be much appreciated. Regards Joseph Crabtree Joseph, I threw this code together quickly, so you'll need to test it to make sure it is doing what you want. I don't know how you plan on telling the program what codes (i.e. "EAS", "LIN", etc.) to look for, so I created a simple Array in the program. The program assumes that the value for each code is one column to the right of the code. The program also assumes you are searching within the UsedRange (i.e. all cells that have been used at some point in time by the worksheet) to find the codes. Lastly, it assumes your summary data on the Summary worksheet begins in A1. I hope this gets you enough code to manipulate it to fit your needs. Best, Matt Herbert Sub CustomFind() Dim rngData As Range Dim rngItem As Range Dim rngFound As Range Dim rngFirstFound As Range Dim rngLastCell As Range Dim rngListFound As Range Dim wksSumm As Worksheet Dim lngOutCnt As Long Dim Wks As Worksheet Dim strWksDate As String Dim varMyArray As Variant Dim lngJ As Long 'code to look for varMyArray = Array("EAS", "LIN") Set wksSumm = Worksheets("Summary") 'loop through each worksheet For Each Wks In ActiveWorkbook.Worksheets 'skip the worksheet if it is the Summary worksheet If Wks.Name < wksSumm.Name Then 'reset the Found ranges for each worksheet Set rngFirstFound = Nothing Set rngListFound = Nothing 'get the date from the worksheet name strWksDate = Right(Wks.Name, Len(Wks.Name) - _ InStr(1, Wks.Name, " ", vbTextCompare)) 'set the range to look in for each worksheet Set rngData = Wks.UsedRange 'get the last cell of the range Set rngLastCell = rngData.Cells(rngData.Cells.Count) 'loop through the codes to find all occurances For lngJ = LBound(varMyArray) To UBound(varMyArray) 'see "Remarks" in Find Method documentation Set rngFound = rngData.Find(What:=varMyArray(lngJ), _ After:=rngLastCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows) If Not rngFound Is Nothing Then 'rngFirstFound acts as a "marker" to identify when ' we have looped through all possible finds, i.e. ' we are back at the beginning again Set rngFirstFound = rngFound 'if there is only one item that is found then the ' one item is the result of the find Set rngListFound = rngFound 'get the next find; this may or may not exist Set rngFound = rngData.FindNext(After:=rngFound) 'loop for all possible finds Do 'this is to catch if there is one item found as ' well as to determine if we are at the beginning ' "marker" of our find list If rngFound.Address = rngFirstFound.Address Then Exit Do End If 'this is to add the multiple found ranges into ' the rngListFound; union appends the new found ' item range to the existing found item range Set rngListFound = Application.Union(rngListFound, rngFound) 'since we are in a loop, we need to set the ' rngFound to the next find; this may or may ' not exist Set rngFound = rngData.FindNext(After:=rngFound) Loop 'output results to the Summary worksheet For Each rngItem In rngListFound With wksSumm lngOutCnt = .Range ("a1").CurrentRegion.Rows.Count .Cells(lngOutCnt + 1, "A").Value = varMyArray (lngJ) .Cells(lngOutCnt + 1, "B").Value = strWksDate .Cells(lngOutCnt + 1, "C").Value = rngItem.Offset(0, 1).Value End With Next End If Next End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract and summarize data
You post isn't as clear as it needs to be. First, assuming your
summary sheet has data like 01.02.08 EAS 02.02.08 EAS 02.05.09 LIN Should the search routine look only in the worksheet named in column A as it loops down the columns on the summary sheet? That is, should it search only sheet 01.02.08 for "EAS", then search only sheet 02.02.08 for EAS and then search only 02.05.09 for LIN? Or should every worksheet be searched for every code? Next, it is a relatively simple task to search the appropriate worksheet for the text "EAS", but what does "return the individual values into the summary sheet" mean? Suppose the code found "EAS" in cell A3 on one of the date sheets. How then does it find the associated value? In your example, you show the value 44 as related to EAS on the 01.02.08 sheet. Where does the 44 come from? And if there are multiple occurrences of EAS on one worksheet, do you want to return all occurrences to the cells on the summary sheet in the row that specified the date sheet name and code value? Are the codes (EAS, etc) always in a particular range or location on the date worksheets? For example, can the search routine restrict itself to a single column and/or a range of rows? Or does the entire worksheet need to be searched. Finally, are you worksheets named "DATE 02.05.09" or just "02.05.09"? Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 24 Mar 2009 05:57:53 -0700 (PDT), joecrabtree wrote: All, I have a large number of worksheets. Each worksheet is named after a date. For example 'DATE 02.05.09' What I would like to do is for all the dates (Worksheets) in the workbook extract the relevant data from the worksheet and place it into a summary sheet. The specific data is defined by the summary sheet. For example if the code EAS is entered, it would search for all the EAS's in the date work sheets and return the individual values into the summary sheet. As shown below: The summary sheet would look like this. DATE CODE VALUE 01.02.08 EAS 44 02.02.08 EAS 22 02.05.09 LIN 44 If anyone could give me some help it would be much appreciated. Regards Joseph Crabtree |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract and summarize data
On Mar 24, 4:10*pm, wrote:
On Mar 24, 7:57*am, joecrabtree wrote: All, I have a large number of worksheets. Each worksheet is named after a date. For example 'DATE 02.05.09' What I would like to do is for all the dates (Worksheets) in the workbook extract the relevant data from the worksheet and place it into a summary sheet. The specific data is defined by the summary sheet. For example if the code EAS is entered, it would search for all the EAS's in the date work sheets and return the individual values into the summary sheet. As shown below: The summary sheet would look like this. DATE * * *CODE * VALUE 01.02.08 *EAS * * *44 02.02.08 *EAS * * *22 02.05.09 *LIN * * * *44 If anyone could give me some help it would be much appreciated. Regards Joseph Crabtree Joseph, I threw this code together quickly, so you'll need to test it to make sure it is doing what you want. *I don't know how you plan on telling the program what codes (i.e. "EAS", "LIN", etc.) to look for, so I created a simple Array in the program. *The program assumes that the value for each code is one column to the right of the code. *The program also assumes you are searching within the UsedRange (i.e. all cells that have been used at some point in time by the worksheet) to find the codes. *Lastly, it assumes your summary data on the Summary worksheet begins in A1. I hope this gets you enough code to manipulate it to fit your needs. Best, Matt Herbert Sub CustomFind() Dim rngData As Range Dim rngItem As Range Dim rngFound As Range Dim rngFirstFound As Range Dim rngLastCell As Range Dim rngListFound As Range Dim wksSumm As Worksheet Dim lngOutCnt As Long Dim Wks As Worksheet Dim strWksDate As String Dim varMyArray As Variant Dim lngJ As Long 'code to look for varMyArray = Array("EAS", "LIN") Set wksSumm = Worksheets("Summary") 'loop through each worksheet For Each Wks In ActiveWorkbook.Worksheets * * 'skip the worksheet if it is the Summary worksheet * * If Wks.Name < wksSumm.Name Then * * * * 'reset the Found ranges for each worksheet * * * * Set rngFirstFound = Nothing * * * * Set rngListFound = Nothing * * * * 'get the date from the worksheet name * * * * strWksDate = Right(Wks.Name, Len(Wks.Name) - _ * * * * * * * * * * InStr(1, Wks.Name, " ", vbTextCompare)) * * * * 'set the range to look in for each worksheet * * * * Set rngData = Wks.UsedRange * * * * 'get the last cell of the range * * * * Set rngLastCell = rngData.Cells(rngData.Cells.Count) * * * * 'loop through the codes to find all occurances * * * * For lngJ = LBound(varMyArray) To UBound(varMyArray) * * * * * * 'see "Remarks" in Find Method documentation * * * * * * Set rngFound = rngData.Find(What:=varMyArray(lngJ), _ * * * * * * * * * * * * * * * * * * * * After:=rngLastCell, _ * * * * * * * * * * * * * * * * * * * * LookIn:=xlValues, _ * * * * * * * * * * * * * * * * * * * * LookAt:=xlPart, _ * * * * * * * * * * * * * * * * * * * * SearchOrder:=xlByRows) * * * * * * If Not rngFound Is Nothing Then * * * * * * * * 'rngFirstFound acts as a "marker" to identify when * * * * * * * * ' we have looped through all possible finds, i.e. * * * * * * * * ' we are back at the beginning again * * * * * * * * Set rngFirstFound = rngFound * * * * * * * * 'if there is only one item that is found then the * * * * * * * * ' one item is the result of the find * * * * * * * * Set rngListFound = rngFound * * * * * * * * 'get the next find; this may or may not exist * * * * * * * * Set rngFound = rngData.FindNext(After:=rngFound) * * * * * * * * 'loop for all possible finds * * * * * * * * Do * * * * * * * * * * 'this is to catch if there is one item found as * * * * * * * * * * ' well as to determine if we are at the beginning * * * * * * * * * * ' "marker" of our find list * * * * * * * * * * If rngFound.Address = rngFirstFound.Address Then * * * * * * * * * * * * Exit Do * * * * * * * * * * End If * * * * * * * * * * 'this is to add the multiple found ranges into * * * * * * * * * * ' the rngListFound; union appends the new found * * * * * * * * * * ' item range to the existing found item range * * * * * * * * * * Set rngListFound = Application.Union(rngListFound, rngFound) * * * * * * * * * * 'since we are in a loop, we need to set the * * * * * * * * * * ' rngFound to the next find; this may or may * * * * * * * * * * ' not exist * * * * * * * * * * Set rngFound = rngData.FindNext(After:=rngFound) * * * * * * * * Loop * * * * * * * * 'output results to the Summary worksheet * * * * * * * * For Each rngItem In rngListFound * * * * * * * * * * With wksSumm * * * * * * * * * * * * lngOutCnt = .Range ("a1").CurrentRegion.Rows.Count * * * * * * * * * * * * .Cells(lngOutCnt + 1, "A").Value = varMyArray (lngJ) * * * * * * * * * * * * .Cells(lngOutCnt + 1, "B").Value = strWksDate * * * * * * * * * * * * .Cells(lngOutCnt + 1, "C").Value = rngItem.Offset(0, 1).Value * * * * * * * * * * End With * * * * * * * * Next * * * * * * End If * * * * Next * * End If Next End Sub Thanks for all your help on this. This works great. Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Date, and summarize data request | Excel Programming | |||
Looking for a way to summarize data? | Excel Worksheet Functions | |||
Summarize Data | Excel Discussion (Misc queries) | |||
Summarize data | New Users to Excel | |||
Summarize Data Set | Excel Worksheet Functions |