Vlookup challenge

[Steve Albert]

I have a spreasheet with two columns. The first column has a list of Incident
numbers and the scond column has notes about the Incident (Table 1). If some
of the Incident numbers are the same, how can i write a lookup to concatenate
all of the note fields for one incident number (Table 2)?

Table 1
Incident # Note
------------ -----
101 This was completed yesterday.
102 Still in process.
103 Will complete tomorrow.
102 Called person on 8-5-09.
103 Delay in completing.


Table 2

Incident # Note
----------- -----
101 This was completed yesterday.
102 Still in process. Called person on 8-5-09.
103 Will complete tomorrow. Delay in completing.

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.

1. Select the range in Col A of Table 1 including the header. You need to
have headers for this column
2. From menu Data>Filter>Advanced Filter>Copy to another location
3. In copy to I have selected D1 and check 'Unique records only'
4. Click OK will give you the unique list of year and title in Col D
6. In E2 apply the below formula

=vlookups(D1,$A$2:$B$100,2)

Function VLOOKUPS(strTemp As String, rngTemp As Range, intCol As Integer) As
String
Dim lngRow As Long
For lngRow = 1 To rngTemp.Rows.Count
If rngTemp(lngRow, 1).Text = strTemp Then _
VLOOKUPS = VLOOKUPS & rngTemp(lngRow, intCol)
Next
End Function

If this post helps click Yes

 

[Jacob Skaria]

corection; formula should be =vlookups(D2,$A$2:$B$100,2)

The syntax would be
VLOOKUPS(lookup_value,table_array,col_index_num)

If this post helps click Yes

 

[Steve Albert]

I haven't tried it yet, but how does this get the "Notes" items from Table 1
to combine under a individual Incident # in Table 2?

 

[Steve Albert]

It looks like I might need to use an array formula. Can anyone help?

 

[Erin Searfoss]

=vlookups(D2,$A$2:$B$100,2)

The $A$2:$B$100 in the function refers to the data in Table 1. The function
is entered in the first cell under the Notes header in Table 2 where D2 is
the first Incident # in Table 2, thus bringing the info from Table 1 to Table
2.

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter). C4:C8 holds the incident #,
D48 holds the Note and C12 holds 101

=TRIM(MCONCAT(IF($C$4:$C$8=C12,$D$4:$D$8," ")))

You may copy this formula down.

Also, MCONCAT is not a standard Excel funtion. You would need to download
and install this addin to use MCONCAT -
http://www.download.com/Morefunc/3000-2077_4-10423159.html

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

Hi Steve

Have you tried this yet? The below mentioned is a user defined function and
not an array formula which will combine the text found in col_index_num of
table_array for all matching entries (lookup_value) found in the 1st column
of the table_array. (Just like vlookup brings up the mathing entry.)

VLOOKUPS(lookup_value,table_array,col_index_num)


If this post helps click Yes

 

[Steve Albert]

It worked! Thank you so much.

Hi,

Try this array formula (Ctrl+Shift+Enter). C4:C8 holds the incident #,
D48 holds the Note and C12 holds 101

=TRIM(MCONCAT(IF($C$4:$C$8=C12,$D$4:$D$8," ")))

You may copy this formula down.

Also, MCONCAT is not a standard Excel funtion. You would need to download
and install this addin to use MCONCAT -
http://www.download.com/Morefunc/3000-2077_4-10423159.html

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Harlan Grove]

Try this array formula (Ctrl+Shift+Enter).  C4:C8 holds the incident #,
D48 holds the Note and C12 holds 101

=TRIM(MCONCAT(IF($C$4:$C$8=C12,$D$4:$D$8," "))) ....
Also, MCONCAT is not a standard Excel funtion.  You would need to download
and install this addin to use MCONCAT -http://www.download.com/Morefunc/3000-2077_4-10423159.html

....

One big caveat: MOREFUNC.XLL, like any other XLL that could be used
with Excel 97 through Excel 2003, can only return strings up to 255
characters in length. If these strings could be longer than 255
characters, VBA functions would be the only practical approach.

Also, for the pure heck of it, why not download MOREFUNC.XLL from its
author's own web site?

http://xcell05.free.fr/morefunc/english/index.htm

 

[Ashish Mathur]

Hi,

Thank you for the info. Moe often that not, the download from the author's
web page yields an error,

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com