Search Excel chart for grouped cell contents?

[Closed]
Report
-
 RJK -
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*] . 

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? 


Thanks,

RJK

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Thank you.

I am not being clear. The problem is that Excel can only "search find" single cell contents. I want to take a targeted pair of adjacent cells in a given row, and search find any other paired adjacent cells in other rows that contain identical contents to my targeted pair, and have the search find feature highlight them for me, much like excel does when searching for single cells with the same contents.

As an example: Row 22: Cell 22:D contains value "Homer" and Cell 22:E contains value "Deb".

I want to find any other areas in my Excel worksheet that contain duplicate adjacent cells in given rows also with "Homer" next to "Deb", and just find and highlight those other areas of the worksheet.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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)
Thanks so much. I'll give it a try.