Macro to return cells if cell repeats value over 5 times in a list

I have several columns with text. For each column in the sheet, I want to return all of the cell values that repeat at least 5 times in any given column, ideally on a different worksheet in the same workbook (and just list the value once, rather than 5 times in a list). I realize I can do some sort of Countif function, but don't want to have to go through and put in the formula for each column, and drag it down all the way and have to deal with all of the duplicates . I unfortunately don't really know where to start with this so don't have something to post.

Also do you mean repeating 5 times in any positions, or does it have to be 5 consecutive times?

Where do you want the results to appear?

Hi hteaford,

Here is some code that will check each column in a worksheet for values that repeat 5 times or more and then put those values in the corresponding columns of another worksheet.

Please Login or Register  to view this content.

One thing to note for people who are new to VBA, many of the worksheet functions translate over to VBA by put the "Worksheetfunction" nomeclature before them. You can see that with how the COUNTIF function is used in the above code.

Hope that provides some direction,

Dan

Dan,
Thank you see much; It worked perfectly and I was able to modify as I wanted to repeat on every 5th column (I don't want it to count each column). Thank you!

Again, thank you for all your help. After working on the project a bit, I have realized I want something slightly different. I have attached a sample spreadsheet of what the data I have looked like Sheet "all" and want I hope to have as an output ("what I want"). In summary: on output sheet, I want the macro to

1) place value of A2 (type) on sheet "all" to A2 on sheet "what I want". This only needs to happen once
2) roll through column b on "all" and run the "count 5" macro that has been the topic of this thread to return names on separate worksheet(in this case "what I want"- can be any name) of a names that appear at least 5 times
3) now, for each name that is returned in that it appears at least 5 times in column B on "all", the values of C and D may differ, even if the value in B is the same. I want columns C and D on "what I want" to list each combo of cells C/D on "all" for each name that repeats at least 5 times. Each combo should be listed once (even if it appears multiple times)
4) For column E, I want the count of how many times that combo appeared when in association with the name that was returned in column B (I don't want the total times a combo repeats in the column, in needs to be total times it repeats WITH the value in column B that was counted at least 5 times).
5) Repeat process on columns F:J (and every chunk of 5 columns after that until last column) and have output on same sheet


Sorry if this is very complicated: hopefully the sheet I have attached explains what I am looking for