Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine data based on IF,THENs?
I was going to export my data to FileMaker to try to do this with series of IF statements and Concatenations, but for efficiency I' really like to use a VBA macro. Current data set, Sheet1: A (title)/B (page start)/C (page end)/D (answer key) cats/1/2/answers dogs/3/3/ bears/4/6/answers frogs/7/7/answers I want to combine the contents of an entire row into one cell. Ideally after running a macro, the above data would like this on Sheet2: 1 cats, pp. 1-2, answer key 2 cats, pp. 1-2 3 dogs, p. 3 4 bears, pp. 4-6, answer key 5 bears, pp. 4-6 6 frogs, p. 7, answer key 7 frogs, p. 7 Explanation: If a title has an answer key (i.e., if column D is *not* blank), the it gets listed twice, the first listing should have with the word "answer key", and the second listing should not (see rows 1 and above). If the page range is greater than one page (i.e., if 'page start' doe *not* equal 'page end'), then format pages like this: "pp. 3-4"; but if the page range is only one page (i.e., 'page start' = 'pag end'), then format pages this way: "p. 5". Can anyone help me with a macro? Thank you -- marle ----------------------------------------------------------------------- marlea's Profile: http://www.excelforum.com/member.php...fo&userid=2620 View this thread: http://www.excelforum.com/showthread.php?threadid=46715 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine data based on IF,THENs?
This macro should work for you: Code ------------------- Sub createList() ' Declare variables... Dim pgStart, pgEnd, newRow, counter As Integer Dim myTitle, ansKey, pgRng, newString, string2 As String ' Select Sheet1 first cell w/data (A2)... ThisWorkbook.Sheets(1).Activate ActiveSheet.Range("A2").Select ' Initialize counter counter = 0 ' Loop through all rows of data and populate Sheet2... Do Until IsEmpty(ActiveCell) ' Count data rows to process counter = counter + 1 ' Get title... myTitle = ActiveCell.Value ' Get page start... pgStart = ActiveCell.Offset(0, 1).Value ' Get page end... pgEnd = ActiveCell.Offset(0, 2).Value ' Get answer key... ansKey = ActiveCell.Offset(0, 3).Value ' Determine page range and format accordingly... If pgEnd pgStart Then pgRng = "pp. " & pgStart & "-" & pgEnd Else pgRng = "p. " & pgStart End If ' Concatenate data and populate Sheet2... newString = myTitle & ", " & pgRng & ", " & ansKey 'Determine first empty row in Sheet2 to populate If counter = 1 Then newRow = ThisWorkbook.Sheets(2).UsedRange.Rows.Count Else newRow = ThisWorkbook.Sheets(2).UsedRange.Rows.Count + 1 End If ' Populate Sheet2 accordingly (2 records if answer key present)... If ansKey < "" Then newString = myTitle & ", " & pgRng & ", " & "answer key" string2 = myTitle & ", " & pgRng ThisWorkbook.Sheets(2).Cells(newRow, 1).Value = newString ThisWorkbook.Sheets(2).Cells(newRow + 1, 1).Value = string2 Else ThisWorkbook.Sheets(2).Cells(newRow, 1).Value = newString End If ' Go to next row of data... ActiveCell.Offset(1, 0).Select Loop ' Switch to Sheet 2 to show results... ThisWorkbook.Sheets(2).Activate ActiveSheet.Range("A1").Select End Su ------------------- Hope this helps, theDud -- theDud ----------------------------------------------------------------------- theDude's Profile: http://www.excelforum.com/member.php...fo&userid=1655 View this thread: http://www.excelforum.com/showthread.php?threadid=46715 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine data based on IF,THENs?
theDude- Thank you so much!! This is fantastic! You've saved me a lot of work i FileMaker. And thanks for including all the comments--they're a grea learning tool. One little thing I noticed after running the macro is that rows wit nothing in the "Answer Key" column get returned with an ending comma e.g.: apples, p. 1, If I study the code for a while (long while), I think I should be abl to remedy that...but in the meantime, if you're able to help (onc again), that'd be great. Thanks again! theDude Wrote: This macro should work for you: Hope this helps, theDud -- marle ----------------------------------------------------------------------- marlea's Profile: http://www.excelforum.com/member.php...fo&userid=2620 View this thread: http://www.excelforum.com/showthread.php?threadid=46715 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine data based on IF,THENs?
Yay, I think my change works: ' Concatenate data and populate Sheet2... If ansKey = "" Then newString = myTitle & ", " & pgRng Else newString = myTitle & ", " & pgRng & ", " & ansKey End I -- marle ----------------------------------------------------------------------- marlea's Profile: http://www.excelforum.com/member.php...fo&userid=2620 View this thread: http://www.excelforum.com/showthread.php?threadid=46715 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine data based on two fields.. | Excel Discussion (Misc queries) | |||
inserting a number from a particular cell based on if/thens | Excel Discussion (Misc queries) | |||
combine 2 tables of data into one based on date | Excel Worksheet Functions | |||
Have "IF,Then" to get data from multiple columns--can I nest add'l IF,THENs? | Excel Worksheet Functions | |||
Macro to combine data in a column | Excel Programming |