2

Got the following question. More likely to be solved by VBA code but as I'm newcomer on VBA, I don't know how.

I have the following table

enter image description here

I'm putting the formula in the cell F2, see below, to figure out redundant values and ultimately, to remove them.

IF(COUNTIF(B2:E58,A2)>0,"YAHOO","Not Present")

My aim is to remove these redundant values automatically and not manually, by either deleting the cell or deleting the values inside.

I've checked on the internet and some solutions that came out, were talking about helpers columns or intermediate table, some others came out with VBA. I had look at the helpers column but I don't see how I can remove my values automatically through helpers columns.

Any suggestions are welcomed.

Thanks in advance.

Edited (1st take):

Thanks to Don Relentless for his help but I think my explanation is not clear enough.

Have: One column, A. Looking within that column A for values that exist when doing a search within an array , B2:E58.

Method/ Solution: Use IF(COUNTIF(B2:E58,A2)>0,"YAHOO","Not Present") to spot the values within colmun A and also present in the array.

Example: For A2, check on B2 to E2 until E58. Next will be for A3 , check on B2 to E2 until E58.

Want: Remove the cell either within the array or in the column by either making them empty or put something like YAHOO.

I hope I'm clear. If not give me a shout.

Andy K
  • 4,944
  • 10
  • 53
  • 82
  • So do you want to delete the cell in column A if it's not found within the table B2:E58? What is wrong with your current approach? –  Jul 02 '14 at 15:36

2 Answers2

1

If you are trying to replace the rows in columnA with blanks or "Yahoo" when a match occurs, then follow these two steps

1) Select cells B2:E58 and name them MyArray 2) Run the code below

Sub ReplaceBlanks()
    Dim i
    Range("A2").Select
    Do While Not IsEmpty(ActiveCell)
    If WorksheetFunction.CountIf(Worksheets("Sheet1").Range("MyArray"), ActiveCell.Value) > 0 Then
        ActiveCell.Value = ""
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub
Ravi Yenugu
  • 3,895
  • 5
  • 40
  • 58
  • Hi ThinkingCap, your code is working great. The 50 points are yours. However, one advice though : The worksheet naming can be different due to countries. As I'm working with a French excel, worksheet name is "Feuil1" ;) – Andy K Jul 03 '14 at 07:21
  • One question though, @ThinkingCap: Am I clear in my formulation of my problem? I put great emphasis on the formulation but it seems that some people are still puzzled by my explanations... You got it right but maybe , there is something for me to improve? Cheers. – Andy K Jul 03 '14 at 07:25
  • @AndyK I think you should always show a screenshot of your current data and what you want to look it like after. Your explanation was not the clearest :P –  Jul 03 '14 at 07:32
  • Sounds good @"Me How". I will do that from now on. Thank you again for your help. – Andy K Jul 03 '14 at 07:38
0

  1. "either deleting the cell or deleting the values inside" - please clarify, because deleting the cell will imply shifting the rest left, or right, etc - must be clarified). Instead of deletion you can set the cell value to empty ("") or any other string using VBA like Range("B2").Value="" (no actual deletion here).Finding sub-string (e.g. "ABC") within another string (e.g. "10ABCD") in VBA is performed by function InStr(), like InStr("10ABCD", "ABC")>0 which indicates a match. You can apply these functions while looping through a range of cells, e.g. Range("B1:E58") pertinent to your particular task and accumulate the results of each`InStr(): non-zero will indicate the existing match (one or many) for the cell under the test ("A1", then "A2", etc.).

  2. The same task can be completed using Worksheet functions as per your example, expanding them to entire column "E".

Regards,

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
  • Hi Alex, I got your point. As you said , what I meant by deletion , is empyting the cell. For the rest, I will need some further testing. Many thanks for your help. – Andy K Jul 03 '14 at 07:54
  • Thanks again, Alex. I'll need some luck. :) Cheers. – Andy K Jul 03 '14 at 14:02