 |
 | 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 / Programming / January 2006Need to select/report all rows based on content
My workbook has worksheets for each year. In each worksheets is a list of papers published in our department. The columns of each worksheet represent Authors, Article Title, Citation information. On a new sheet (I guess, because I need to be able to do it to any of the sheets without messing them up), I need to generate a list of all of the publications for the department by year (sheet) for each author. For example, I have the list of publications from 2005, but it lists each paper only once, whereas I need to generate a list by author that will (therefore) duplicate the papers. Sorting only gets me part of the way because it provides all of the papers by each first author, but doesn't consider any other authors. I'll provide the illustrations below. Let me know if I can provide any additional information. What I have: Column A Column B Column C Schmidt R, Klimo Jr P Cerebral vasospasm ... In Cerebral Vasospasm pp... Gottfried ON, Smith WT Review of ... N Engl J Med 351:1493-... Gottfried ON, Binning ML Surgical approaches... Contemp. Neurosurg. 27:1-8 What I need to calculate out of that: Column A Column B Column C SCHMIDT R Schmidt R, Klimo Jr P Cerebral vasospasm ... In Cerebral Vasospasm pp... KLIMO JR P Schmidt R, Klimo Jr P Cerebral vasospasm ... In Cerebral Vasospasm pp... GOTTFRIED ON Gottfried ON, Smith WT Review of ... N Engl J Med 351:1493-... Gottfried ON, Binning ML Surgical approaches... Contemp. Neurosurg. 27:1-8 SMITH WT Gottfried ON, Smith WT Review of ... N Engl J Med 351:1493-... BINNING ML Gottfried ON, Binning ML Surgical approaches... Contemp. Neurosurg. 27:1-8 Kristin, Are the authors always on, and only on, the first line of an entry? And are they always delimited by a comma? > My workbook has worksheets for each year. > In each worksheets is a list of papers published in our department. The [quoted text clipped - 38 lines] > Gottfried ON, Binning ML Surgical approaches... Contemp. > Neurosurg. 27:1-8 HTH Sub Extract() Dim lastrow As Long, Authors As Variant Dim ws1 As Worksheet, ws2 As Worksheet Dim lastrow As Long, r As Long, nextr As Long, nrow As Long Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") orow = 2 With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row ws1.Cells(1, 1).Resize(1, 3).Copy ws2.Cells(1, 1) r = 2 Do nextr = .Cells(r, 3).End(xlDown).Row If nextr > lastrow Then nrow = lastrow - r + 1 Else nrow = nextr - r End If ' Get authors Authors = Split(.Cells(r, 1), ",") For j = LBound(Authors) To UBound(Authors) ws1.Cells(r, 1).Resize(nrow, 3).Copy ws2.Cells(orow, 1) ws2.Cells(orow, 1) = Trim(Authors(j)) ' replace with author orow = orow + nrow Next j r = r + 2 Loop Until r >= lastrow End With End Sub > My workbook has worksheets for each year. > In each worksheets is a list of papers published in our department. The [quoted text clipped - 38 lines] > Gottfried ON, Binning ML Surgical approaches... Contemp. > Neurosurg. 27:1-8 I appreciate your help. I just put this in as a macro. Is that correct? This results in an error "Duplicate declaration in current scope." for this part: lastrow As Long. The authors are always in the first column and always separated by commas. Thanks, Kristin > HTH > [quoted text clipped - 77 lines] > > Gottfried ON, Binning ML Surgical approaches... Contemp. > > Neurosurg. 27:1-8 Kristin, Yes, add as macro and remove one of the "DIM lastrow as Long" statements. > I appreciate your help. I just put this in as a macro. Is that correct? > This results in an error "Duplicate declaration in current scope." for this [quoted text clipped - 86 lines] > > > Gottfried ON, Binning ML Surgical approaches... Contemp. > > > Neurosurg. 27:1-8 I tried removing the second "DIM..." and then replaced that and tried removing the first, but each time I got "Compile error: Statement invalid outside type block" for the text that immediately follows what I removed. I greatly appreciate your help. Kristin > Kristin, > Yes, add as macro and remove one of the "DIM lastrow as [quoted text clipped - 90 lines] > > > > Gottfried ON, Binning ML Surgical approaches... Contemp. > > > > Neurosurg. 27:1-8 Reposting code: worksOK for me Sub Extract() Dim Authors As Variant Dim ws1 As Worksheet, ws2 As Worksheet Dim lastrow As Long, r As Long, nextr As Long, nrow As Long Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") orow = 2 With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row ws1.Cells(1, 1).Resize(1, 3).Copy ws2.Cells(1, 1) r = 2 Do nextr = .Cells(r, 3).End(xlDown).Row If nextr > lastrow Then nrow = lastrow - r + 1 Else nrow = nextr - r End If ' Get authors Authors = Split(.Cells(r, 1), ",") For j = LBound(Authors) To UBound(Authors) ws1.Cells(r, 1).Resize(nrow, 3).Copy ws2.Cells(orow, 1) ws2.Cells(orow, 1) = Trim(Authors(j)) ' replace with author orow = orow + nrow Next j r = r + 2 Loop Until r >= lastrow End With End Sub > HTH > [quoted text clipped - 77 lines] > > Gottfried ON, Binning ML Surgical approaches... Contemp. > > Neurosurg. 27:1-8 |
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
|
©2008 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.