Posts by MJB123

Re: Remove none repeated rows

Hi, thanks. Would a macro work faster than a formula?

Hi All - I have a database of sales transactions in column A and products in column B (see attached spreadsheet). I want to remove all unique rows (i.e. transactions which only have 1 product). If a transaction has more than 1 product then the transaction is repeated. Ultimately I want to only show transactions which contain more than one product. My database is 65k lines long so which is the fastest way to do this? Thanks

Re: Vba to find most frequent items/groups of items bought

I don't know where to start with it really. A suggestion would be great to get me started

Thanks

Mike

Hi All,

I have a database of sales transactions and products bought within those transactions. A transaction could contain between 1 and 20 products. I am trying to find out what the most frequently bought items are, and groups of items.

This kind of analysis is called market basket analysis and typically uses an algorithim called an 'apriori algorithim', and I am trying to reconstruct this in excel.

I have attached a sample spreadsheet of the database and what I would like the output to be. The database would contain around 10,000 products within 2,000 transactions so would need a macro to be most efficient.

Hope someone can assist.

Many thanks

Mike

Re: Val To Cover Range On Mid Function

Thanks shg and AAE for your replies.

I have decided to use shg's idea of inserting a formula and setting it to a value.

Please see below

Sheet5.Cells(Target.Row, 10).FormulaArray = "=SUM(IF(RC[2]:RC[184]<>"""",IF(LEFT(RC[2]:RC[184],1)=""S"",MID(RC[2]:RC[184],2,20)+0,0)))+SUM(IF('Oct-Mar'!RC[2]:RC[183]<>"""",IF(LEFT('Oct-Mar'!RC[2]:RC[183],1)=""S"",MID('Oct-Mar'!RC[2]:RC[183],2,20)+0,0)))"
Sheet5.Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value

Many Thanks,

Mike

Re: Val To Cover Range On Mid Function

Theres no way of doing this without using a formula then?

Re: Val To Cover Range On Mid Function

I basically have a lot of cells which contain information such as 'H8', or 'S8' or 'T8' and I need to sum up all the number elements of these cells.

I thought that doing it this way would be quicker than inserting an array formula and pastevaluing.

Mike

Hi,

I am trying to get the following to work but I am having no luck!

Cells(Target.Row, 10) = Val(Mid(Range("L6:GL6"), 2, 20))

This works when the range covers one cell, however I need it to cover a number of cells. Does anyone know how to fix this or another way to do it? I don't want to use a formula if I can help it.

Thanks,

Mike

Re: Worksheet Change Not Firing

Hi,

I solved the problem, enable events had been turned off from another workbook which was open.

Thanks for you help.

Mike

Hi,

Does anyone know why this code does not work when the worksheet is changed between range "B1:F5"?

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("B1:F5")) Is Nothing Then


With Range("B1:F5")
Cells(Target.Row, 7) = Cells(Target.Row, 6).Value + Cells(Target.Row, 5).Value
End With


End If


End Sub

Thanks

Re: Report Of Employees By Date, Holidays & Sick Days

Ian,

I have come across an error in the code.

I have extended the range of dates to look at to 365 columns, 1 for every day of the year.

If I have a date which is at the end of the year (i.e end of the holiday year, 31 Mar 09) when the code is run it adds 1 to whatever is put down as the amount of hours for the leave.

Example, if I put 'H8' in for 31 Mar 09, then on the report it shows that day as 9 hours annual leave???

I've tried going through the code but can't see where it is doing this.

Thanks,

Mike

Re: Report Of Employees By Date, Holidays & Sick Days

Hi Yard,

Thanks for your reply, and what you have done works well!

I know the layout of the sheet is bad, but this is the company format for the holiday planner, which this is for.

Is there a way of getting my format into yours with a macro or running the pivot table straight from my format? (Bearing in mind there will be a lot more employees and the dates cover a year going across the top.

Thanks,

Mike

Hi,

Not sure what to put as my thread title, if someone can change then please do so.

Please see attached spreadsheet on what I am trying to achieve.

In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.

I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.

If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.

Thanks and please ask any questions.

Mike

Re: Sum Value Part Of Cell Only

Great! Thanks Yard!

Re: Sum Value Part Of Cell Only

Thanks Richard,

The sumproduct formula works well. How could I alter this so that I don't get a #VALUE! error when the formula covers a range where there might be some blank cells?

Could also do with finding out a way of just adding up the cells which start with a 'H'?

Thanks for your help.

Mike

Hi,

I have a number of cells which contain information such as 'H8', 'S4' and 'T6', etc, etc.

What I want is a formula which sums just the numerical part of these cells, so would add up the 8 plus 6 plus 4.

I have hundereds of these cells filled out like this. The first character in the cell is always a letter and then the number will either be a singe number (e.g. H8) or decimal (e.g. H7.5).

Thanks,

Mike

Re: Colour Cell Based On First Letter In Cell

Perfect! Cheers Yard!

Re: Colour Cell Based On First Letter In Cell

Yard,

Looks and works well!

I just need it to run when the worksheet is changed, but don't know whether to add it to a worksheet change event or calculate event.

When I type H8 into a cell and press enter I want that cell to colour.

Thanks

Hi,

Sorry if the thread title could be better.

I need a way of colouring a cell based on what the 1st letter of the cell is.

So for example, if the cell value is H8 or H4 then it goes yellow, if it is S4 or S6 then it goes green, etc, etc.

I have about 8 different criteria so can't use the standard conditional formatting.

Cheers,

Mike

Re: Find Dates Over Multiple Sheets

Daniel,

I've cracked it! I've had to write two pieces of code and flick between each code depending on what the month the date is in, so if it is january, it looks in the sheet oct-mar.

Please see below. There might be a better way of doing this but atleast it works!

Thanks for all your help.

Sub Find_Dates()
Dim Variable, c As Range
If Sheet1.CheckBox5 = True Then
    
On Error GoTo ErrorHandler
    Sheet1.Activate
    Range("AB14").Select
    Do While ActiveCell.Offset(0, -1) <> ""
    If ActiveCell.Offset(0, -2).Value > 9 Then
    GoTo ErrorHandler
    Else
        Variable = ActiveCell.Value
        Sheet5.Activate
        Set c = Cells.Find(What:=DateValue(Variable))
        c.Activate
        If Not c Is Nothing Then
            ActiveCell.Offset(1, 0).Select
            Range(ActiveCell, ActiveCell.Offset(299, 0)).Select
            With Selection.Interior
                .Pattern = xlGray8
                .PatternThemeColor = xlThemeColorLight1
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.14996795556505
                .PatternTintAndShade = 0.499984740745262
            End With
        End If
        End If
ErrorHandler:
        'Range("A1").Select
        Sheet1.Activate
        ActiveCell.Offset(1, 0).Select
     Loop
Find_Dates2
Else
Remove_Dates
End If
End Sub


Sub Find_Dates2()
Dim Variable2, c2 As Range
On Error GoTo ErrorHandler2
    Sheet1.Activate
    Range("AB14").Select
    Do While ActiveCell.Offset(0, -1) <> ""
    If ActiveCell.Offset(0, -2).Value > 3 And ActiveCell.Offset(0, -2).Value < 9 Then
    GoTo ErrorHandler2
    Else
        Variable2 = ActiveCell.Value
        Sheet6.Activate
        Set c2 = Cells.Find(What:=DateValue(Variable2))
        c2.Activate
        If Not c2 Is Nothing Then
            ActiveCell.Offset(1, 0).Select
            Range(ActiveCell, ActiveCell.Offset(299, 0)).Select
            With Selection.Interior
                .Pattern = xlGray8
                .PatternThemeColor = xlThemeColorLight1
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.14996795556505
                .PatternTintAndShade = 0.499984740745262
            End With
        End If
        End If
ErrorHandler2:
        'Range("A1").Select
        Sheet1.Activate
        ActiveCell.Offset(1, 0).Select
     Loop




Sheet5.Activate
Range("A1").Select
Sheet6.Activate
Range("A1").Select
Sheet1.Activate
Range("A1").Select
MsgBox "Public Holidays have been added to the planner"
End Sub