| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving data from multiple rows to single rows | Excel Worksheet Functions | |||
| Moving data from multiple rows into a single row | Excel Discussion (Misc queries) | |||
| Sum single or duplicate rows with multiple criteria | Excel Worksheet Functions | |||
| HELP!! ARRANGE MULTIPLE ROWS IN A SINGLE ROW | Excel Worksheet Functions | |||
| Multiple rows from a single row | Excel Discussion (Misc queries) | |||
Linear Mode
