How to return all values matching two criteria

[Closed]
Report
Posts
2
Registration date
Friday September 27, 2013
Status
Member
Last seen
September 28, 2013
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
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
Posts
2
Registration date
Friday September 27, 2013
Status
Member
Last seen
September 28, 2013

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!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!