Matching values in 1 column to data in another

Hello,

Pulling what's left of my hair out trying to come up with a solution. I have a column (G), with a list of IDs (IWEC44332). Each ID has 2 numbers associated with it in another column (F). I would like to create a macro or function to search column G and list both numbers associated with the ID #. Displaying those numbers either together in 1 column (H) or 2 columns (H & I). Is this possible ???

Backup your workbook before trying this macro


you can change the searching text by changing search_text variable value.


try it and let me know
Sub Macro1()
Dim INIT, I, t, incr, search_text As Variant
I = 0
Columns("g:g").Select
On Error GoTo a:
search_text = "IWEC44332"
Selection.Find(What:=search_text, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
INIT = ActiveCell.Address
Range("h" & ActiveCell.Row).Value = Range("f" & ActiveCell.Row).Value
While I = 0
Selection.FindNext(After:=ActiveCell).Activate
If ActiveCell.Address = INIT Then
I = 1
Else
Range("h" & ActiveCell.Row).Value = Range("f" & ActiveCell.Row).Value
End If
Wend
a:
If Err.Description <> "" Then
MsgBox "no match found"
End If

End Sub

Thanks a bunch anilsolipuram, I'll give it a shot and let you know!

a simple find macro should help. Modify to suit
Sub findem()
For Each c In Range("e7:e" & Cells(Rows.Count, "e").End(xlUp).Row)
c.Offset(0, 1) = Columns("h").Find(c).Offset(0, 1)
Next
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"Oreg" <Oreg.1qtped_1119099909.1482@excelforum-nospam.com> wrote in message
news:Oreg.1qtped_1119099909.1482@excelforum-nospam.com...
>
> Hello,
>
> Pulling what's left of my hair out trying to come up with a solution.
> I have a column (G), with a list of IDs (IWEC44332). Each ID has 2
> numbers associated with it in another column (F). I would like to
> create a macro or function to search column G and list both numbers
> associated with the ID #. Displaying those numbers either together in
> 1 column (H) or 2 columns (H & I). Is this possible ???
>
>
> --
> Oreg
> ------------------------------------------------------------------------
> Oreg's Profile:
http://www.excelforum.com/member.php...fo&userid=9195
> View this thread: http://www.excelforum.com/showthread...hreadid=380308
>

anilsolipuram & Don, Neither macros were working for me so I went back to my original message and noticed I mixed up my columns and should have been more descriptive. I tried to modify your macros with no luck.. Column (F) is a list of IDs. Example: IWEC432151, IWEC968489..... In column (F), each IWEC # shows up twice. In Column (G) are other numbers associated to the IWEC #. Example: 1,2,5,15.... The outcome I am hoping for is a macro to search column G, list each IWEC number once in column (H), match the data in Column (G) and list it in Columns (I) & (J).

Example:
Columns F G H I J
IWEC456934 1 IWEC456934 1 5
IWEC790234 2 IWEC790234 2 15
IWEC456934 5
IWEC790234 15

Thanks,
Oreg

You have to select all columns and sort it by ids first.

I am assuming the ids range from f2:f88, you can edit the range as you want.

try it now and let me know what ever

Sub Macro1()
Dim INIT, I, r, val As Variant
Dim ra As Range
I = 0
Range("f2:f88").Select
Set ra = Selection
For Each c In ra

If I = 0 Then

INIT = c.Value
r = c.Row
I = 1
Else

If (c.Value = INIT) Then
val = val & c.Row & ","
Else
MsgBox val
t = Split(val, ",")
If UBound(t) > 0 Then
Range("h" & r).Value = Range("f" & r).Value
Range("i" & r).Value = Range("g" & r).Value
For j = 0 To UBound(t)
temp = t(j)
If temp = "" Then
GoTo a:
Else
Range("i" & r).Offset(0, j + 1).Value = Range("g" & temp).Value
End If
Next
a:
End If
val = ""
INIT = c.Value
r = c.Row
End If
End If
If c.Value = "" Then
End
End If
Next
End Sub

anilsolipuram,

WOW!!!! This works great! Thanks so much for taking the time.

What I sent was tested and will work, without any sorting, if properly
modified to your data.

--
Don Guillett
SalesAid Software
donaldb@281.com
"Oreg" <Oreg.1qvv6c_1119200706.2005@excelforum-nospam.com> wrote in message
news:Oreg.1qvv6c_1119200706.2005@excelforum-nospam.com...
>
> anilsolipuram & Don, Neither macros were working for me so I went back
> to my original message and noticed I mixed up my columns and should
> have been more descriptive. I tried to modify your macros with no
> luck.. Column (F) is a list of IDs. Example: IWEC432151,
> IWEC968489..... In column (F), each IWEC # shows up twice. In Column
> (G) are other numbers associated to the IWEC #. Example: 1,2,5,15....
> The outcome I am hoping for is a macro to search column G, list each
> IWEC number once in column (H), match the data in Column (G) and list
> it in Columns (I) & (J).
>
> Example:
> Columns F G H I J
> IWEC456934 1 IWEC456934 1 5
> IWEC790234 2 IWEC790234 2 15
> IWEC456934 5
> IWEC790234 15
>
> Thanks,
> Oreg
>
>
> --
> Oreg
> ------------------------------------------------------------------------
> Oreg's Profile:
http://www.excelforum.com/member.php...fo&userid=9195
> View this thread: http://www.excelforum.com/showthread...hreadid=380308
>