Macro to combine data based on IF,THENs?

[marlea]

I was going to export my data to FileMaker to try to do this with a series of IF statements and Concatenations, but for efficiency I'd 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), then it gets listed twice, the first listing should have the words "answer key", and the second listing should not (see rows 1 and 2 above).

If the page range is greater than one page (i.e., if 'page start' does *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' = 'page end'), then format pages this way: "p. 5".

Can anyone help me with a macro? Thank you!

[theDude]

This macro should work for you:

Please Login or Register  to view this content.

Hope this helps,
theDude

[marlea]

theDude-

Thank you so much!! This is fantastic! You've saved me a lot of work in FileMaker. And thanks for including all the comments--they're a great learning tool.

One little thing I noticed after running the macro is that rows with 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 able to remedy that...but in the meantime, if you're able to help (once again), that'd be great.

Thanks again!

This macro should work for you:

Hope this helps,
theDude

[marlea]

Yay, I think my change works:

' Concatenate data and populate Sheet2...
If ansKey = "" Then
newString = myTitle & ", " & pgRng
Else
newString = myTitle & ", " & pgRng & ", " & ansKey
End If