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 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 29, 2013 at 12:56 AM
Given this table

A B C
Number Sector Name
1 21144 P John
2 21144 P Mark
3 21144 P Bob
4 21144 A Charlie
5 21099 P Mike
6 21099 P Allan
7 21099 A Dave
8

I would like the cell A8 to display all values matching these 2 criteria: number=21144 and Sector=P. A8 should look like "John, Mark, Bob". Any ideas? Thanks!

3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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)

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
0
kstdrew Posts 2 Registration date Friday September 27, 2013 Status Member Last seen September 28, 2013
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!
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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.

Private Sub Worksheet_Change(ByVal Target As Range)
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

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.
you can enter in row 3 or later

CONFIRM WHETHER THIS SERVES YOUR PURPOSE. you can modify the event code if necessary.
0