Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Arbitrary Lookups - return ALL found values

Hello, for long time now I'm searching a way in excel to solve following:
Data I have:
Name Score
Annie 11
Beth 22
Cathy 33
Dana 44
Annie 55
Beth 66
Annie 77
Beth 88
Cathy 99

Data I want to produce:
Name Score
Annie 11|55|77
Beth 22|66
Cathy 33|99
Dana 44

The character "|" is just a example of separator it can be any other
meaningfull characted.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Arbitrary Lookups - return ALL found values

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

then use the following formulas

To get unique names:

In D2:
=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down

In E2: =SUBSTITUTE(TRIM(MCONCAT(IF(Name=$D2,Score,"")&" "))," ","|")

ctrl+shift+enter, not just enter
copy down



"baki" wrote:

Hello, for long time now I'm searching a way in excel to solve following:
Data I have:
Name Score
Annie 11
Beth 22
Cathy 33
Dana 44
Annie 55
Beth 66
Annie 77
Beth 88
Cathy 99

Data I want to produce:
Name Score
Annie 11|55|77
Beth 22|66
Cathy 33|99
Dana 44

The character "|" is just a example of separator it can be any other
meaningfull characted.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Arbitrary Lookups - return ALL found values

With your data in cols A & B, running this macro:

Sub baki()
Range("C:D").Clear
n = Cells(Rows.Count, 1).End(xlUp).Row
Cells(2, 3).Value = Cells(2, 1).Value
k = 3
For i = 3 To n
Set bb = Range("A2:A" & i)
cnt = Application.WorksheetFunction.CountIf(bb, Cells(i, 1))
If cnt = 1 Then
Cells(k, 3).Value = Cells(i, 1).Value
k = k + 1
End If
Next

For i = 2 To k - 1
flr = Cells(i, 3).Value
For j = 2 To n
If flr = Cells(j, 1).Value Then
Cells(i, 4).Value = Cells(i, 4).Value & Cells(j, 2).Value & "!"
End If
Next
Next
End Sub

will give you the desired data in cols C & D
--
Gary''s Student - gsnu200784


"baki" wrote:

Hello, for long time now I'm searching a way in excel to solve following:
Data I have:
Name Score
Annie 11
Beth 22
Cathy 33
Dana 44
Annie 55
Beth 66
Annie 77
Beth 88
Cathy 99

Data I want to produce:
Name Score
Annie 11|55|77
Beth 22|66
Cathy 33|99
Dana 44

The character "|" is just a example of separator it can be any other
meaningfull characted.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,180
Default Arbitrary Lookups - return ALL found values

Or use Pivot Table:
http://www.savefile.com/files/1544408
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Arbitrary Lookups - return ALL found values

Hi Teethless mama,
I've downloaded add-in but your first formula is not wokring for me.
I've put data starting in A1 but when I insert your function in D2 it does
not show any data nor error - just blank. Am I missing something.

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

then use the following formulas

To get unique names:

In D2:
=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down

In E2: =SUBSTITUTE(TRIM(MCONCAT(IF(Name=$D2,Score,"")&" "))," ","|")

ctrl+shift+enter, not just enter
copy down



"baki" wrote:

Hello, for long time now I'm searching a way in excel to solve following:
Data I have:
Name Score
Annie 11
Beth 22
Cathy 33
Dana 44
Annie 55
Beth 66
Annie 77
Beth 88
Cathy 99

Data I want to produce:
Name Score
Annie 11|55|77
Beth 22|66
Cathy 33|99
Dana 44

The character "|" is just a example of separator it can be any other
meaningfull characted.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Arbitrary Lookups - return ALL found values

If you're going to use Morefunc to concat the data why not use Morefunc to
extract the uniques?

=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))



=INDEX(UNIQUEVALUES(Name),ROWS(A$1:A1))

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

then use the following formulas

To get unique names:

In D2:
=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down

In E2: =SUBSTITUTE(TRIM(MCONCAT(IF(Name=$D2,Score,"")&" "))," ","|")

ctrl+shift+enter, not just enter
copy down



"baki" wrote:

Hello, for long time now I'm searching a way in excel to solve following:
Data I have:
Name Score
Annie 11
Beth 22
Cathy 33
Dana 44
Annie 55
Beth 66
Annie 77
Beth 88
Cathy 99

Data I want to produce:
Name Score
Annie 11|55|77
Beth 22|66
Cathy 33|99
Dana 44

The character "|" is just a example of separator it can be any other
meaningfull characted.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Arbitrary Lookups - return ALL found values

Hi again Teethless mama,
I was wrong, your code does work, I'm not so profound with Excel so I did
not get it right away that ranges shuld be defined. Thanks for your help.

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

then use the following formulas

To get unique names:

In D2:
=IF(ISERR(SMALL(IF(MATCH(Name,Name,0)=ROW(INDIRECT ("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS($1:1)) ),"",INDEX(Name,SMALL(IF(MATCH(Name,Name,0)=ROW(IN DIRECT("1:"&ROWS(Name))),MATCH(Name,Name,0)),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down

In E2: =SUBSTITUTE(TRIM(MCONCAT(IF(Name=$D2,Score,"")&" "))," ","|")

ctrl+shift+enter, not just enter
copy down



"baki" wrote:

Hello, for long time now I'm searching a way in excel to solve following:
Data I have:
Name Score
Annie 11
Beth 22
Cathy 33
Dana 44
Annie 55
Beth 66
Annie 77
Beth 88
Cathy 99

Data I want to produce:
Name Score
Annie 11|55|77
Beth 22|66
Cathy 33|99
Dana 44

The character "|" is just a example of separator it can be any other
meaningfull characted.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Thanks for the code

The code works great for total nummeric values in 'A2:A' but is there a way to open the format requirements, to allow text or any of the other formats available to be in column A? For example I have some ESNs (Alphanumeric data) that when I paste them, I get the green triangle in the upper right corner. If I leave them alone and run the macro, it wont comeplete the. IF I convert the pasted data to where all the data is alligned to the right side of the cell(green thiny goes away)... excel will truncate the data; and the macro will complete. I have data for an example and I am not seeing where i can upload data for an example....I will return to see if there is a reply. Thx.
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Thanks for the code

Try posting within the original thread.

A standalone post like yours gives no background or information about the
original problem or the code supplied.


Gord Dibben MS Excel MVP

On Tue, 13 May 2008 08:27:15 -0700, Tim Fisher wrote:

The code works great for total nummeric values in 'A2:A' but is there a way to open the format requirements, to allow text or any of the other formats available to be in column A? For example I have some ESNs (Alphanumeric data) that when I paste them, I get the green triangle in the upper right corner. If I leave them alone and run the macro, it wont comeplete the. IF I convert the pasted data to where all the data is alligned to the right side of the cell(green thiny goes away)... excel will truncate the data; and the macro will complete. I have data for an example and I am not seeing where i can upload data for an example....I will return to see if there is a reply. Thx.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return all values found for criteria duketter Excel Discussion (Misc queries) 2 May 23rd 07 08:36 PM
Vlookup, return zero if not found molsansk Excel Worksheet Functions 2 August 22nd 06 06:40 PM
can lookup return err if no match found Kim Greenlaw Excel Worksheet Functions 12 January 12th 06 05:27 PM
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. Mr Wiffy Excel Worksheet Functions 2 May 16th 05 04:29 AM
Return text found in a search Dave R. Excel Worksheet Functions 4 May 12th 05 08:53 PM


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"