Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to combine data based on two fields.. gambler1650 Excel Discussion (Misc queries) 1 May 4th 10 08:11 PM
inserting a number from a particular cell based on if/thens Peter Hansen Excel Discussion (Misc queries) 1 May 26th 08 06:40 AM
combine 2 tables of data into one based on date Tim Nealon Excel Worksheet Functions 5 September 1st 06 01:42 AM
Have "IF,Then" to get data from multiple columns--can I nest add'l IF,THENs? marlea Excel Worksheet Functions 1 August 31st 05 07:45 PM
Macro to combine data in a column Janna Excel Programming 2 February 10th 05 06:51 AM


All times are GMT +1. The time now is 03:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"