multiple rows to single row
 

I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.

multiple rows to single row
 

How high do your letters go (c,m,z,ab)?

"Abhay" wrote:

I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.

multiple rows to single row
 

Hello,

Names ranges
Names ranges
Code =Offset(BD!$A$2;;;CountA(BD!$A:$A))-1
Val =offset(BD!$B$2;;;CountA(BD!$A:$A)-1)

Codes in A2:
-Select A2:A20
=IF(ISTEXT(INDEX(Code,SMALL(IF(MATCH(Code,Code,0)= ROW(INDIRECT
("1:"&ROWS(Code))),
MATCH(Code,Code,0),""),ROW(INDIRECT("1:"&ROWS(Code )))))),
INDEX(Code,SMALL(IF(MATCH(Code,Code,0)=ROW(INDIREC T("1:"&ROWS(Code))),
MATCH(Code,Code,0),""),ROW(INDIRECT("1:"&ROWS(Code ))))),"")
-Valid with Ctrl+****+enter

Vales in B2:
=IF(COLUMN()-1<=COUNTIF(Code,$A2),INDEX(Val,MATCH($A2,Code,0)+C OLUMN
()-2,1),"")

http://cjoint.com/?bvoxeZyegv

JB
http://boisgontierjacques.free.fr/

On 19 jan, 12:16, Abhay wrote:
I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.

multiple rows to single row
 

It can be upto 5000 to 10000 and any text e.g. PT-1001 etc.

"JMay" wrote:

How high do your letters go (c,m,z,ab)?

"Abhay" wrote:

I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.

multiple rows to single row
 

JB,
This is really complex for me to understand. I am novice in excel. -:(

"JB" wrote:

Hello,

Names ranges
Names ranges
Code =Offset(BD!$A$2;;;CountA(BD!$A:$A))-1
Val =offset(BD!$B$2;;;CountA(BD!$A:$A)-1)

Codes in A2:
-Select A2:A20
=IF(ISTEXT(INDEX(Code,SMALL(IF(MATCH(Code,Code,0)= ROW(INDIRECT
("1:"&ROWS(Code))),
MATCH(Code,Code,0),""),ROW(INDIRECT("1:"&ROWS(Code )))))),
INDEX(Code,SMALL(IF(MATCH(Code,Code,0)=ROW(INDIREC T("1:"&ROWS(Code))),
MATCH(Code,Code,0),""),ROW(INDIRECT("1:"&ROWS(Code ))))),"")
-Valid with Ctrl+****+enter

Vales in B2:
=IF(COLUMN()-1<=COUNTIF(Code,$A2),INDEX(Val,MATCH($A2,Code,0)+C OLUMN
()-2,1),"")

http://cjoint.com/?bvoxeZyegv

JB
http://boisgontierjacques.free.fr/

On 19 jan, 12:16, Abhay wrote:
I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.

multiple rows to single row
 

Hi Abhay,

There is one more way,
First sort the list (your example shows a sorted list)
assume that you want to change the data in sheet 2
then name the headers in sheet2 row A1
Copy the data from sheet 1 (only for particular data for eg (a) as per the
example given by you) and right click in cell A2 in Sheet2 , Paste
specialclick transpose

This will put the data as required by you i have also given an example of
this.

Col A Col B
a 1
a 2
a 3
b 1
b 2

Col A Col B Col C Col D
a 1 2 3
b 1 2


--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade


"Abhay" wrote:

It can be upto 5000 to 10000 and any text e.g. PT-1001 etc.

"JMay" wrote:

How high do your letters go (c,m,z,ab)?

"Abhay" wrote:

I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.

multiple rows to single row
 

Thanks Suleman. This works only for small amount of data. What I have is 1000
to 10000 rows and file updates frequently.

"Suleman Peerzade" wrote:

Hi Abhay,

There is one more way,
First sort the list (your example shows a sorted list)
assume that you want to change the data in sheet 2
then name the headers in sheet2 row A1
Copy the data from sheet 1 (only for particular data for eg (a) as per the
example given by you) and right click in cell A2 in Sheet2 , Paste
specialclick transpose

This will put the data as required by you i have also given an example of
this.

Col A Col B
a 1
a 2
a 3
b 1
b 2

Col A Col B Col C Col D
a 1 2 3
b 1 2


--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade


"Abhay" wrote:

It can be upto 5000 to 10000 and any text e.g. PT-1001 etc.

"JMay" wrote:

How high do your letters go (c,m,z,ab)?

"Abhay" wrote:

I have Data which is like
a 1
a 2
a 3
b 1
b 2

This I would like to regroup this as following
a 1 2 3
b 1 2 and so on.
Issue is -
Number of times a .... appears is not fixed.
Number of unique ä,b in column is not fixed
All data is text only, as an example I used numbers here.
Help appreciated. Thanks.