Hi All, this is the first time i have logged on. Please help me.
i have a list of 100 customers, where each customer has 1 or more phone
numbers.
the labels are - customer name(A1), Phone number (A2)....
the phone numbers are in seperate rows... how do i concatenate such that i
get the customer name in the first column and all his phone numbers
(seperated by a comma) in the second column

Assuming your data in A1 down have a regular pattern, eg like this:

Cust1
111
222
Cust2
333
444
etc

(Custname, followed by say 2 lines of phone numbers, then next Custname/his
2 lines of phone numbers, and so on)

Then you could place this in B1:
=OFFSET($A$1,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)
Copy B1 to D1, then fill down as far as required to exhaust the data (until
zeros appear). This will re-arrange the source col A into 3 separate cols,
which result would be pretty close to your intents, I figure.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fuzzy" wrote:
Hi All, this is the first time i have logged on. Please help me.
i have a list of 100 customers, where each customer has 1 or more phone
numbers.
the labels are - customer name(A1), Phone number (A2)....
the phone numbers are in seperate rows... how do i concatenate such that i
get the customer name in the first column and all his phone numbers
(seperated by a comma) in the second column

Max, that is my problem.... its not a regular pattern...
its like
cust a - phone 1
cust a - phone 2
cust b - phone 1
cust b - phone 2
cust b - phone 3
cust c - phone 1
cust c - phone 2

there are customers with even 6 phone numbers.....

now what???

if it was the in a frequent order, i'd have used a simpler formula of
=concatenate(a2,",",b2) and dragged it down..then i can just delete the
duplicate names!!! correct?

"Max" wrote:

Assuming your data in A1 down have a regular pattern, eg like this:

Cust1
111
222
Cust2
333
444
etc

(Custname, followed by say 2 lines of phone numbers, then next Custname/his
2 lines of phone numbers, and so on)

Then you could place this in B1:
=OFFSET($A$1,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)
Copy B1 to D1, then fill down as far as required to exhaust the data (until
zeros appear). This will re-arrange the source col A into 3 separate cols,
which result would be pretty close to your intents, I figure.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fuzzy" wrote:
Hi All, this is the first time i have logged on. Please help me.
i have a list of 100 customers, where each customer has 1 or more phone
numbers.
the labels are - customer name(A1), Phone number (A2)....
the phone numbers are in seperate rows... how do i concatenate such that i
get the customer name in the first column and all his phone numbers
(seperated by a comma) in the second column

Max, maybe we could try it another way...
lets say, i copy the customer name into another excel sheet...
and i do a vlookup to get the phone numbers.... but the question is, how do
i do a multiple vlookup such that it does not copy only one phone number, but
all the phone numbers of the customer in one cell??

did i just confuse u?

"Max" wrote:

Assuming your data in A1 down have a regular pattern, eg like this:

Cust1
111
222
Cust2
333
444
etc

(Custname, followed by say 2 lines of phone numbers, then next Custname/his
2 lines of phone numbers, and so on)

Then you could place this in B1:
=OFFSET($A$1,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)
Copy B1 to D1, then fill down as far as required to exhaust the data (until
zeros appear). This will re-arrange the source col A into 3 separate cols,
which result would be pretty close to your intents, I figure.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fuzzy" wrote:
Hi All, this is the first time i have logged on. Please help me.
i have a list of 100 customers, where each customer has 1 or more phone
numbers.
the labels are - customer name(A1), Phone number (A2)....
the phone numbers are in seperate rows... how do i concatenate such that i
get the customer name in the first column and all his phone numbers
(seperated by a comma) in the second column

.. its not a regular pattern...
Then it's not possible, I'm afraid.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---