Search Excel chart for grouped cell contents?Closed

Question

Hello,

I want to be able to search an Excel chart looking for "groups of cells" in other rows that have the same sequence of contents. 

For instance, assume one row contains cell S26 with cell contents of [%&], and the adjacent cell T26 in the same row  contains cell contents of [98*] . &#8232;&#8232;If I want to search the rest of the Excel table to find all other groups of adjacent cells in other rows that match the same adjacent cell contents of [%&] next to [98*] that I had put in S26 and T26, is there a way to do that? &#8232;

Thanks,

RJK

venkat1926 · Answer

your requiremnt is not clear. I am guessing how your data is like. 

I shall give you an example modify this to suit you 

the data is from a1:B7 with headings in row no. 1 like this 

hdng1	hdng2
1%&2	9845
123    	98456
2%&4	98423
324	      45678
5%&3	7589
6%&5	9856

now in col E and F introduce a criteria range like this (E1:F2)

hdng1	
*%&*	TRUE

in F2 you copy this formula 
=LEFT(B2,2)+0=98
once you type this formula and hit enter key you will get the result of the formula as "True"
you should not type "True" in F2 . It should be the result of the formula. 
note that B2 is selected because it contains 98 as the first two number. If B2 does not cotain 98 as the first two numbers you have to choose another B cell in your data.
note that F1 is blank 

now highlight the original data A1:B7

click data-filter-advance filter
choose "copy to another location" under "action" at the top 
in the middle choose list range as "a1:B7  easier to do it is click the reference icon at the extreme right end of this small window and take the mouse over the whole of your data A1:B7 and hit enter key
against "criteria range" similar take the mouse around E1 to F2 even though F1 is blank.(hit enter key)
in the "copy to" window again take the mouse any one cell e.g. A15(which  is the top left cell of the result)
hit enter 

now click ok once and again once more 
see the result from cell A15

the result  will be like this from A15

hdng1	hdng2
1%&2	9845
2%&4	98423
6%&5	9856


now I am sure you can modify the filter condition for your data

If there is any problem post a small extract of data and also  how the result looks like

venkat1926 · Answer

advance filter is another way of search find. did you try my solution to my example data. will it not give you a clue what to do . this is easier method. I suppose you want a macro. Here is the macro for your changed criteira. 

Sub test()
Dim x As String, y As String
Dim rfind As Range, add As String
x = "Homer"
y = "Deb"
Worksheets("sheet1").Activate
ActiveSheet.Cells.Interior.ColorIndex = xlNone
On Error Resume Next

Set rfind = Cells.Find(what:=UCase(x), lookat:=xlWhole)
If Not rfind Is Nothing Then
add = rfind.Address
If rfind.Offset(0, 1) = y Then
Range(rfind, rfind.Offset(0, 1)).Interior.ColorIndex = 3
End If
End If
Do
Set rfind = Cells.FindNext(rfind)
If rfind.Offset(0, 1) = y Then
Range(rfind, rfind.Offset(0, 1)).Interior.ColorIndex = 3
End If
Loop While Not rfind Is Nothing And rfind.Address <> add

End Sub



If the criteria changes modify 
x=  
y=

note: spellings should be correct including case (upper or lower)

Search Excel chart for grouped cell contents?

2 responses

Similar discussions

Subscribe To Our Newsletter!