Posts by jacjas

Apologies but I don't understand your reply / post do you want me to add a file as an example

Hi

I have asked this before but didn't get a resolution and thinking I possibly didn't ask it correctly so am going to try again.

I am creating a mail merge document for stickers each sheet has 8 stickers

the mail merge data consists of class data I want to create a VBA code that will look at the class code column and when the class code changes count back how many rows this class code covers and if not divisible by 8 will add enough blank rows so it is and then the same for the next set of class codes in that column.

this is to ensure that when I complete the mail merge there aren't multiple classes on one sheet of stickers, I have a code that will add one row at a change in value in the class code column but this doesn't get me over the need to not have multiple classes on one sticker sheet

Re: concatenateif formula not working ?

this has worked a treat thank you so very much this has now saved me hours of work thank you

not sure if its no longer a recognised formula but I have an old spreadsheet where I am using a concatenate if formula

=CONCATENATEIF($B$2:$B$82, H2, $E$2:$E$82, " & ")

but all I keep getting is the #NAME error so I am assuming that this is no longer a compatible formula can any one confirm and if so what can I use to replace this and get the same result many thanks in advance

Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8

yes that's correct like i say my explanation most probably was to blame, would it help if i anonomised the data and sent or uploaded the file ?

column C is sorted

Yes there are headers (column titles)
Yes the data starts in row 2
sheet name is simply "Sheet1"
column T is the last data column and it is not always populated i can delete it as that data is not required for the mail merge.

Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8

thank you again, you are most patient. i see now what this is doing and indeed it does exactly as you say, thank you. however this is not what i am looking for i probably wasn't clear in my explanation apologies.

i have a broadsheet of data that i don't want to change in Column three i have class codes that will repeat for so many rows then change value. what i want to do is run a code that will based on col 3 identify where the value changes and then count the rows where the value matches above it and if divisible by 8 leave it as it is and if not divisible by 8 then add enough blank rows so that is it before the next value in that column.

this is so that when i use this excel sheet as a mail merge source for a sheet of labels ( 8 per page) i wont get multiple class codes on the same sheet of labels, i will get the one class and any blanks that we have added on one sheet does that make sense is this possible ?

Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8

Quote from M40wen;793599

The sheet you want that code to execute from, it needs to be there rather than a module. Its difficult to know about your current code as id have to see it all...

Thank you for your support and patience but i cannot get this to work, I have opened my sheet and in the editor i have selected worksheet as the object and change as the procedure

then copied in the below code but when i click on run it asks me to name a macro ?? i have tried using the Worksheet_Change as the name but it just opens a new window for a macro apologies can i ask for more direction please

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
         
        If Target.Row - 1 Mod 8 = 0 Then
             'divisible by 8 so do nothing - change if you need
        Else
             'not divisible by 8
            Application.EnableEvents = False
            Rows(Target.Row - 1 & ":" & Target.Row - 1 + (8 - Target.Row - 1 Mod 8)).EntireRow.Insert
            Application.EnableEvents = True
        End If
         
    End If
     
End Sub

Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8

hi

many thanks for your response, im a complete novice at VBA do i need to add the above code you have given me somewhere into my original code ?

I have the below code that will insert a blank row into my data based on a change in value in column 3, i need to alter this to look at column 3 and at a change in value count the number of rows above it and if divisible by 8 leave it as it is if not divisible by 8 then add enough blank rows so it is.

I want to use the data I have as a mail merge document into a label template that has 8 labels per page. and want to ensure that at a change in value in column 3 it goes onto another sheet of labels and this is the only way I can think of doing it other than scrolling down counting the rows and manually adding blank rows on a worksheet that has 1910 rows of data

Insert blank row at a change in value in column 3

Sub NEW_ROWS()
Dim rw_1 As Long: rw_1 = 2  'start row
Dim rw_2 As Long
crit = Cells(rw_1, 1)
Do Until crit = ""
    rw_2 = rw_1 + WorksheetFunction.CountIf(Columns(3), crit)
    Rows(rw_2).Insert
    rw_1 = rw_2 + 1
    crit = Cells(rw_1, 1)
Loop
End Sub

I am trying to compare two columns of numerical data to see if they match but one is a number that goes to multiple decimal places and the other goes to two decimal places e.g. -1.305 and -1.31 I want to try and combine an if statement and a roundup statement is this possible ? I would like it to look at the first cell and roundup to 2 decimal places then compare that to the other cell and if matches says yes if not No I have tried it several ways but cannot get it to work thank you in advance to anyone that can help

Re: count a text string in one column up to a change in of data in another column

that's great thank you I am sure I tried over complicate this, this is greatly appreciated.

I have an excel list column A has the person ids, column I has a text string of AM or PM I want to add a formulas to the end column (M) to count the number of times AM appears in the AM column for each person id so for example id A09356 is repeated in Column A 100 times it then changes to A09513 I want the formula to count the number of AM entries for A09356 then when this changes to A09513 calculate how many AM entries for A09513 and so on.

I have attached a file

I am having a complete blank, I have a table of data column headers are names I want to write a formula that will look at a cell that is populated from a validation list then match the name in that cell to the same name in the table of data once it has located the name I want it to sum the rows of data below it.

I know I can simply sum each column but want to use this way for another purpose aswell.

so based on the cell R2, S2 will calculate the total paid by that person from the table of data. see attached

Re: VBA code to split data to seperate tabs based on a change in Column 1

Many thanks for your help I had a moment of inspiration after I asked the question and removed the / and replaced with a . and the code worked perfectly, thanks for all your help I am a novice at this but am thoroughly enjoying learning and find this Forum a huge source of support.

I will try your above suggestion just to learn some more new funstionality.

thanks again

Re: VBA code to split data to seperate tabs based on a change in Column 1

Huge apologies i wasn't aware of this and thank you very much for adding on this occasion.

Re: sheet names ok is there a reason these are unusable ?

thanks again

Hi

I have a set of data that I need to separate out to individual tabs.

I need the data to separate at a change of text in column 1. I also need the tabs to be named after the text they are separating from.

e.g. column 1 is a list of teaching groups at each change of teaching group I want the data to split to a new tab and name the tab after the Teaching group taking all the data for that group with it using the same column headings.

I have tried the below but it fails:

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1        
Set ws = Sheets("Sheet1")        
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:K1"           
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Re: Populate other cells based on specific criteria

That's brilliant thank you, I've had to change it around a little to fit the master I have but I got there in the end I'm in awe I have no idea what that formula is or how it works or was built but it does what I need so once again thank you very much

Is there a formula that can do the following for me:

I have 270 students with four humanities subjects, Humanities, Geography, History and Beliefs, Philosophy & Ethics. For each student there has been a target grade entered for the subject Humanities , I want to use a formula that will look at the Student Id, then look at the humanities grade entered and then populate the target grade field for Geography, History and Beliefs, Philosophy and Ethics for that student with the same target grade entered for Humanities. see tables below and I have attached an example file

[TABLE="width: 568"]

cid

year

tg

house

subject

tgroup

target

A09314

9

9T2

Thoresby

Beliefs, Philosophy & Ethics

9y/Bp1

A09314

9

9T2

Thoresby

Geography

9y/Gg1

A09314

9

9T2

Thoresby

History

9y/Hi1

A09314

9

9T2

Thoresby

Humanities

9y/Hu1

7c

[/TABLE]

Look at theCID as that’s unique to that student, then look for the Humanities subject andits relevant target grade, then populate the three blank humanities subjects withthe same target grade that has been entered for Humanities, end result seebelow.

[TABLE="width: 560"]

cid

year

tg

house

subject

tgroup

target

A09314

9

9T2

Thoresby

Beliefs, Philosophy & Ethics

9y/Bp1

7c

A09314

9

9T2

Thoresby

Geography

9y/Gg1

7c

A09314

9

9T2

Thoresby

History

9y/Hi1

7c

A09314

9

9T2

Thoresby

Humanities

9y/Hu1

7c

[/TABLE]

Re: vlookup and sumifs or count ifs

Brilliant got to the Countifs to work eventually I couldn't use the SUMProduct as the headers differed. thanks again guys you are brilliant

Hi

I have a work book with two worksheets.

the master data tab and a summary table tab

I need a formula that can do a vlookup so using an id number in my summary table, locate that id number in the master table it will appear multiple times and then I need it to look at another column in my master data and count if for that id number the other column has a specific text string so for example.

for id number A09669 there are a count of 3 entries for the action a written comment and for the same ID a count of 1 entry for the Action Kept Behind

please see attached