Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 8
There are 1 users currently browsing forums.
|
 |
06-29-2009, 08:29 AM
|
|
Registered User
|
|
Join Date: 29 Jun 2009
Location: Warsaw, Poland
MS Office Version:Excel 2003
Posts: 4
|
|
|
Combining search results into one cell
Please Register to Remove these Ads
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
Last edited by antoni.konarski; 07-02-2009 at 08:04 AM.
|
06-29-2009, 09:06 AM
|
 |
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,489
|
|
|
Re: Combining search results into one cell
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
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
06-29-2009, 09:19 AM
|
|
Registered User
|
|
Join Date: 29 Jun 2009
Location: Warsaw, Poland
MS Office Version:Excel 2003
Posts: 4
|
|
|
Re: Combining search results into one cell
Great!
Thanks a lot.
Is this Module now stored in the .xls file?
Cheers
A
|
06-29-2009, 09:21 AM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,489
|
|
|
Re: Combining search results into one cell
Quote:
Originally Posted by antoni.konarski
Great!
Thanks a lot.
Is this Module now stored in the .xls file?
Cheers
A
|
Yes it is.
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
06-29-2009, 09:47 AM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,489
|
|
|
Re: Combining search results into one cell
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
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
07-02-2009, 03:31 AM
|
|
Registered User
|
|
Join Date: 29 Jun 2009
Location: Warsaw, Poland
MS Office Version:Excel 2003
Posts: 4
|
|
|
Re: Combining search results into one cell
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?
|
07-02-2009, 07:49 AM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,489
|
|
|
Re: Combining search results into one cell
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.
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
07-02-2009, 08:04 AM
|
|
Registered User
|
|
Join Date: 29 Jun 2009
Location: Warsaw, Poland
MS Office Version:Excel 2003
Posts: 4
|
|
|
Re: Combining search results into one cell
Thanks
Cheers
A
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
