Combining search results into one cell

**antoni.konarski** · 06-29-2009, 09:29 AM

Hi everybody

I am a new forum user and I'm not a frequent Excel user...

I have a list of room numbers and room types. I would like to find all rooms of a given type (eg. KR01L) and list all room numbers that match this type.
It would be great to have it in one cell like "109, 114, 123...".

Thanks

AK

**NBVC** · 06-29-2009, 10:06 AM

Try this UDF from Harlon Grove:

Code:

Function ACONCAT(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
ACONCAT = ACONCAT & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
ACONCAT = ACONCAT & y & sep
Next y
Else
ACONCAT = ACONCAT & a & sep
End If

ACONCAT = Left(ACONCAT, Len(ACONCAT) - Len(sep))
End Function

To apply the code, hit Alt+F11 and then go to Insert|Module and paste the above in the editor window.

Then in your sheet apply formula:

Code:

=SUBSTITUTE(TRIM(ACONCAT(IF($B$2:$B$315=E2,$A$2:$A$315,"")," "))," ",",")

Where E2 contains Type to Filter for...

The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER

**antoni.konarski** · 06-29-2009, 10:19 AM

Great!
Thanks a lot.
Is this Module now stored in the .xls file?

Cheers
A

**NBVC** · 06-29-2009, 10:21 AM

Originally Posted by antoni.konarski

Great!
Thanks a lot.
Is this Module now stored in the .xls file?

Cheers
A

Yes it is.

**NBVC** · 06-29-2009, 10:47 AM

Please do remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**antoni.konarski** · 07-02-2009, 04:31 AM

The macro is working (thanks again NBVC!), but only when I have the Security Level set to Low. The main problem is that other people I'm working with would all have to change their Security Level.

I have "Trust all installed add-ins and templates" checked.

At the moment it seems like the only way to use macros is to set the Security Level for Macros to Low. Doesn't make sense, does it?

Do you know how to solve it?

**NBVC** · 07-02-2009, 08:49 AM

Well, that is why it is called Security Level.. I would advise your users to set security to medium.. then upon opening the file you will get asked whether to enable or disable macros... this is microsoft's way of protecting (or allowing you to choose your protection) against viruses within macros.