Process Automation

[saurabhlotankar]

Hi Folks,



I hope you all are doing good.

I badly need to design a macro to automate a process which i am doing manually and taking hell lot of time.

I have attached an excel sheet.

Please find below the pointers that i need to do in it.

• open the attached file.
• Go to sheet3, where you will find a table at the top with headers code, name and country names etc(A1:J11)
• Whereever there is "yes" mentioned in that table i want country name and code(mentioned in column A). i want to list that country name and code from row A14 onwards. Challenge here is, i want country name as many times as there is "yes" in above table.
• To make it simple to understand this time i have copied such data manually from cell A14 onwards. E.g. if you look at United Kingdom, there are 2 yes in above table, so i took United kingdom twice from cell A14 and infront of that i also took code of those 2 yes from above table(A7 & A10)

Here ends the first part of the macro.


lets move to 2nd part.

• In C & D column of sheet3 you will find cells with Filter Criteria headers. I have formulated that. No need to touch them. What I want to do is, use them as a filter criteria in Sheet1 column P
• But the filter should be for Deselecting the values this time.
• For example, in sheet3, cell C14 there is value "UK:06:" , so in sheet1 column P I want to apply filter and deselect rows which have value "UK:06:". and in same way it should deselect all rows which have values in C & D column filter criteria
• Ideally Code should automatically find unique vales from filter criteria, and then apply filter in sheet 1, because all the values are repeating again and again.
• Once the filter is applied, sheet 1 should have all rows except the rows which does not have values mentioned filter criteria in sheet3(like UK:06:, UK:07:, NE:06:. NE:07: etc)

It would be great if you can assist me on this.


Thanks,

[saurabhlotankar]

Any luck guys?

[charlesdh]

Hi,

Not that I have a solution. But, from all of the help you received in your previous post it would appear that you should be able to write the code yourself.

[saurabhlotankar]

Am trying my best but not working out. I apologize if am troubling u guys.

[charlesdh]

Hi,

I noticed that in columns "C" and "D" starting at row 14 have duplicate values.
That is the "UK" has "UK:06: UK:07" 2 times. This I believe is predicated on the nr. of times a country is listed. If this is always the case the you would need to set the filter in sheet1 <> "UK:06: UK:07".
My question to you is will the data starting in row 14 column "C", "D" use the same pattern?
That is if row 14 has "UK:06: UK:07". Will row 15 be the same?
Also in sheet3 you have 2 different codes that = "Yes" but no correlation to this is in sheet1.
So I would assume that for sheet1 you would only need the filter only 1 time for the the data that <> "UK:06: UK:07".

Now once you set the filter for sheet1 what do you do with the filtered data.

[excelledsoftware]

Looks like Charles has a handle on this but my question is the filter part. Do you want any row in sheet 1 to be hidden if it contains any of the values in columns C or D on Sheet 3? That is the only way I can see this doing what you want. If that is what you are looking for let us know. Charles I have the idea on how to do this but since you are first on scene I will only provide it if you need.

Thanks

[charlesdh]

exceledsoftware,

I've been looking at this, but have not found a solution. If you have one please share it with us.

[excelledsoftware]

Ok here is what I was able to put together quickly. Now I have the filter actually hiding the rows rather than filtering it. This is slightly different. In the event you need to actually apply the filter I would advise having a column get a value like a 1 in column DQ then apply the filter to DQ.

Option Explicit

Sub CheckYesValues()
  'Looks at each header in sheet 3 and then identifies if it has a yes.
  'If it does the value is placed into the resultrow.
  Dim wb As Workbook, cws As Worksheet, CheckRow As Long, ResultRow As Long
  Dim Header As String, CheckCol As Integer, LastCol As Integer, LastRow As Long
  Dim Code As String, CheckString As String, rws As Worksheet
  Dim FilterString As String, FilterGroup As Variant, v As Variant
  
  
  'Set the references
  Set wb = ThisWorkbook
  Set cws = wb.Worksheets("Sheet3")
  Set rws = wb.Worksheets("Sheet1")
  LastCol = cws.Range("A1").End(xlToRight).Column
  LastRow = 11 'Hard coded for now
  ResultRow = 14
  
  
  'Perform the checks and print results
  For CheckCol = 3 To LastCol
    Header = cws.Cells(1, CheckCol).Value
    For CheckRow = 2 To LastRow
      Code = cws.Range("A" & CheckRow).Value
      CheckString = LCase(cws.Cells(CheckRow, CheckCol).Value)
      If CheckString = "yes" Then 'print result
        cws.Range("A" & ResultRow).Value = Header
        cws.Range("B" & ResultRow).Value = Code + 0
        ResultRow = ResultRow + 1
      End If
    Next CheckRow
  Next CheckCol
  
  'Hold the values in a string to check
  For CheckRow = 14 To ResultRow - 1 ' Again hardcoded for now
    CheckString = cws.Range("C" & CheckRow).Value
    If InStr(1, FilterString, CheckString) = 0 Then
      FilterString = FilterString & CheckString & ","
    End If
    CheckString = cws.Range("D" & CheckRow).Value
    If InStr(1, FilterString, CheckString) = 0 Then
      FilterString = FilterString & CheckString & ","
    End If
  Next CheckRow
  'Remove final comma
  If Right(FilterString, 1) = "," Then
    FilterString = Mid(FilterString, 1, Len(FilterString) - 1)
  End If
  'Split into array to use
  FilterGroup = Split(FilterString, ",")
   
  '****************************************
  'Now do the 2nd part of the macro
  LastRow = rws.Range("B1").End(xlDown).Row + 1
  'Make sure all are showing first
  For CheckRow = 2 To LastRow
    If rws.Range("A" & CheckRow).EntireRow.Hidden = True Then
      rws.Range("A" & CheckRow).EntireRow.Hidden = False
    End If
  Next CheckRow
  
  'Now check
  For CheckRow = 2 To LastRow
    CheckString = rws.Range("P" & CheckRow).Value
    'Check if its in there
    For Each v In FilterGroup
      If InStr(1, CheckString, v) Then
        rws.Range("A" & CheckRow).EntireRow.Hidden = True
        Exit For
      End If
    Next v
  Next CheckRow
  
  MsgBox "done"
  
End Sub

As with all code, backup your work and save before running.

Let me know if this is a good starting point, the solution or if you need anything else.

Thanks

[charlesdh]

exceledsoftware,

Your code is different form what I was trying to with no luck.
I was attempting to use a filter using an "Array". But, that proved to be a little difficult.
Hiding the rows as you did looks good. But, what does the user want to do with the data after the code runs?
Does the user want to do each country separately or as your code does at the same time.
Also the user did not provide data in sheet1 that was in sheet3.
Perhaps the user will give us more information as to how they want to handle this.

[saurabhlotankar]

Thanks Guys for your help. Sorry for the delayed reply.

I was doing it through formulas. But i guess macro version is faster.

Thanks,
Both of you.

[excelledsoftware]

Hopefully it is working. If so please feel free to mark this thread as solved. Let me know if there is anything else I can help with.