Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti

Hi,

How about a UDF Alt+F11 to open Vb editor, right click 'This Workbook' and
insert module and paste the code in on the right.

Call with
=listvalues(1,B1:B5)

The 1 could be a range

=listvalues(A1,B1:B5)


Function listvalues(key As Integer, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function


Mike


"Gunti" wrote:

Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti

Mike, this is exactly what i'm looking for. I'm having some trouble, however.

I'm getting a #num# error. I can't seem to find the problem.
A couple example value's i'm using:

Sheet1:

A B
900101967 460342173
900098554 460333597
900101967 460333596


Sheet2:

A
900101967

Listvalues(A1;'Sheet1'!B2:B4') returns #NUM

Any help? your help is greatly appreciated!





"Mike H" wrote:

Hi,

How about a UDF Alt+F11 to open Vb editor, right click 'This Workbook' and
insert module and paste the code in on the right.

Call with
=listvalues(1,B1:B5)

The 1 could be a range

=listvalues(A1,B1:B5)


Function listvalues(key As Integer, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function


Mike


"Gunti" wrote:

Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti

Hi,

Try this mod

The correct syntax for calling the function is

=listvalues(A1,Sheet1!B1:B4)

Function listvalues(key As Long, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function

"Gunti" wrote:

Mike, this is exactly what i'm looking for. I'm having some trouble, however.

I'm getting a #num# error. I can't seem to find the problem.
A couple example value's i'm using:

Sheet1:

A B
900101967 460342173
900098554 460333597
900101967 460333596


Sheet2:

A
900101967

Listvalues(A1;'Sheet1'!B2:B4') returns #NUM

Any help? your help is greatly appreciated!





"Mike H" wrote:

Hi,

How about a UDF Alt+F11 to open Vb editor, right click 'This Workbook' and
insert module and paste the code in on the right.

Call with
=listvalues(1,B1:B5)

The 1 could be a range

=listvalues(A1,B1:B5)


Function listvalues(key As Integer, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function


Mike


"Gunti" wrote:

Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti

Gunti,

That's my correct regional syntax it looks like your using ; instead of , so
change it.

Mike

"Gunti" wrote:

Mike, this is exactly what i'm looking for. I'm having some trouble, however.

I'm getting a #num# error. I can't seem to find the problem.
A couple example value's i'm using:

Sheet1:

A B
900101967 460342173
900098554 460333597
900101967 460333596


Sheet2:

A
900101967

Listvalues(A1;'Sheet1'!B2:B4') returns #NUM

Any help? your help is greatly appreciated!





"Mike H" wrote:

Hi,

How about a UDF Alt+F11 to open Vb editor, right click 'This Workbook' and
insert module and paste the code in on the right.

Call with
=listvalues(1,B1:B5)

The 1 could be a range

=listvalues(A1,B1:B5)


Function listvalues(key As Integer, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function


Mike


"Gunti" wrote:

Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti

Hi mike,
This time i'm getting a #value error. I tried embracing both references in
the Value function.. Also didn't work.

Thanks again for the help, though.

Greets,
Gunti

"Mike H" wrote:

Gunti,

That's my correct regional syntax it looks like your using ; instead of , so
change it.

Mike

"Gunti" wrote:

Mike, this is exactly what i'm looking for. I'm having some trouble, however.

I'm getting a #num# error. I can't seem to find the problem.
A couple example value's i'm using:

Sheet1:

A B
900101967 460342173
900098554 460333597
900101967 460333596


Sheet2:

A
900101967

Listvalues(A1;'Sheet1'!B2:B4') returns #NUM

Any help? your help is greatly appreciated!





"Mike H" wrote:

Hi,

How about a UDF Alt+F11 to open Vb editor, right click 'This Workbook' and
insert module and paste the code in on the right.

Call with
=listvalues(1,B1:B5)

The 1 could be a range

=listvalues(A1,B1:B5)


Function listvalues(key As Integer, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function


Mike


"Gunti" wrote:

Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti

Hi,

A value error is when it cannot find a match for what is in A1 on Sheet1.
Check your numbers really are numbers not text that looks like numbers

To demonstrate the point type a number in a1 (A 1 will do). Ensuring a cell
in Column A of sheet 1 is formatted as general type the same number in there
and my bet is the value error will go away.


Mike

"Gunti" wrote:

Hi mike,
This time i'm getting a #value error. I tried embracing both references in
the Value function.. Also didn't work.

Thanks again for the help, though.

Greets,
Gunti

"Mike H" wrote:

Gunti,

That's my correct regional syntax it looks like your using ; instead of , so
change it.

Mike

"Gunti" wrote:

Mike, this is exactly what i'm looking for. I'm having some trouble, however.

I'm getting a #num# error. I can't seem to find the problem.
A couple example value's i'm using:

Sheet1:

A B
900101967 460342173
900098554 460333597
900101967 460333596


Sheet2:

A
900101967

Listvalues(A1;'Sheet1'!B2:B4') returns #NUM

Any help? your help is greatly appreciated!





"Mike H" wrote:

Hi,

How about a UDF Alt+F11 to open Vb editor, right click 'This Workbook' and
insert module and paste the code in on the right.

Call with
=listvalues(1,B1:B5)

The 1 could be a range

=listvalues(A1,B1:B5)


Function listvalues(key As Integer, Rng As Range)
For Each c In Rng
If c.Offset(0, -1).Value = key Then
listvalues = listvalues & c.Value & " , "
End If
Next
listvalues = Left(listvalues, Len(listvalues) - 3)
End Function


Mike


"Gunti" wrote:

Hello,
I'll first give an example of my situation.

I've got a couple of value's:

A B
1 55
1 56
2 58
3 59
1 60

I'm looking for a method so i can make a list like this:
A B
1: 55,56,60

Basicly Excel has to put the value's in the following cell divided by a ,

I've come as far as making an IF function like IF(A1:A3="1";B1:B3;"Error")
Now the list of numbers (55,56,60) is in an array.

Is there any way to convert this array to the format i described?


Greetings,
Gunti