# Posts by MJB123

• ## Remove none repeated rows

Re: Remove none repeated rows

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

• ## Remove none repeated rows

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

• ## Vba to find most frequent items/groups of items bought

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

• ## Vba to find most frequent items/groups of items bought

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

• ## Sum Numbers Only Of Cells Preceded With Text

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.

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

• ## Sum Numbers Only Of Cells Preceded With Text

Re: Val To Cover Range On Mid Function

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

• ## Sum Numbers Only Of Cells Preceded With Text

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

• ## Sum Numbers Only Of Cells Preceded With Text

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

• ## Sheet & Workbook Event Code Not Firing

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

• ## Sheet & Workbook Event Code Not Firing

Hi,

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

Thanks

• ## Report Of Employees By Date, Holidays & Sick Days

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

• ## Report Of Employees By Date, Holidays & Sick Days

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

Hi Yard,

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

• ## Report Of Employees By Date, Holidays & Sick Days

Hi,

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

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.

Mike

• ## Sum Cell Numbers Which Are Preceded With Text

Re: Sum Value Part Of Cell Only

Great! Thanks Yard!

• ## Sum Cell Numbers Which Are Preceded With Text

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'?

Mike

• ## Sum Cell Numbers Which Are Preceded With Text

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

• ## Colour Cell Based On First Letter In Cell

Re: Colour Cell Based On First Letter In Cell

Perfect! Cheers Yard!

• ## Colour Cell Based On First Letter In Cell

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

• ## Colour Cell Based On First Letter In Cell

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

• ## Find Dates Over Multiple Sheets

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!