Macro or Formula Needed

[carl]

My data looks like this:

Tag1 MessageType
SLV 120421C31.00bce00001 SailDirectedOrderNotice
SLV 120421C31.00bce00001 SailDirectedOrderAcceptation
SLV 140118C30.00bce00004 SailDirectedOrderNotice
SLV 111217P20.00bce00001 SailDirectedOrderNotice
SLV 111217P20.00bce00001
SailDirectedRoutedOrderRejectionAndQuoteResubmit


I have some Tag1 values in another sheet cell A1:

Tag1
SLV 120421C31.00bce00001
SLV 140118C30.00bce00004
SLV 111217P20.00bce00001


I need a macro or formula that for each Tag1 value, looks at my data
and creates a string of the associated MessageType. Like this:

Tag1 MessageTypeChain
SLV 120421C31.00bce00001
SailDirectedOrderNotice;SailDirectedOrderAcceptation
SLV 140118C30.00bce00004 SailDirectedOrderNotice
SLV 111217P20.00bce00001
SailDirectedOrderNotice;SailDirectedRoutedOrderRejectionAndQuoteResubmit


Is this possible ?

Thank you in advance.

 

[Pete_UK]

Put this formula in C2 of Sheet1, and copy it down at least to the
bottom of your data:

=IF(A2="","",A2&"_"&COUNTIF(A$2:A2,A2))

Then in Sheet2 you can put this formula in cell B2:

=IF(A2="","",IF(ISNA(MATCH(A2&"_1",Sheet1!C:C,0)),"",INDEX(Sheet1!
B:B,MATCH(A2&"_1",Sheet1!C:C,0)))&IF(ISNA(MATCH(A2&"_2",Sheet1!C:C,
0)),"",";"&INDEX(Sheet1!B:B,MATCH(A2&"_2",Sheet1!C:C,
0)))&IF(ISNA(MATCH(A2&"_3",Sheet1!C:C,0)),"",";"&INDEX(Sheet1!
B:B,MATCH(A2&"_3",Sheet1!C:C,0))))

and copy this down. It will give you up to 3 messages joined together,
but if you need more the formula can easily be extended, e.g. to cater
for up to 4 messages the formula would be:

=IF(A2="","",IF(ISNA(MATCH(A2&"_1",Sheet1!C:C,0)),"",INDEX(Sheet1!
B:B,MATCH(A2&"_1",Sheet1!C:C,0)))&IF(ISNA(MATCH(A2&"_2",Sheet1!C:C,
0)),"",";"&INDEX(Sheet1!B:B,MATCH(A2&"_2",Sheet1!C:C,
0)))&IF(ISNA(MATCH(A2&"_3",Sheet1!C:C,0)),"",";"&INDEX(Sheet1!
B:B,MATCH(A2&"_3",Sheet1!C:C,0)))&IF(ISNA(MATCH(A2&"_4",Sheet1!C:C,
0)),"",";"&INDEX(Sheet1!B:B,MATCH(A2&"_4",Sheet1!C:C,0))))

Hope this helps.

Pete

 

[joeu2004]

I need a macro or formula that for each Tag1 value,
looks at my data and creates a string of the associated
MessageType.

Perhaps something like the following. Some caveats:

1. The procedure must be placed in the target worksheet module. Right-click
the worksheet tab and click View Code.

2. The procedure assumes that "SLV" (or other designator) is in column A and
strings of the form "120421C31.00bce00001" are in column B. It was unclear
whether the first two columns of your examples are one string or two
columns.

3. Although the procedure works per your specifications, I question your
specifications. The concatenated strings can become very long. Excel will
display only up to 255 characters.

4. The procedure assumes that the name of the source worksheet is "data".

5. The procedure can be optimized if the data and the "keys" are grouped and
in the same order, as in your examples. As implemented below, the procedure
does not rely on that assumption.

-----

Option Explicit

' *** must be placed in target worksheet module ***
Sub collectIt()
Dim data, key, nData As Long, nkey As Long
Dim kr As Long, dr As Long, s As String
With Sheets("data")
data = .Range("a2", .Cells(.Range("a1").End(xlDown).Row, "c"))
End With
key = Range("a2", Cells(Range("a1").End(xlDown).Row, "b"))
nData = UBound(data, 1)
nkey = UBound(key, 1)
For kr = 1 To nkey
s = ""
For dr = 1 To nData
If key(kr, 1) = data(dr, 1) And key(kr, 2) = data(dr, 2) Then
s = s & ";" & data(dr, 3)
End If
Next
If s <> "" Then Cells(1 + kr, "c") = Mid(s, 2)
Next
End Sub