Appending data from one worksheet to another 1

[Lynne41]

Hi

I have a spreadsheet with 3 worksheets. I am trying to get data over from one sheet to another if it meets a certain criteria (ie a grading of 2 or 3).

How can I get the data over so it appends below the previous one and therefore eliminates blank lines?

The data is as follows:

Grade Comment
2 This is a test
4 Another test
3 Yet another test

The data I want on the other sheet is the Comments. I have done an "if" to select the relevant comments if the grade is 2 or 3 and gone down each row of the spreadsheet. However I get the #Name error on the blank rows where the criteria is not met.

I think this may need a loop statement which says something like "if [sheet1]A! = 2 then A! [comments] then goto next row and repeat etc.

Or it may need an append statement so each true comment places itself on the other worksheet below the previous one.

Trouble is I have no idea how to write the code that will do this!

I would be grateful for your help so I can record the macro to run automatically.

Thanks
Lynne

 

[ETID]

Is this a lookup table in sheet1?

That is to say, only 1 instance of every grade & comment combination?

 

[Lynne41]

Hi ETID

Thanks for coming back.

It may be 1 instance of the grade & comment combination but it is a free text field so I guess the potential is there to use the same comments eg "As before" or something!

Hope this helps

Lynne

 

[ETID]

I'm thinking the vlookup function is what you need

if your list is on sheet1 in cols A & B and your working in sheet 2 then

where A contains the number an B contains the comment

then...

on sheet 2

in col B ....



=vlookup(a2,sheet1!$a$2:$b$4,2,false)

where A2 contains the number value to "lookup"

fill down

 

[Lynne41]

Hi ETID

Thanks for this. However it is now returning all comments regardless of what the number is on sheet1. Where is "A2" that you referred to? Do I need to create a number table in A2 down to Awhatever on sheet2?

Thanks
Lynne

 

[ETID]

A2 (which I understand is the grade for which you want to return a comment) is on the new sheet,..or in my example sheet2 and this formula is in b2 on the new sheet.

 

[Lynne41]

ETID

Thanks for this. Unfortunately it is only returning the first instance of the grade and not any repeat grades further down the spreadsheet.

Lynne

 

[ETID]

So you can have more than 1 instance of each grade,...each having a unique comment?

 

[Lynne41]

Hi ETID

Yes that is correct. More than one instance of grade then a unique comment.

I'm also looking to eliminate the blank lines in between the grades as the grades are not sorted in order so another instance may fall half way down the spreadsheet etc.

 

[ETID]

Is the end result that you are looking for, a list of comments by grade?

 

[Lynne41]

ETID

Correct. The end result is a list of comments (one after the other if poss) that match grades 1 or 2 (Strengths list. This list doesn't need to be in grade order - just one after the other within the 1 or 2 category.

I will also be doing a second list for grades 5,6, and 7 (Priorities list) but I want to crack the first one before starting on this!

Lynne

 

[ETID]

Ah,...Look to the pivot table tool....


select the first cell in the first col of your grade/comments sheet

then,...

data>pivot table and charts wizard...

answer the questions in the wizard...

when you get to the screen with the "layout button" in the left lower corner, select it and drag both of your column headers into the "row" box (with grade being the first)and any one of the headers into the "data" box (it will porb. default to sum of header),...since you wont be using these sums or counts you can ignore or change the summary type if you wish)

the select finish and tell the wizard to creat a new sheet

you can then dbl click the header buttons on the pivot report to customize the view.....play with it (it's a very versatle tool, if you get in too deep, just kill the sheet and recreate a new one.

 

[Lynne41]

ETID

This kind of works but you can't put the calculated field (to select only those comments that belong in the 1 or 2 grade catogory) in until you have created the pivot. It also won't let me select only those grades with 1 or 2 as it selects all grades but just puts them into order of grade.

This spreadsheet is going to 230 schools so it has to be automated - ideally, as they are typing in the grades/comments on sheet1 it automatically extracts the date for grades 1 or 2 into sheet2!

Lynne

Lynne

This spreadsheet is going out to 230 schools so everything has to be automated

 

[ETID]

If you right click on the header "button" for grade in the pivot...you can filter out (hide) any grades that you don't need.


...but if you give me some time,..I think I can put together a macro to help with this.

 

[Lynne41]

ETID

Cool - that would be great! I am currently playing round with the Query facility as this seems to work! Haven't tried to get it into a macro yet though!

 

[ETID]

Ok...IF sheet1 has 2 cols (Grade and Comment)
AND sheet2 has 1 col (Grade)...WITH HEADERS!

then on the sheet2 name tab right click and select view code,..
select "worksheet" then "activate" from the drop downs on the right.

you should see this statement in the lower window

Private Sub Worksheet_Activate()

end sub

copy the code below and paste it over top of this statement
the end result should be this........
Note: alt+F11 will get you back to the workbook...then when you active sheet2 it will run


Private Sub Worksheet_Activate()

commentrows = Sheets("sheet1&quot.UsedRange.Rows.Count
commentcols = Sheets("sheet1&quot.UsedRange.Columns.Count
graderows = ActiveSheet.UsedRange.Rows.Count
Stop
r = 2
gr = 1
c = 2
'MsgBox usedrows & " Rows " & usedcols & " Columns"
Do While gr <= graderows
Do While r <= commentrows And Val(Cells(gr, 1)) > 0
If Sheets(&quot;sheet1&quot.Cells(r, 1) = Cells(gr, 1) Then
'Comment$ = Comment$ + Sheets(&quot;sheet1&quot.Cells(r, 2) + &quot; - &quot;
Cells(gr, c) = Sheets(&quot;sheet1&quot.Cells(r, 2)
c = c + 1
Debug.Print Comment$
Else
End If
r = r + 1
Loop
If Comment$ = &quot;&quot; Then
Else
'Cells(gr, 2) = Left(Comment$, Len(Comment$) - 3)
End If
Comment$ = &quot;&quot;
r = 2: c = 2
gr = gr + 1
Loop

End Sub

 

[ETID]

Oh ... make sure you set macro security to low or medium ....from the Tools>Macro....menu

This may require a restart of Excel.

 

[Lynne41]

Hi ETID

Many thanks for the macro. It wouldn't work as it was saying variable wasn't defined so took out the &quot;Option Explicit&quot; from the top of the VBA sheet.

It then wouldn't work with the &quot;Stop&quot; in your code but did when I deleted the word (&quot;Stop&quot. Bizarre!

I have now had an update on the spreadsheet I am working on and have been told there are 9 different sections and numerous sub-sections. This means I would have to define separate ranges for the macro to work on - in otherwords the macro produces individual sections based on grade 1 or 2 etc.

In addition this code you very kindly gave me puts the comments in columns where I wanted them listed in rows (in otherwords all the 1 and 2 grades in column named &quot;Strengths&quot; one after another. I know I'm a pain but this is what the powers that be are asking!

Also the &quot;grade&quot; and &quot;comment&quot; section on sheet1 are in columns B and C so don't know how to point your code to that range.

If there is an easy way to change this code to list the &quot;comments&quot; in sheet1 to sheet2 by rows not columns, AND there was a way to define each range (ie section) this would work. However you have been very helpful and I don't want to take up more of your precious time.

 

[ETID]

Sorry...my bad, the stop and option explicite were by products of haste,..