Posts by MJB123

    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

    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


    Code
    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


    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!


    Code
    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

    Hi,


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



    Thanks

    Re: Report Of Employees By Date, Holidays &amp; 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 &amp; 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


    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


    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.