Vlookup challenge
 

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.

Vlookup challenge
 

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 DataFilterAdvanced FilterCopy 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


"Steve Albert" wrote:

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.

Vlookup challenge
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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 DataFilterAdvanced FilterCopy 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


"Steve Albert" wrote:

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.

Vlookup challenge
 

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?

"Jacob Skaria" wrote:

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 DataFilterAdvanced FilterCopy 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


"Steve Albert" wrote:

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.

Vlookup challenge
 

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

"Steve Albert" wrote:

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.

Vlookup challenge
 

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

"Steve Albert" wrote:

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?

"Jacob Skaria" wrote:

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 DataFilterAdvanced FilterCopy 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


"Steve Albert" wrote:

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.

Vlookup challenge
 

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/300...-10423159.html

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve Albert" wrote in message
...
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.

Vlookup challenge
 

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
---------------
Jacob Skaria


"Steve Albert" wrote:

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?

"Jacob Skaria" wrote:

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 DataFilterAdvanced FilterCopy 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


"Steve Albert" wrote:

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.

Vlookup challenge
 

It worked! Thank you so much.

"Ashish Mathur" wrote:

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/300...-10423159.html

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve Albert" wrote in message
...
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.

Vlookup challenge
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve Albert" wrote in message
...
It worked! Thank you so much.

"Ashish Mathur" wrote:

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/300...-10423159.html

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve Albert" wrote in message
...
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.

Vlookup challenge
 

"Ashish Mathur" wrote...
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

Vlookup challenge
 

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

"Harlan Grove" wrote in message
...
"Ashish Mathur" wrote...
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