How to return all values matching two criteria
Closed
kstdrew
Posts
2
Registration date
Friday September 27, 2013
Status
Member
Last seen
September 28, 2013
-
Sep 27, 2013 at 10:34 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 29, 2013 at 12:56 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 29, 2013 at 12:56 AM
Related:
- How to return all values matching two criteria
- How to return on flipkart - Guide
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
- Display two columns in data validation list but return only one - Guide
- If cell contains date then return value ✓ - Office Software Forum
- We couldn't find an account matching the login info you entered, but found an account that closely matches based on your login history ✓ - Facebook Forum
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 28, 2013 at 04:08 AM
Sep 28, 2013 at 04:08 AM
data is like this
from row 1 to row 7
columns A to C
Number Sector Name
21144 P John
21144 P Mark
21144 P Bob
21144 A Charlie
21099 P Mike
21099 P Allan
21099 A Dave
now try this macros (run only test)
from row 1 to row 7
columns A to C
Number Sector Name
21144 P John
21144 P Mark
21144 P Bob
21144 A Charlie
21099 P Mike
21099 P Allan
21099 A Dave
now try this macros (run only test)
Sub TEST()
Dim r As Range, x As String
Dim j As Integer, k As Integer
undo
Set r = Range("A1").CurrentRegion
j = r.Rows.Count
r.AutoFilter field:=2, Criteria1:="P"
r.AutoFilter field:=1, Criteria1:=21144
For k = 2 To j
If Cells(k, "C").EntireRow.Hidden = False Then x = x & " " & Cells(k, "C")
'MsgBox x
Next k
'MsgBox x
x = Right(x, Len(x) - 1)
'MsgBox x
ActiveSheet.AutoFilterMode = False
Range("A1").End(xlDown).Offset(1, 0) = x
End Sub
Sub undo()
Cells(1, "C").End(xlDown).Offset(1, 0).EntireRow.Delete
End Sub
kstdrew
Posts
2
Registration date
Friday September 27, 2013
Status
Member
Last seen
September 28, 2013
Sep 28, 2013 at 12:31 PM
Sep 28, 2013 at 12:31 PM
Thanks! However, it doesn't work. Maybe I didn't explain this correctly. The table above is just an example. In fact, it contains hundreds of rows. The workbook itself has two sheets: one contains the raw data as shown above. On the second one, when I enter the number (any number, not just 21144), I want the cell on the right to display all matches as long as the other condition is met. That is, for 21144=true and P=true, display "John, Mark, Bob". Furthermore, the next cell to the right should display "Charlie" as long as 21144=true and A=true. Basically, instead of having 4 rows (or 5, or 100) for the same number, I'd like to have a single row that basically separates data. So, this table I have on sheet2
Number Sector Name
21144 P John
21144 P Mark
21144 P Bob
21144 A Charlie
21099 P Mike
21099 P Allan
21099 A Dave
should look like this on sheet1
Number P A
21144 John Mark Bob Charlie
21099 Mike Allan Dave
without comma between names.
Again, the macro should be able to handle all information contained on sheet2. This sheet is under constant modification (more records are added every day). Also, the macro should run automatically, without the need to manually run it every time I add new data.
Another question: can I use a formula instead of a macro? Thanks!
Number Sector Name
21144 P John
21144 P Mark
21144 P Bob
21144 A Charlie
21099 P Mike
21099 P Allan
21099 A Dave
should look like this on sheet1
Number P A
21144 John Mark Bob Charlie
21099 Mike Allan Dave
without comma between names.
Again, the macro should be able to handle all information contained on sheet2. This sheet is under constant modification (more records are added every day). Also, the macro should run automatically, without the need to manually run it every time I add new data.
Another question: can I use a formula instead of a macro? Thanks!
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 29, 2013 at 12:56 AM
Sep 29, 2013 at 12:56 AM
in your first message you have written
quote
I would like the cell A8 to display all values matching these 2 criteria: number=21144 and Sector=P.
unquote
that is why the macro was given.
had you explained in your fist messagea itself we could have saved time and effort.
ok does not matter.
I have prepared a file . download this from here
http://speedy.sh/wTF7m/drew-130929.xlsm
in sheet1 is the data (NO BLANK ROWS OR BLANK COLUMNS )
right click sheet2(repeat sheet2) and click view code and in the window comes up copy the code given below (I have already done this in the downloadable file)
if you do it in some other file save it after copying the event code
the event code is also repeated here.
you can enter in row 3 or later
CONFIRM WHETHER THIS SERVES YOUR PURPOSE. you can modify the event code if necessary.
quote
I would like the cell A8 to display all values matching these 2 criteria: number=21144 and Sector=P.
unquote
that is why the macro was given.
had you explained in your fist messagea itself we could have saved time and effort.
ok does not matter.
I have prepared a file . download this from here
http://speedy.sh/wTF7m/drew-130929.xlsm
in sheet1 is the data (NO BLANK ROWS OR BLANK COLUMNS )
right click sheet2(repeat sheet2) and click view code and in the window comes up copy the code given below (I have already done this in the downloadable file)
if you do it in some other file save it after copying the event code
the event code is also repeated here.
Private Sub Worksheet_Change(ByVal Target As Range)now go to sheet2 repeat sheet2 and type the NUMBER in column A and SECTOR in column B (note this carefully) and see what happens in column C. I have already given two eXampleS of what happens to C when A1 B1 entered or A2 B2.
Dim r As Range, x As String
Dim j As Integer, k As Integer
'undo
If Target.Column > 3 Then Exit Sub
On Error GoTo events
Application.EnableEvents = False
With Worksheets("sheet1")
Set r = .Range("A1").CurrentRegion
j = r.Rows.Count
r.AutoFilter field:=2, Criteria1:=Target
r.AutoFilter field:=1, Criteria1:=Target.Offset(0, -1)
For k = 2 To j
If .Cells(k, "C").EntireRow.Hidden = False Then x = x & " " & .Cells(k, "C")
'MsgBox x
Next k
'MsgBox x
x = Right(x, Len(x) - 1)
'MsgBox x
.AutoFilterMode = False
End With
Target.Offset(0, 1) = x
'Range("A1").End(xlDown).Offset(1, 0) = x
events:
Application.EnableEvents = True
End Sub
you can enter in row 3 or later
CONFIRM WHETHER THIS SERVES YOUR PURPOSE. you can modify the event code if necessary.
