Search Excel chart for grouped cell contents?
[Closed]
Report
RJK

RJK 
RJK 
Related:
 Search Excel chart for grouped cell contents?
 How to copy cell contents in excel  HowTo  Excel
 Excel formula bar not showing cell contents  HowTo  Excel
 To get cell contents into Excel file property ✓  Forum  Excel
 Excel clear cell contents if another cell changes ✓  Forum  Excel
 Clear cell contents based on color ✓  Forum  Office Software
2 replies
venkat1926
 Posts
 1864
 Registration date
 Sunday June 14, 2009
 Status
 Contributor
 Last seen
 August 7, 2021
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 datafilteradvance 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
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 datafilteradvance 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
 Posts
 1864
 Registration date
 Sunday June 14, 2009
 Status
 Contributor
 Last seen
 August 7, 2021
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.
If the criteria changes modify
x=
y=
note: spellings should be correct including case (upper or lower)
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)
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.