Vlookup challenge

S

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.
 
J

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
 
J

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
 
S

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?
 
E

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.
 
J

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
 
S

Steve Albert

It worked! Thank you so much.

Ashish Mathur said:
Hi,

Try this array formula (Ctrl+Shift+Enter). C4:C8 holds the incident #,
D4:D8 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
 
H

Harlan Grove

Ashish Mathur said:
Try this array formula (Ctrl+Shift+Enter).  C4:C8 holds the incident #,
D4:D8 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top