Extracting data from range and placing in one cell with comma'sbetween while ignoring blanks

[paitaioz]

Hi guys,

I have a column with mobile numbers in it, say column a1:a25. And another table with multiple columns in it, say a1:d25

I would like to extract the numbers into a single cell with a comma (,) between each number.

The last number should have no , after it but be displayed on its own. I will assume that can be a function added in.

The tricky thing is, say a1:a25 is my table, only a1:a10 have numbers. the rest of the cells are blank.

So CANACTENATE returns eg. "0498356398, 04248692487, 04405986300,,,,,,,,,,,,,,"

When what i'm after is "0498356398, 04248692487, 04405986300" only

I should mention that the table is a vlookup table so the blanks are really"non returns" from the vlookup thingy.

Also, with the same column, I would like the option of selecting these numbers with a toggle button. I am familiar with VBA, so imagine i would need asimple script for each toggle/number that would extract the value, add a comma and place it in the same cell as the rest of the toggle buttons.

What formula and script could i use to achieve my objective?

Thank you in advance for any help.
Jason

Optional Information:
What have you tried so far?: CONCATENATE- But it adds the blank cells in aswell, so it ends up looking like this: XXXXXXXXXX 0453729217,0453729209,,,,,,,,,,,,,,

 

[lhkittle]

Hi guys,



I have a column with mobile numbers in it, say column a1:a25. And anothertable with multiple columns in it, say a1:d25



I would like to extract the numbers into a single cell with a comma (,) between each number.



The last number should have no , after it but be displayed on its own. I will assume that can be a function added in.



The tricky thing is, say a1:a25 is my table, only a1:a10 have numbers. the rest of the cells are blank.



So CANACTENATE returns eg. "0498356398, 04248692487, 04405986300,,,,,,,,,,,,,,"



When what i'm after is "0498356398, 04248692487, 04405986300" only



I should mention that the table is a vlookup table so the blanks are really "non returns" from the vlookup thingy.



Also, with the same column, I would like the option of selecting these numbers with a toggle button. I am familiar with VBA, so imagine i would needa simple script for each toggle/number that would extract the value, add acomma and place it in the same cell as the rest of the toggle buttons.



What formula and script could i use to achieve my objective?



Thank you in advance for any help.

Jason



Optional Information:

What have you tried so far?: CONCATENATE- But it adds the blank cells in as well, so it ends up looking like this: XXXXXXXXXX 0453729217,0453729209,,,,,,,,,,,,,,

Here is a little diddy I arcived from a fourm.
Give it a try, where your data is in column A and the result will be in B1.
I was impressed with how much work this single line of code does.

Option Explicit

Sub SuperTranspose()
Range("B1") = Join(Application.Transpose(Range(Range("A1"), _
Range("A1").End(xlDown))), ", ")
End Sub

Regards,
Howard

 

[paitaioz]

Hi Howard,

I have copied into worksheet code.

How do I "activate" it?

Thank you greatly.

jason

 

[lhkittle]

Hi Howard,



I have copied into worksheet code.



How do I "activate" it?

One way is Alt + f8, click on the macro name and then click RUN.

If the code works for you, you may want to assign a key-stroke short cut torun it or assign it to a forms buttom on the sheet.

Regards,
Howard

 

[paitaioz]

OKAY!!! I have the formula!

Cells C22:C42 Contain the mobile numbers.

=CONCATENATE(IF(C22>1,C22,""),IF(C22="","",","),IF(C23>1,C23,""),IF(C23="","",","),IF(C24>1,C24,""),IF(C24="","",","),IF(C25>1,C25,""),IF(C25="","",","),IF(C26>1,C26,""),IF(C26="","",","),IF(C27>1,C27,""),IF(C27="","",","),IF(C28>1,C28,""),IF(C28="","",","),IF(C29>1,C29,""),IF(C29="","",","),IF(C30>1,C30,""),IF(C30="","",","),IF(C31>1,C31,""),IF(C31="","",","),IF(C32>1,C32,""),IF(C32="","",","),IF(C33>1,C33,""),IF(C33="","",","),IF(C34>1,C34,""),IF(C34="","",","),IF(C35>1,C35,""),IF(C35="","",","),IF(C36>1,C36,""),IF(C36="","",","),IF(C37>1,C37,""),IF(C37="","",","),IF(C38>1,C38,""),IF(C38="","",","),IF(C39>1,C39,""),IF(C39="","",","),IF(C40>1,C40,""),IF(C40="","",","),IF(C41>1,C41,""),IF(C41="","",","),IF(C42>1,C42,""),IF(C42="","",","))

So all i've done is added if statements that display nothing if the cell isblank, but display the Mobile Number(first if statement of each cell) and the comma (second if statement of each cell).

Easy Peasy!

Plagiarise away my friends, plagiarise away!

Wish someone told me ages ago.....

 

[GS]

Try this UserDefined function...

Public Function JoinData(RangeAddress As String)
Dim vData, n&
vData = Range(RangeAddress)
ReDim vaData(1 To UBound(vData))
For n = 1 To UBound(vData): vaData(n) = vData(n, 1): Next
JoinData = Join(vaData, ",")
End Function

Put it in a standard module and call it from any cell like this...

=joindata("C22:C42")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion