Finding up Duplicate Contain Texts Values from the Table & Display as Report

**Sivashanmugam** · 01-07-2015, 06:01 AM

Finding up Duplicate Contain Texts Values in an Array

I have 3 columns, one is State, City & X Value as a Table (around 50000 records).

I need the solution for the below mentioned criteria.

Input Values: I can input the State in One Cell & City in another cell.
Table/Database Values: State, City & X Value (in %)

Once i give the state & city. I need the list of cities which contains the input value & the respective X value.

Objective is to find out When we input Los Angeles & California in 2 cells.

We should get the list of rows which contains Los Angeles in California City.

i.e. Los Angeles East, Los Angeles West, Lake Los Angeles

IT should display all the Cities name contains Los Angeles in California State.

For more clarity, i attached a sample file. it would be really useful when someone helps on how to get this report.

Note: This Result may lead to Maximum of 500 rows.

This Formula / Macro should do the work on we filtering the first row (heading) of the database -> selecting one state -> under City selecting Filter drop down -> Select Text Filter -> Select Contains .

Once we do this we will get the result which we need to derive it in a formula / in a Macro.

Thanks in Advance

**JOHN H. DAVIS** · 01-07-2015, 07:41 AM

Maybe:

Sub Sivashanmugam()
Dim i As Long

For i = 2 To Range("A" & Rows.Count).End(3).Row
    If Cells(i, "A").Value Like "*" & Cells(2, "H").Value & "*" And Cells(i, "B").Value Like "*" & Cells(3, "H").Value & "*" Then
        Range(Cells(i, "A"), Cells(i, "C")).Copy Range("F" & Rows.Count).End(3)(2)
    End If
Next i
End Sub

BTW your Column Headers are wrong in A/B they should be switched around (ie Arkansas is a State)

**Sivashanmugam** · 01-08-2015, 02:53 AM

Hi John / Apo,

Thanks for your reply, I tried both of your codes / macro but if we run this macro, it should clear the previous results & show only the new results. now old results are also getting displayed.

Kindly help in adding the code for: when we give the state & city as input, once we click the button / run the macro, previous results should get cleared & new results should display as results.

**Sivashanmugam** · 01-08-2015, 03:33 AM

Thanks, it solved the problem by including the this code onto it. it has cleared the previous results & shown the current results
Range("F10:H1000").Cells.ClearContents

the code i used is

Private Sub CommandButton1_Click()
Range("F10:H1000").Cells.ClearContents

Application.ScreenUpdating = False
With Cells(1).CurrentRegion
.AutoFilter 1, "*" & [H2] & "*": .AutoFilter 2, "*" & [H3] & "*"
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy [F10]
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

**apo** · 01-07-2015, 09:34 AM

For 50 000 records.. maybe autofilter..

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    With Cells(1).CurrentRegion
        .AutoFilter 1, "*" & [H2] & "*": .AutoFilter 2, "*" & [H3] & "*"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy [F10]
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Finding up Duplicate Contain Texts Values from the Table & Display as Report

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Finding up Duplicate Contain Texts Values from the Table & Display as Report

Re: Finding up Duplicate Contain Texts Values from the Table & Display as Report

Re: Finding up Duplicate Contain Texts Values from the Table & Display as Report

Re: Finding up Duplicate Contain Texts Values from the Table & Display as Report

Re: Finding up Duplicate Contain Texts Values from the Table & Display as Report

Thread Information

Users Browsing this Thread

Similar Threads

Report Filter when using texts from column

Finding duplicate values

[SOLVED] Filter / Report Top 10 Values with identifier into new spreadsheet / table / report style

How to get a report filter to display info alphabetically in a pivot table

Finding Duplicate Values in a Col,

Bookmarks

Bookmarks

Posting Permissions