Ufdjrw

In the "Harry Potter and the Order of the Phoenix" video game, what potion is used to sabotage Umbridge's speakers?

Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact

What is the process for cleansing a very negative action

Do I need to write [sic] when including a quotation with a number less than 10 that isn't written out?

Is it okay to majorly distort historical facts while writing a fiction story?

Airplane gently rocking its wings during whole flight

Why did early computer designers eschew integers?

Is it OK to decorate a log book cover?

Yu-Gi-Oh cards in Python 3

Spaces in which all closed sets are regular closed

free fall ellipse or parabola?

Is there such a thing as a proper verb, like a proper noun?

Reshaping json / reparing json inside shell script (remove trailing comma)

(How) Could a medieval fantasy world survive a magic-induced "nuclear winter"?

Film where the government was corrupt with aliens, people sent to kill aliens are given rigged visors not showing the right aliens

Is there a difference between "Fahrstuhl" and "Aufzug"?

Which one is the true statement?

How did Beeri the Hittite come up with naming his daughter Yehudit?

Redefining symbol midway through a document

What would be the main consequences for a country leaving the WTO?

Why am I getting "Static method cannot be referenced from a non static context: String String.valueOf(Object)"?

Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?

How do I fit a non linear curve?

Strange use of "whether ... than ..." in official text

Excel VBA - Match all instances of a value in range and return adjacent values

0

I have a list of people, each belonging to one or multiple groups. I need to find out all the groups that each individual belongs to.

In one column, I have the list of names. In another column, I have the combined list of names belonging to each group, with no spaces or separators between names. I want the result to show each name next to a cell containing all groups the person belongs to. See the example below, where the yellow columns are the desired result:

I thought about creating an array for each name, but I haven't had any success. Here is the vba I have so far, not sure if I'm headed in the right direction.

 Sub Test()
Dim range_1 As Range
Dim range_2 As Range

Set range_1 = Range("Names")
Set range_2 = Range("Combined_Names")

Dim v As Variant

Dim myArray() As Double, X As Long
X = 0
ReDim Preserve myArray(X)
For Each cell In range_1
 v = range_1.Value
 If v <> "" Then
 For Each cell In range_2
 w = range_2.Value
 If InStr(1, w, v, 1) Then
 ReDim Preserve myArray(0 To X)
 myArray(X) = cell.Offset(0, 1).Value
 X = X + 1
 End If
 End If
Next
End Sub

Any advice or help would be greatly appreciated!

excel vba

share|improve this question

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

asked Mar 7 at 18:13

user3047983user3047983

246

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So the only way to distinguish your names is the capital letter? That is a really bad way to store data.
  
  – SJR
  Mar 7 at 19:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is just a way to put it for simplicity, the real data has larger values.
  
  – user3047983
  Mar 7 at 19:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't understand your reply. You should post your data as is.
  
  – SJR
  Mar 8 at 8:32
  

  
  
  
  

add a comment | 

0

I have a list of people, each belonging to one or multiple groups. I need to find out all the groups that each individual belongs to.

In one column, I have the list of names. In another column, I have the combined list of names belonging to each group, with no spaces or separators between names. I want the result to show each name next to a cell containing all groups the person belongs to. See the example below, where the yellow columns are the desired result:

I thought about creating an array for each name, but I haven't had any success. Here is the vba I have so far, not sure if I'm headed in the right direction.

 Sub Test()
Dim range_1 As Range
Dim range_2 As Range

Set range_1 = Range("Names")
Set range_2 = Range("Combined_Names")

Dim v As Variant

Dim myArray() As Double, X As Long
X = 0
ReDim Preserve myArray(X)
For Each cell In range_1
 v = range_1.Value
 If v <> "" Then
 For Each cell In range_2
 w = range_2.Value
 If InStr(1, w, v, 1) Then
 ReDim Preserve myArray(0 To X)
 myArray(X) = cell.Offset(0, 1).Value
 X = X + 1
 End If
 End If
Next
End Sub

Any advice or help would be greatly appreciated!

excel vba

share|improve this question

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

asked Mar 7 at 18:13

user3047983user3047983

246

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So the only way to distinguish your names is the capital letter? That is a really bad way to store data.
  
  – SJR
  Mar 7 at 19:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is just a way to put it for simplicity, the real data has larger values.
  
  – user3047983
  Mar 7 at 19:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't understand your reply. You should post your data as is.
  
  – SJR
  Mar 8 at 8:32
  

  
  
  
  

add a comment | 

I have a list of people, each belonging to one or multiple groups. I need to find out all the groups that each individual belongs to.

In one column, I have the list of names. In another column, I have the combined list of names belonging to each group, with no spaces or separators between names. I want the result to show each name next to a cell containing all groups the person belongs to. See the example below, where the yellow columns are the desired result:

I thought about creating an array for each name, but I haven't had any success. Here is the vba I have so far, not sure if I'm headed in the right direction.

 Sub Test()
Dim range_1 As Range
Dim range_2 As Range

Set range_1 = Range("Names")
Set range_2 = Range("Combined_Names")

Dim v As Variant

Dim myArray() As Double, X As Long
X = 0
ReDim Preserve myArray(X)
For Each cell In range_1
 v = range_1.Value
 If v <> "" Then
 For Each cell In range_2
 w = range_2.Value
 If InStr(1, w, v, 1) Then
 ReDim Preserve myArray(0 To X)
 myArray(X) = cell.Offset(0, 1).Value
 X = X + 1
 End If
 End If
Next
End Sub

Any advice or help would be greatly appreciated!

excel vba

share|improve this question

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

asked Mar 7 at 18:13

user3047983user3047983

246

 Sub Test()
Dim range_1 As Range
Dim range_2 As Range

Set range_1 = Range("Names")
Set range_2 = Range("Combined_Names")

Dim v As Variant

Dim myArray() As Double, X As Long
X = 0
ReDim Preserve myArray(X)
For Each cell In range_1
 v = range_1.Value
 If v <> "" Then
 For Each cell In range_2
 w = range_2.Value
 If InStr(1, w, v, 1) Then
 ReDim Preserve myArray(0 To X)
 myArray(X) = cell.Offset(0, 1).Value
 X = X + 1
 End If
 End If
Next
End Sub

share|improve this question

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

asked Mar 7 at 18:13

user3047983user3047983

246

share|improve this question

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

asked Mar 7 at 18:13

user3047983user3047983

246

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

edited Mar 8 at 7:35

Pᴇʜ

24.9k63052

asked Mar 7 at 18:13

user3047983user3047983

246

asked Mar 7 at 18:13

user3047983user3047983

246