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

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

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

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