filter to show only 1 instance (1 Viewer)

[smiler44]

I have a spread sheet with a few thousand rows of data. I want to filter using the data in column L that matches for example pen, car, boat.
I do not however want every row but just any one row that has pen in column L, or has car or boat in column L.
So even if there are 10 rows with pen in, I just want any one row to show.
I want to hide the rest.

How?

thanks

smiler44

 

[namliam]

add a column that does a countif, then also filter on that column = 1

 

[smiler44]

add a column that does a countif, then also filter on that column = 1

namliam, thank you but sorry I don't follow how to and I need to add a twist to me request.
The spread sheet I want to filter is a new spread sheet that I download from a server. Anything I want to do I think needs to be done via the personal workbook.

what should the two formulas look like? I tried doing a multiple countif but could not get it right and I'm unsure about the filter for = 1

smiler44

 

[namliam]

no 2 formulas, just the one countif that counts the instances of the values that you want to filter

Then you add a filter to search "pen" and another filter on the countif column for only 1 value.

 

[Brianwarnock]

I would be looking to write code that uses the Match function type = 2 to find the first row containing each of the required values, and then using this information to create a new sheet of the selected rows.
The required values could be in a separate sheet for ease of amendment.

Brian

 

[Brianwarnock]

Ok I wouldn't use the worksheet function Match in code but code the search directly.
The code below is a very simple conceptual example with hardcoding of one search, I just wanted to make sure that there were no problems searching a sheet with all rows hidden.

Dim r As Long
Sheets(1).Rows.EntireRow.Hidden = True
For r = 3 To 7
    If Sheets(1).Cells(r, 2).Value = "c" Then
        Sheets(1).Rows(r).EntireRow.Hidden = False
        Exit For
   End If
Next r

You would need to replace the 7 by lastrow, the value "c" by a reference and the Exit For by an iteration on the reference for the values then restart the search.
And of course all cell and sheet references, you have much to do.

Brian

 

[smiler44]

thank you for the replies. By accident I found an easy way. Not a way that I had expected but never the less will do what I need
ok, I use a macro to filter my spread sheet to show only the rows that contain pen, car, boat etc in column whatever. could be about 25 criteria's and of course be hundreds of rows. I click the down arrow of the filter. It lists 1 instance of what I want in that column as well as other entries but .................mine will have a tick in.

By accident I found this, may seem obvious to you, was not to me.


smiler44

 

[Brianwarnock]

Hmm! I thought that you wanted to totally automate it for the user.

Brian

 

[RayH]

You can create a pivot table from the data to do this.
Select a couple of columns, your reference column along with one other.
Drop the 2nd column into the values area and choose 'count' of data'.
This will give you a unique list of references along with a count of how many you have of each. It'll even sort it for you.
You can leave it in place and you will just have to refresh it for update results.

 

[smiler44]

Hmm! I thought that you wanted to totally automate it for the user.

Brian

I did want to automate but accidently finding this did the job...of sorts.
when I click away from excel the drop down list of the filter closes so it was not quite the solution I thought it was

smiler44

 

[smiler44]

You can create a pivot table from the data to do this.
Select a couple of columns, your reference column along with one other.
Drop the 2nd column into the values area and choose 'count' of data'.
This will give you a unique list of references along with a count of how many you have of each. It'll even sort it for you.
You can leave it in place and you will just have to refresh it for update results.

sorry I'm lost.
smiler44

 

[Brianwarnock]

I thought that this had been resolved.
exactly one month ago I suggested some code, but I guess you did not like that, however I attach two samples , one in 97-2003 format , the othere done in 2013.

they are simple but show the idea in full/

Brian

Ah this forum will not allow me to upload an xlsm file

 

[smiler44]

gggrrrr typed a reply but it timed out and I lost the post.

Brian,
thank you. I think I understand but can I check. macro2 will display on sheet1 1 instance of a word in column B if that word is found on sheet2 column A.
Do I need macro 1.

Thank you for taking the time tom put this together in an excel work book.
I will send the code to work as I use excel 2010 or 2013 at work but 2007 at home. sheet2 I will have to populate by a macro but that's easy and then follow with your macro. I'm making mistakes doing it manually so not only will your macro save me time but stop me making mistakes

thanks again
smiler44

 

[Brianwarnock]

You are correct in your reasoning as to how macro2 works and yes you don't need macro1, sorry for my sloppiness for leaving that in , it was part of an original simple test.


Brian

 

[smiler44]

Brian, thank you. Not sloppy at all I will keep macro 1 an use it but was just checking it was not required in conjunction with macro2.

could you help me a bit more? agh, hang on, I may have it, let me go try and I'll be back

smiler44

 

[smiler44]

Brian,
no, I'm stumped.

I cant work this out



For r2 = 1 To lrws2
'rescan
For r1 = 2 To lrws1
If ws1.Cells(r1, 2) = ws2.Cells(r2, 1) Then
ws1.Rows(r1).EntireRow.Hidden = False
Exit For
End If
Next r1
Next r2

I thought:
r2 was column 2 on sheet 1
r1 was column1 on sheet 2

(r1, 2)
what is the 2?

is 2 the column and r2 the row?

smiler44

 

[smiler44]

no worries. all sorted and macro is working fine.
after a couple of hours I found some columns were hidden in the original work book :banghead:

thank you everyone particularly Brian

smiler44

 

[Brianwarnock]

Glad its working for you, sorry I did not reply yesterday evening but was out walking yesterday and spent the evening trying to buy pensioner bonds from NS&I so did not visit this site after an initial look at around 6 pm.

Brian

 

[smiler44]

Glad its working for you, sorry I did not reply yesterday evening but was out walking yesterday and spent the evening trying to buy pensioner bonds from NS&I so did not visit this site after an initial look at around 6 pm.

Brian

Brian my goodness no need to apologise your always very helpful. whenever I see your user name on one of my posts I know the issue will be resolved.

smiler44