Search results

Hi Inddon Will the data be mixed? 1 1 2 2 5 2 2 4 3 1 etc Can you udate your file and show what it really looks like. I have an idea but need to see some real data. Take care Smallman

Hi Charlie What are you attempting to do? I would prefer to see a file before and with data after the procedure has run. If you just say what you are trying to do there may be a better way than looping through your cells. It is not very clear so please restate and take the time to provide...

Hey Paul I was reticent about clicking on the link but went ahead anyways. That is really good - thanks for sharing. LOL - Shot in the Ass O Meter Priceless!!! Take care Smallman

Wow This post looks like you are about to choose a much longer formula based on something you have read about Volatile functions. I am afraid I am with Hui on this one. Volatile functions are really only a problem in very large workbooks where calculation processing power is compromised...

Hi Ram72 Your latest file shows more forethought and is easier to understand. A formula can't delete VBA. I would recommend you put a filter on Column Q and filter for Blanks then simply delete the rows in your filter. This is quick and requires no code. If you must have code then the...

Gaiter Welcome to Chandoo forum :) First of all your data in Column C is text not a date. Please convert the data in Col C to a date as no if statement will work the way you intend using text (dates are really numbers in discusie). Put a table in another sheet with your fiscal years and the...

Hi Jack The formula for the conditional formatting looks like the following. =AND($A2="U",$B2="D",$C2="D",$D2="U",$E2="D") THe file outlines the technique. Take care Smallman

Hi Ram72 What rows need to be deleted. I can't see any difference between the rows you have highlighted and the others in your file. Is it all rows which have an empty cell? What is the difference between Row 6 and row 13? How are we meant to differentiate the two. Clarity required. Take...

Bines53 In your example file you gave 1 single example yet rows 12-19 are blank. It is difficult to see a pattern when the dataset is incomplete. At any rate the long hand formula may be something like this. =INDIRECT(LEFT(J1,1)&ROW(A1),1)&","&INDIRECT(MID(J1,2,1)&ROW(A1),1)&"...

Hi Consider the following. Note I move the file to the end of the file in line with my code. Option Explicit Sub getAllValues2() Dim i As Integer Dim j As Integer Dim ar As Variant ar = [{"G3", "A1", "C4", "C3", "C5", "C5", "A28", "J3", "J4"}] For i = 1 To Worksheets.Count - 1...

Hi Mr Karr The following will list all of the slicer items in your slicer. Option Explicit Sub ListSlicerItems() Dim i As Integer With ActiveWorkbook.SlicerCaches("Slicer_Test") For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_Test").SlicerItems.Count Range("B" & i) =...

Hi Karyn Welcome to the Chandoo Forum. :) Give the following a try. =IF(COUNTIF($A$2:A2,A2)=1,1,"") Take care Smallman

Hi bines53 What about number 3, it appears 4 times? Why is 7 ahead of 3? Take care Smallman

Hi Sampath Give the following a try. Option Explicit Sub MergeMe() Dim i As Integer For i = 1 To Worksheets.Count - 1 Sheets(i).[a1].CurrentRegion.Copy Sheet4.Range("IV1").End(xlToLeft)(, 2) Next i End Sub Take care Smallman

Hi kuldeepjainesl The website I quoted in my post is my own, in anticipation of any such questions. It expains this formula as simply as I could put it. Take care Smallman

Onother option is to change the dates from text to dates then you can use a formula. An example for the Acutals. =SUMPRODUCT((Actuals!$A$3:$A$23=$A7)*(Actuals!$B$1:$M$1<=$F$2)*(Actuals!$B$3:$M$23)) Take care Smallman

Hi A non array formula. =IFERROR(LOOKUP(2^15,SEARCH($D$2:$D$10,A2),$E$2:$E$10),"") Ensure your data in Col E is filled down (your result) and I added some items you omitted from your list. See attached for example. Another example FuzzyLogic Take care Smallman

Hi Vishwasexcel Welcome to the Chandoo forum :) Your first and most pressing problem is your data layout. You will have lots of trouble getting a scoll bar working effectively without the months down the side. When you set up your pivot tables instead of the filter being month you should...

Hi Vicktor The fastest way to do it would be to concatenate B to F and H to L and put a countif fomrula in column N and then just use a filter to weed out the matches. Any 1 appearing in N is a match. This could be simply cut and pasted to the area you wish. Example attached. Take care...

Well without a file that can't be tested except in a test environment. Here is what I do when i want to test a theory - put some dummy data in a fresh sheet, write a snippet of code on the back of some simple conditional formatting and run it. If that works I know that the theory is good and...

Hi ysherriff This code would be easier to follow if we did not have to recreate it. You said you attached a file but there is nothing. Please attach a file. Take care Smallman

Hi Real I am please you got there in the end. Have a good new year. Take care Smallman

Jack Change: Worksheets("Found Values")Cells.ClearContents to Worksheets("Found Values").Cells.ClearContents where is the . before the cells - see above. Take care Smallman

Jack Just a caveat on Kchiba's comment. If your data is broken by blank whole lines this will not go to the bottom of the range. It will go down as far as the first break. Another reason to keep your data tabular with no breaks, just like a database. Take care Smallman

where is the . before the cells?